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

Question

If I print:

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

I can get entries from a table that are not in the list. I want to do the opposite: get values ​​from a list that is not in the table. For example, if there is a column in the table with the name name that has the values ​​"Test1" and "Test3", I want to compare this with ("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 any way to do this WITHOUT creating a new table with all the values ​​in the list?

Answer 1

Depending on how many values ​​you have, you can make several alliances.

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) 

Answer 2

I usually use SELECT 'FOO' AS COL UNION SELECT 'BAR' , etc., and then use the standard left join idiom and check for NULL to find the 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 

Produces output

 name -------------------------------------------------- Test1 Test3 (2 row(s) affected) testName -------- Test2 (1 row(s) affected) 

Answer 3

 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 a temp table #

Answer 4

Assuming the "othertable" contains 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