SQL Server: finding values ​​that are not in the table

Question

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

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

Many are the values ​​that I check. Is there a way to find out which values ​​in this set do not exist in column1? Basically, I am looking for the reverse sql statement above.

This is for the report, so all I need is values ​​that do not exist to return.

I have and can do it with the left join and put the values ​​in another table, but the values ​​I check are always different, and hoped to find a solution that would not include clearing the table and inserting the data first. Trying to find the best solution for me, if it exists.

Answer 1

You can also use EXCEPTas well as OUTER JOIN, for example

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

Answer 3

• , , A

• LEFT OUTER JOIN A WHERE Table.column1 IS NULL

  SELECT column1 ON A.column1 = Table.column1 WHERE Table.column1 IS NULL

, A, .

Answer 4

, , ( , ), , .

You can, for example, make a temporary result, which you can join the table to filter out those that 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

Answer 5

One way to do this is: SELECT * FROM WHERE table column1 NOT IN (...);

Answer 6

Use the NOT operator:

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