CODEWITHSUNDEEP
sqloracle11g
gkmohit
gkmohit

Reputation: 710

From a list of numbers I want to get those ids that are not present in the table

I have a list of IDs that I want to check if they are in the employee table. I want to display only those that are not in the table.

SELECT * FROM EMPLOYEE;
EMPLOYEEID|FIRSTNAME|LASTNAME
1         | JOHN    | DOE
2         | JANE    | DOE

When I run the sql statement below I am hoping to get the IDs not present in employee table.

SELECT '1', '2, '3', '4' AS "X1"FROM 
EMPLOYEE WHERE X1 NOT IN (SELECT EMPLOYEEID FROM EMPLOYEE);

Upvotes: 0

Views: 28

Answers (1)

eugenioy
eugenioy

Reputation: 12393

You need to transform those numbers to a subquery, then select the ones that not exists in your table:

with t as
(
select 1 X1 from dual
union
select 2 X1 from dual
union
select 3 X1 from dual
)
SELECT X1 FROM t
WHERE X1 NOT IN (SELECT EMPLOYEEID FROM EMPLOYEE)

If you have a big list of IDs and you will perform some more complex queries, it might be worth looking into creating a temporary table to store them, then use the temporary table instead of the subquery I suggested.

Upvotes: 1

Related Questions