SQL Server: Find Values that don't exist in a table

Question

I have a list or set of values that I would like to know which ones do not currently exist in a table. I know I can find out which ones do exist with:

SELECT * FROM Table WHERE column1 IN (x,x,x,x,x)

The set is the values I am checking against. Is there a way to find out which values in that set do not exist in column1? Basically, I'm looking for the inverse of the sql statement above.

This is for a report, so all I need is the values that don't exist to be returned back.

I have and could do this with a left join and putting the values in another table, but the values I check are always different and was hoping to find a solution that didn't involve clearing a table and inserting data first. Trying to find a better solution for me if one exists.

Answer 1

• Put the values you want to check for in a table A

• LEFT OUTER JOIN the table A against your Table WHERE Table.column1 IS NULL

  SELECT column1 FROM A LEFT OUTER JOIN Table ON A.column1 = Table.column1 WHERE Table.column1 IS NULL

This will only show the rows that exist in A but not in Table.

Answer 2

One way you can do it is: SELECT * FROM Table WHERE column1 NOT IN(...);

Answer 3

Use the NOT operator:

SELECT * FROM Table WHERE column1 NOT IN (x,x,x,x,x)

Answer 4

WITH    q(x) AS
        (
        SELECT  x1
        UNION ALL
        SELECT  x2
        UNION ALL
        SELECT  x3
        )
SELECT  x
FROM    q
WHERE   x NOT IN
        (
        SELECT  column1
        FROM    [table]
        )

Answer 5

You can also use EXCEPT as well as the OUTER JOIN e.g.

SELECT * FROM
(
SELECT -1 AS N
UNION 
SELECT 2 AS N
) demo 
EXCEPT 
SELECT     number
FROM         spt_values

Answer 6

As you want some of the values from the set in the result, and you can't take them from the table (as you want the ones that doesn't exist there), you have to put the set in some kind of table or result so that you can use that as source.

You can for example make a temporary result, that you can join against the table to filter out the ones that does exist in the table:

select set.x
from (
  select 1 as x union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5
) as set
left join Table as t on t.column1 = set.x
where t.columnn1 is null