SQL query to find given IDs not in table

Database

SQL query to find given IDs not in table

Consider there is table with some ids. I want to search the ids that are not available in that table.

For eg: The table

id

--------

1991

1992

As of now, am writing this query

"Select * from table_name where id in (1991,1992,1993)"

From the result, I am manually finding out that 1993 is missing.

Is there any query to achieve it? That query should directly give me 1993.

Tags (1)
1 REPLY
Senior Apprentice

Re: SQL query to find given IDs not in table

You need to split the list of values into rows:

WITH cte AS   
(
SELECT CAST(token AS INT) AS id
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '1991,1992,1993', ',')
RETURNS (outkey INTEGER,
tokennum INTEGER,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d
)
SELECT * FROM cte
WHERE NOT EXISTS
( SELECT * FROM tab AS t WHERE t.id = cte.id)