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

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 2

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]
        )