Write a SQL Query to find missing numbers in a series

 

 

 

 

CREATE TABLE NUMBERS(

ID NUMBER

);INSERT INTO NUMBERS VALUES(1);

INSERT INTO NUMBERS VALUES(4);

INSERT INTO NUMBERS VALUES(7);

INSERT INTO NUMBERS VALUES(9);

INSERT INTO NUMBERS VALUES(12);

INSERT INTO NUMBERS VALUES(14);

INSERT INTO NUMBERS VALUES(16);

INSERT INTO NUMBERS VALUES(17);

INSERT INTO NUMBERS VALUES(20);

commit;

 

WITH RECURSIVE_TEMP(ID) AS(

--Anchor Block

SELECT MIN(ID) AS ID FROM NUMBERSUNION ALL--Recursive Block

SELECT ID+1 AS ID FROM RECURSIVE_TEMP WHERE ID < (SELECT MAX(ID) AS ID FROM NUMBERS )

)

SELECT ID FROM RECURSIVE_TEMP

;

 

WITH RECURSIVE_TEMP(ID) AS(

--Anchor Block

SELECT MIN(ID) AS ID FROM NUMBERSUNION ALL--Recursive Block

SELECT ID+1 AS ID FROM RECURSIVE_TEMP WHERE ID < (SELECT MAX(ID) AS ID FROM NUMBERS )

)

SELECT ID FROM RECURSIVE_TEMP

MINUS

SELECT ID FROM NUMBERS;

 

Alternate Solution:

 

SELECT

(SELECT MIN(ID) FROM Numbers)-1+ LEVEL AS ID

FROM dual

CONNECT BY LEVEL <= (SELECT MAX(ID) FROM Numbers);

 

WITH TEMP AS(

SELECT

(SELECT MIN(ID) FROM Numbers)-1+ LEVEL AS ID

FROM dual

CONNECT BY LEVEL <= (SELECT MAX(ID) FROM Numbers)

)

SELECT ID FROM TEMP

MINUS

SELECT ID FROM NUMBERS;