CODEWITHSUNDEEP
sql-serversql-server-2008
sridharnetha
sridharnetha

Reputation: 2248

How to return ids that are not in the table using SQL Server

I would like to check whether the IDs are present or not in the table then return IDs that are not present in the table from a where clause using SQL Server.

Here is my SQL query,

select <columname> 
from tablename 
where ID in (10, 20, 30, 40, 100, 200)

If 20, and 30 exists in table then output should return 10,40,100,200

Please help me someone!

Upvotes: 0

Views: 43

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

You can use LEFT JOIN:

SELECT sub.ID
FROM (SELECT 10 AS id
      UNION ALL SELECT 20
      UNION ALL SELECT 30
      UNION ALL SELECT 40
      UNION ALL SELECT 100
      UNION ALL SELECT 200) AS sub
LEFT JOIN tablename  t
  ON sub.id = t.id
WHERE t.id IS NULL;

You can change subquery with VALUES () or table-valued parameter if needed.

Upvotes: 0

Related Questions