Geek asks and answers

SQL query to return rows sorted by key plus empty rows for missing keys

I have a table that has essentially this structure:

 key value
 ------ ------
 2 val1
 3 val2
 5 val3

Keys are consecutive integers from 1 to (currently) 1 million, increasing by several thousand every day. Key spaces occur when entries are deleted.

I am looking for an SQL query that returns this:

 key value
 ------ ------
 1
 2 val1
 3 val2
 4 
 5 val3

I see how to do this by joining a second table with a complete list of keys. However, I would prefer a solution that uses standard SQL (without stored procedures or a second key table), and this will work no matter what the upper key value is.

+3
source
4

SQL- . , "" , ( ).

, " ", - 0 9:

CREATE TABLE n (d NUMERIC);
INSERT INTO n VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

00..99:

SELECT n1.d + n2.d*10 AS d
FROM n AS n1 CROSS JOIN n AS n10;

00..57:

SELECT n1.d + n2.d*10 AS d
FROM n AS n1 CROSS JOIN n AS n2
WHERE n1.d + n2.d*10 <= 57;

, 100 , 1000 .. , WHERE, .

FROM .

SELECT n0.d, mytable.value
FROM
   (SELECT n1.d + n2.d*10 + n2.d*100 + n3.d*1000 
      + n4.d*10000 + n5.d*100000 AS d
    FROM n AS n1 CROSS JOIN n AS n2 CROSS JOIN n AS n3 
      CROSS JOIN n AS n4 CROSS JOIN n AS n5) AS n0
  LEFT OUTER JOIN mytable ON (n0.d = mytable.key)
WHERE n0.d <= (SELECT MAX(key) FROM mytable);

CROSS JOIN , . . 1 , n6.

, WHERE .

, , SQL. , " ", .

+3

. . ( ASKTOMs Blog)

select  level
from    dual
connect by level <= 1000000

-

WITH 
upper_limit AS
(
    select 1000000 limit from dual
),
fake_table AS
(
    select  level key
    from    dual
    connect by level <= (select limit from upper_limit)
)
select key, value
from table, fake_table
where fake_table.key = table.key(+)

, . . Oracle .

+1

In MySQL, you can find the edges of gaps by doing left combinations against yourself with positive and negative offsets.

For instance:

create table seq ( i int primary key, v varchar(10) );

insert into seq values( 2, 'val1' ), (3, 'val2' ), (5, 'val3' );


select s.i-1 from seq s left join seq m on m.i = (s.i -1) where m.i is null;

+-------+
| s.i-1 |
+-------+
|     1 |
|     4 |
+-------+


select s.i+1 from seq s left join seq m on m.i = (s.i +1) where m.i is null;
+-------+
| s.i+1 |
+-------+
|     4 |
|     6 |
+-------+

This does not give you exactly what you want, but gives enough information to figure out what the missing lines are.

0
source 
WITH range (num) AS (
SELECT 1 -- use your own lowerbound
UNION ALL
SELECT 1 + num FROM range
WHERE num < 10 -- use your own upper bound
)
SELECT r.num, y.* FROM range r left join yourtable y
on r.num = y.id
0
source

More articles:


All Articles