Trinculo Trinculo - 5 years ago 169
SQL Question

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?

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download