CODEWITHSUNDEEP
sqlsql-server
MacAnthony
MacAnthony

Reputation: 4521

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

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.

Upvotes: 3

Views: 8447

Answers (6)

Guffa
Guffa

Reputation: 700780

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453887

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

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425803

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

Upvotes: 3

Cornel Creanga
Cornel Creanga

Reputation: 5308

Use the NOT operator:

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

Upvotes: -2

Daniel Marques
Daniel Marques

Reputation: 1

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

Upvotes: -2

Konerak
Konerak

Reputation: 39773

  • 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.

Upvotes: 1

Related Questions