Generate a sequence of numbers using a SQL query

Published on: 2024-03-11 - Permalink SQL Oracle MariaDB

Sometimes we need to generate a sequence of numbers, maybe for cartesian products, or we want to work directly with number series using SQL expressions, for those cases I present a simple option using query recursiveness

Oracle

Using CONNECT BY operator

select level
  from dual
connect by level <= 10;

Using Query Recursive Subfactoring

with c_numbers (lvl) as
( select 1 as lvl
  from dual
 union all
  select lvl + 1
   from c_numbers
   where lvl < 10
)
select lvl
  from c_numbers
order by lvl;

MySQL / MariaDB

(borrowed directly from the MySQL manual) https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;