MySQL: find out missing order IDs

Question

i Know this question asked several times in StackOverFlow. i have tried few of them but i am out of luck.

i have a MySQL table where there is a field(orders_id) which is might appear randomly in the table (Not sequentially) and i need to find out which ids are missing from the table.

 orders_id     product_name       qty    
       1007      Wireless Mouse      1
       1000      Laptop              1
       1004      Wireless Mouse      3
       1020      PC                  3
       1003      Wireless Mouse      4
       1025      IPAD                4
       1026      iphone              1

Expected Answer:

Assume orders_id start from 1000.

  orders_id     
       1000 
       1001
       1002     
       1005      
       1006    
       1008      
      ......  

i have already created above table at "SqlFiddle" , you guys can use it.

See table: SQLfiddle

**the SQL i have tried: **

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(`orders_id`) from orders

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join orders t on 
        s.id = t.`orders_id` 
 where t.`orders_id` is null

 drop table IDSeq

I have taken above SQL from the following Answer:

SQL: find missing IDs in a table

i have also tried the ANSI SQL:

SELECT a.orders_id+1 AS start, MIN(b.orders_id) - 1 AS end
    FROM orders AS a, orders AS b
    WHERE a.orders_id < b.orders_id
    GROUP BY a.orders_id
    HAVING start < MIN(b.orders_id)

Anyone has any idea?? how can i find out the missing orders id.

Answer 1

Here is another solution to do this:

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

SET @maxid = (SELECT MAX(orders_id) FROM orders);

SELECT     s.id 
FROM
(
    SELECT id
    FROM
    (
       SELECT t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n + 1 AS id
       FROM         TEMP AS t1
       CROSS JOIN TEMP AS t2
       CROSS JOIN TEMP AS t3
       CROSS JOIN TEMP AS t4
    ) t 
    WHERE id BETWEEN 1000 AND @maxid
) s 
LEFT JOIN orders t ON s.id = t.`orders_id` 
WHERE t.`orders_id` IS NULL;

This should give you the orders_ids:

ID
1001
1002
1005
1006
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1021
1022
1023
1024

SQL Fiddle Demo

Note that: I created a TEMP table with values from 0 to 9, in order to use to generate an anchor table containing values from 1 to 10000 instead of the while loop that you had in the query you posted. You can control the values from this table depending on the values in your table. If you had values that exceed 10000 CROSS JOIN the TEMP table one more time.

Update: If the orders_id is of datatype varchar just cast it to INTEGER like so:

SELECT     s.id 
FROM
(
    SELECT id
    FROM
    (
       SELECT t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n + 1 AS id
       FROM         TEMP AS t1
       CROSS JOIN TEMP AS t2
       CROSS JOIN TEMP AS t3
       CROSS JOIN TEMP AS t4
  ) t WHERE id between 1000 AND @maxid
) s 
LEFT JOIN
(
    SELECT CONVERT(orders_id, UNSIGNED INTEGER) AS orders_id 
    FROM orders
) t ON s.id = t.`orders_id` 
WHERE t.`orders_id` IS NULL;

Updated SQL Fiddle Demo