To Find Missing Values from Set of Values in Oracle

Query to fetch missing numeric values from given set of values in oracle table:

Step 1: Create a Table “TEMP” as shown below:

CREATE TABLE TEMP (A NUMBER);

Step 2: Insert numeric values into “TEMP” Table.

INSERT INTO TEMP VALUES(1);

INSERT INTO TEMP VALUES(3);

INSERT INTO TEMP VALUES(9);

INSERT INTO TEMP VALUES(12);

INSERT INTO TEMP VALUES(15);

INSERT INTO TEMP VALUES(16);

INSERT INTO TEMP VALUES(20);

INSERT INTO TEMP VALUES(19);

Commit;

Step 3: Select the “TEMP” Table.

SELECT * FROM TEMP;

A
19
1
3
9
12
15
16
20

Step 4: Run any of the below query to fetch missing numeric values from given set of values.

Note: Query1, Query2 and Query3 all will give the same output.

Query1:

SELECT ROWNUM FROM ALL_TABLES WHERE ROWNUM<=(SELECT MAX(A) FROM TEMP)

MINUS

SELECT A FROM TEMP;

Query2:

SELECT  (SELECT MIN(A) FROM TEMP)+ ROWNUM

FROM    DUAL

CONNECT BY

LEVEL   <=(SELECT MAX(A) - MIN(A) FROM TEMP)

MINUS

SELECT A FROM TEMP;

Query3:

SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=20

MINUS

SELECT A FROM TEMP

Output:

A
2
4
5
6
7
8
10
11
13
14
17
18