CODEWITHSUNDEEP
sql
Trinculo
Trinculo

Reputation: 2021

Select values from a table that are not in a list SQL

If I type:

SELECT name FROM table WHERE name NOT IN ('Test1','Test2','Test3');

I can get the entries from the table that are not in the list. I want to do the opposite: Get the values from the list that are not in the table. For example, if table has a column named name that has the values 'Test1' and 'Test3' I want to compare that to ('Test1','Test2','Test3') and return Test2. Or as another example, if the table is empty, then return everything in the list: Test1, Test2, and Test3.

Is there a way to do this WITHOUT creating a new table with all of the values in the list?

Upvotes: 16

Views: 53899

Answers (5)

Paramesh Korrakuti
Paramesh Korrakuti

Reputation: 2067

In SQL server, the below query works well.

SELECT v.val FROM (VALUES 
    ('A'), 
    ('B'), 
    ('C'), 
    ('D'), 
    ('E') 
) v (val)
LEFT JOIN dbo.TABLE_NAME t ON t.COLUMN_NAME = v.val
WHERE t.COLUMN_NAME IS NULL;

Can find the below output:

val
-------
A
B
C
D

Upvotes: 0

rdn87
rdn87

Reputation: 724

Select a.value from (
SELECT 'testvalue' value UNION
SELECT 'testvalue2' value UNION
SELECT 'testvalue3' value UNION
SELECT 'testvalue4' value UNION
) a
left outer join othertable b
on a.value=b.value
where b.value is null

This is perfect for my problem without temp table#

Upvotes: 1

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

I usually use SELECT 'FOO' AS COL UNION SELECT 'BAR' etc and then use the standard idiom of left joining and checking for NULL to find missing elements.

CREATE TABLE #YourTable(
name nvarchar(50)
)

insert into #YourTable (name) values ('Test1'), ('Test3')

-- ALL
select * from #YourTable

--MISSING
select t1.* from (
  select 'Test1' testName
  union select 'Test2'
  union select 'Test3') as t1
  left outer join #YourTable yt on t1.testName = yt.name
  where yt.name is null

DROP TABLE #YourTable

Gives output

name
--------------------------------------------------
Test1
Test3

(2 row(s) affected)

testName
--------
Test2

(1 row(s) affected)

Upvotes: 2

David W
David W

Reputation: 10184

Assuming "othertable" holds the table in question...

 select a.value from 
    (select 'test1' value
     union
     select 'test2' value
     union 
     select 'test3' value) a
       left outer join othertable b
         on a.value=b.value
      where b.value is null

Upvotes: 0

aquinas
aquinas

Reputation: 23796

Depending on how many values you have, you could do a few unions.

See: http://www.sqlfiddle.com/#!5/0e42f/1

select * from (
  select 'Test 1' thename union
  select 'Test 2' union 
  select 'Test 3'
)
where thename not in (select name from foo)

Upvotes: 11

Related Questions