Search code examples
mysqlloopsshortcut

MySQL Multi Union Shortcut

I want to have a mysql table that will list data vertically without using table values.

Just like this.

SELECT 0 AS vertical UNION SELECT 1 UNION SELECT 2 UNION SELECT 3;

this will output

vertical
0
1
2
3

now, is there a way to do this in much lesser code? if i want to list a total of up to 50?

Solution

  • Use recursive CTE for generating range of numbers with or without gap of any range.

    Without GAP:

    -- MySQL (v8.0)
-- range between 0 to 50 with no gap
WITH RECURSIVE num_range (n) AS
(
  SELECT 0 -- starting the range
  UNION ALL
  SELECT n + 1 -- increment by 1
  FROM num_range 
  WHERE n < 50 -- last number
)
SELECT n
FROM num_range;

    With GAP:

    -- range between 0 to 50 with gap
WITH RECURSIVE num_range (n) AS
(
  SELECT 0 -- starting the range
  UNION ALL
  SELECT n + 2  -- increment by 2
  FROM num_range 
  WHERE n < 50 -- last number
)
SELECT n
FROM num_range;

    Please check this url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=30e714fbb43b0d2bf55974bea50410f9