SQL: find missing IDs in a table

user_103532 0
Viewed 71485

I have table with a unique auto-incremental primary key. Over time, entries may be deleted from the table, so there are "holes" in this field's values. For example, table data may be as follows:

 ID  | Value    | More fields...
---------------------------------
 2   | Cat      | ... 
 3   | Fish     | ...
 6   | Dog      | ...
 7   | Aardvark | ...
 9   | Owl      | ...
 10  | Pig      | ...
 11  | Badger   | ...
 15  | Mongoose | ...
 19  | Ferret   | ...

I'm interested in a query that will return the list of missing IDs in the table. For the data above, the expected results are:

 ID 
----
 1
 4
 5
 8
 12
 13
 14
 16
 17
 18

Notes:

  1. It is assumed that the initial first ID was 1
  2. The maximum ID that should be examined is the final one, i.e. it's okay to assume that there were no additional entries after the current last one (see additional data on this point below)

A drawback of the above requirements is that the list will not return IDs that were created after ID 19 and that were deleted. I'm currently solving this case in code, because I hold the max ID created. However, if the query can take as a parameter MaxID, and also return those IDs between the current max and MaxID, that would be a nice "bonus" (but certainly not a must).

I'm currently working with MySQL, but consider moving to SQL Server, so I would like the query to fit both. Also, if you are using anything that can't run on SQLite, please mention it, thanks.

sql
22 Answers

to get the missing rows from table

DECLARE @MaxID INT = (SELECT MAX(ID) FROM TABLE1)
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TABLE1 t ON t.ID = LkUp.SeqID
WHERE t.ID is null and SeqID < @MaxID
user_29404500

I just have found the solution for Postgres:

select min(gs) 
from generate_series(1, 1999) as gs 
where gs not in (select id from mytable)
user_151069690

Easiest solution for me: Create a select that gives all ids up to max sequence value (ex:1000000), and filter:

with listids as (
Select Rownum idnumber From dual Connect By Rownum <= 1000000)

select * from listids
where idnumber not in (select id from table where id <=1000000)
user_61256880

A modified version borrowing @Eric proposal. This is for SQL Server and holds in a temp table the start and end value for missing ranges. If the gap is just one value it puts NULL as end value for easier visualization.

It will produce an output like this

|StartId| EndId |
|-------|-------|
|     1 | 10182 |
| 10189 | NULL  |
| 10246 | 15000 |

And this is the script where myTable and id needs to be replaced by your table and identity column.

declare @id bigint
declare @endId bigint
declare @maxid bigint
declare @previousid bigint=0

set @id = 1
select @maxid = max(id) from myTable

create table #IDGaps
(
    startId bigint,
    endId bigint
)

while @id < @maxid
begin
    if NOT EXISTS(select id from myTable where id=@id)
    BEGIN
        SET @previousid=@id
        select top 1 @endId=id from myTable where id>@id

        IF @id=@endId-1
            insert into #IDGaps values(@id,null)
        ELSE
            insert into #IDGaps values(@id,@endId-1)

        SET @id=@endId
        
    END
    ELSE
        set @id = @id + 1
end

select * from #IDGaps

drop table #IDGaps
user_3519750

SOLUTION FOR SQLITE

if your table id only support positive values you can use this

SELECT DISTINCT table_id - 1 AS next_id
FROM table
WHERE next_id NOT IN (SELECT DISTINCT table_id FROM table)
  AND next_id > 0

otherwise you should remove ids greater than the biggest id with

SELECT DISTINCT table_id + 1 AS next_id
FROM table
WHERE next_id NOT IN (SELECT DISTINCT table_id FROM table)
  AND id < (SELECT MAX(id) FROM table)
user_38388050

I have a large audit table and needed something that ran quickly - this worked well for me. It merges the top and bottom IDs for the missing ranges

select minQ.num,minId,maxId from 

(SELECT DISTINCT id +1 as minId, Row_Number() Over ( Order By id ) As Num
FROM tblAuditLoghistory
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM tblAuditLogHistory)
AND id < (SELECT max(id) FROM tblAuditLoghistory)) Minq
 join


(SELECT DISTINCT id - 1 as maxId, Row_Number() Over ( Order By id ) As Num
FROM tblAuditLoghistory
WHERE id - 1 NOT IN (SELECT DISTINCT id FROM tblAuditLogHistory)
AND id > (SELECT min(id) FROM tblAuditLoghistory)) maxQ on minQ.num=maxQ.num
user_105836770
Related