Are there any database implementations that allow for tables that don't contain data but generate data upon query?

I have an application that works well with database query outputs but now need to run with each output over a range of numbers. Sure, I could refactor the application to iterate over the range for me, but it would arguably be cleaner if I could just have a "table" in the database that I could CROSS JOIN with my normal query outputs. Sure, I could just make a table that contains a range of values, but that seems like unnecessary waste.

For example a "table" in a database that represents a range of values, say 0 to 999,999 in a column called "number" WITHOUT having to actually store a million rows, but can be used in a query with a CROSS JOIN with another table as though there actually existed such a table.

I am mostly just curious if such a construct exists in any database implementation.

>Solution :

PostgreSQL has generate_series. SQLite has it as a loadable extension.

SELECT * FROM generate_series(0,9);

On databases which support recursive CTE (SQLite, PostgreSQL, MariaDB), you can do this and then join with it.

WITH RECURSIVE cnt(x) AS (
  VALUES(0)
  UNION ALL
  SELECT x+1 FROM cnt WHERE x < 1000000
)
SELECT x FROM cnt;

The initial-select runs first and returns a single row with a single column "1". This one row is added to the queue. In step 2a, that one row is extracted from the queue and added to "cnt". Then the recursive-select is run in accordance with step 2c generating a single new row with value "2" to add to the queue. The queue still has one row, so step 2 repeats. The "2" row is extracted and added to the recursive table by steps 2a and 2b. Then the row containing 2 is used as if it were the complete content of the recursive table and the recursive-select is run again, resulting in a row with value "3" being added to the queue. This repeats 999999 times until finally at step 2a the only value on the queue is a row containing 1000000. That row is extracted and added to the recursive table. But this time, the WHERE clause causes the recursive-select to return no rows, so the queue remains empty and the recursion stops.