Identifying which values do NOT match a table row

Question

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:

1. SELECT * FROM dbo."TABLE" WHERE "ID" IN ('1','2','3','4'), let's say the table contains no row with ID 2.
2. Dump the results into Excel
3. Run a VLOOKUP on the original list that searches for each list value in the result list.
4. 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

Answer 1

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.

Answer 2

I would build up a table variable or temp table containing the IDs that you're searching for... then use Remus's solution, minus the 2008 syntactic sugar:

declare @t table (ID int)
insert into @t values (1)
insert into @t values (2)
insert into @t values (3)
insert into @t values (4)
insert into @t values (5)

select ID from @t
except
select ID
from [Table];

Answer 3

I'm now a couple years wiser (and have a newer SQL Server) than when I asked this question, so to celebrate the Famous Question badge I got for asking this, here's what I would do now. (I don't think I've ever used the EXCEPT operator since.)

I would say the LEFT JOIN method below is more useful than EXCEPT since you can compose it with other joins without needing a CTE.

SELECT v.val
  FROM (VALUES (1), (2), (3), (4), (5)) v (val)
    LEFT JOIN dbo.SomeTable t
      ON t.id = v.val
  WHERE t.id IS NULL;