sql – How to choose missing IDs?

Question:

There is an ID field in some Tab table, some numbers are missing, for example: 1,2,4,5,6,8

How to choose numbers 3 and 7?

I made such a request for a long time and now I can’t remember how)) I remember there was ID + 1 in the condition

Answer:

Algorithm. For each Id, we look at the next one, if it is not equal to the current one + 1, we find a gap. Let there be a table SomeTable, and it has records with id values: 5,7,8,11,15 Let's find skip intervals:

SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
  SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
  FROM SomeTable
)T
WHERE id+1 <> next_id

will give:

start_interval  finish_interval
6   6
9   10
12  14

If we are interested in the interval from 1 to the first Id, we will artificially introduce id=0 into the request:

SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
  SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
  FROM (
    SELECT 0 Id
    UNION ALL
    SELECT Id FROM SomeTable
  )T
)T
WHERE id+1 <> next_id

will give:

start_interval  finish_interval
1   4
6   6
9   10
12  14

Note: I have used MS SQL since version 2012, it should work in Oracle and PostgreSQL too.

For versions of MS SQL below 2012 , you can use the following code, which is almost as efficient as LEAD():

SELECT id1+1 start_interval, id2-1 finish_interval
FROM(
  SELECT 
    MAX(CASE WHEN dN=1 THEN Id END)id1,
    MAX(CASE WHEN dN=0 THEN Id END)id2
  FROM(
    SELECT ROW_NUMBER()OVER(ORDER BY Id)as N, Id
    FROM SomeTable
  )T1 , ( SELECT 0 as dN UNION ALL SELECT 1 )T2
  GROUP BY N+dN
)T
WHERE Id1+1 <> Id2

For MySQL it will look something like this

SELECT last_id+1 as start_interval, currentId-1 as finish_interval
FROM(
  SELECT @Id last_id, @Id := Id currentId
  FROM SomeTable, (SELECT @Id := NULL)T
  ORDER BY Id
)T
WHERE last_id+1 <> current_id