Identifying which values do NOT match a table row I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.

To better explain, here's what I would do now, to check which IDs of the list "1, 2, 3, 4" do not exist in a table:


SELECT * FROM dbo."TABLE" WHERE "ID" IN ('1','2','3','4'), let's say the table contains no row with ID 2.
Dump the results into Excel
Run a VLOOKUP on the original list that searches for each list value in the result list.
Any VLOOKUP that results in an #N/A is on a value that did not occur in the table.


I'm thinking there's got to be a better way to do this. I'm looking, ideally, for something like


  List to check -> Query on table to check -> Members of list not in table
Use EXCEPT:

SELECT * FROM
  (values (1),(2),(3),(4)) as T(ID)
EXCEPT
SELECT ID 
FROM [TABLE];


See SqlFiddle.



The values constructor will only work on SQL Server 2008 or later. For 2005, use

SELECT 'value'
UNION SELECT 'value'


as detailed in this SO answer.