How to generate numerical sequences in SQL without creating tables?

Question

Reading the question How to set to zero or null in an SQL query of a date range, for those whose value does not exist? I remembered a similar problem I have. Usually when I need those sequences that relate to the data range I use auxiliary tables to control it. I imagine this is not a very good solution but I have no idea how to solve it without them.

Is there any way to generate these sequences without the need for a new table?

My intention is not specific to a database, I prefer a general solution, but if you have any reason to run only in a database, go ahead, respond to what you know works in a specific way.

Answer 1

In these cases, a performatic solution is to use generators. The initial solution works on virtually any DBMS with SQL support. See more at the end of the answer solutions optimized for specific Dbms.

Here is an example that produces numbers from 0 to 999:

SELECT d1+d2*10+d3*100 AS gerador FROM
   ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
   ( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2,
   ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t3

See working on SQL Fiddle.

Functioning:

• Each of the SELECT 0 AS d1 UNION SELECT 1 ... SELECT 9 generates numbers from 0 to 9;

• what we did in the initial example was to match 3 of them, to have 3 decimal places. The formula d1+d2*10+d3*100 serves to convert the 3 outputs of each generator into an integer number.

Depending on the DBMS it may be necessary to use UNION ALL instead of just UNION.

Optimizing for more specific uses:

• If you need numbers from 1 to 780, just adjust the formula and use a LIMIT 780, for example.

• nothing prevents multiplication by changing the scale in the Queries:

  SELECT d1 + d2 AS gerador FROM
   ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1,
   ( SELECT 0 AS d2 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
     UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) t2
  

  In this example the second set of SELECTs is optimized with the numbers * 10 now.

• if you need numbers from 0 to 255, it is much better to use a shorter query:

  SELECT d1+d2*4+d3*16+d4*64 AS gerador FROM
     ( SELECT 0 AS d1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t1,
     ( SELECT 0 AS d2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t2,
     ( SELECT 0 AS d3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t3,
     ( SELECT 0 AS d4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) t4
  

  In this case the logic was as follows: 4 4 = 256, so we use 4 darlings, but with 4 digits in each. The important thing is to understand the operation of the generator, because there are several ways to optimize the code.

• Generating dates

  We can restrict the third digit of the initial example to only 3, because 0 to 399 is enough to cover an entire year. DATE_ADD is a solution to generate a date:

  DATE_ADD( '2014-01-01', INTERVAL d1+d2*10+d3*100 DAY )
  

  and all it takes is one WHERE to restrict output to a one-year interval. See another question that you mentioned a reply with the full example.

Proprietary solutions:

MS SQL Server

In MS SQL Server we have to simplify a bit using a function. Note the caution to limit recursion with MAXRECURSION 0:

WITH gerador (id) AS (
     SELECT 1
     UNION ALL
     SELECT id + 1
     FROM gerador
     WHERE id < 1000000
  )
  SELECT TOP 1000 id FROM gerador
  OPTION ( MAXRECURSION 0 )
GO

See working on SQL Fiddle.

Postgresql

Even simpler, with the function generate_series:

SELECT * FROM generate_series ( 1, 1000 )

See working on SQL Fiddle.

Oracle

Oracle has a standard installation special table called dual, with a series of pseudo-columns for special uses. Example for our question:

SELECT level FROM dual CONNECT BY level < 1000

See working on SQL Fiddle.

Complementing:

Initially the question puts the condition of not having a table for it. Anyway, an intermediate solution, but very interesting if these sequences are needed on many occasions, is you have a table of integers:

CREATE TABLE inteiros (i INT);
INSERT INTO inteiros (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Thus, you can use the above techniques by combining this table in place of the SELECT UNION:

SELECT d3.i*100+d2.i*10+d1.i+1 AS gerador
FROM inteiros AS d1
JOIN inteiros AS d2
JOIN inteiros AS d3;

Answer 2

SEQUENCES are DBMS objects to generate auto numbering values, where we can define the initial values and their increments.

Here’s an example of creating Quence on Oracle:

create sequence SEQ_EXAMPLE
  minvalue 1
  maxvalue 999
  start with 1
  increment by 1
  nocache
  nocycle;

Its parameters mean respectively that the minimum value of 1(minvalue 1), the maximum value of 999(maxvalue 999), starts at 1(start with 1), will be incremented by 1(increment by 1), will not preallocate values for this Quence (nocache) and when it reaches the maximum will not go to the minimum value(in Cycle).

To get the current value of Quence:

 SELECT SEQ_EXAMPLE.currval

To get the next Sequence value:

SELECT SEQ_EXAMPLE.nextval

To use it in creating your table, in this case as key:

INSERT INTO exemplo_tb (NR_SEQUENCIA, NOME, LOGIN)
VALUES (SEQ_EXAMPLE.Nextval, 'Fulaninho', 'ful');

Answer 3

What you should do and create a SEQUENCE which has compatibility:

The command CREATE SEQUENCE complies with the SQL standard, with the following exceptions:

• Default expression not supported AS
• Obtaining the next value is done using the nextval() function instead of the expression of the pattern NEXT VALUE FOR.
• The clause OWNED BY is a Postgresql extension.

At this link : Create Sequence has a detailed Postgresql 8.2.0 Documentation explaining and exemplifying possible cases of Sequence creation.