13

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

3

3 Answers 3

18

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.

7
  • Whoops, should have specified. What if ID is a varchar?
    – NReilingh
    Mar 26, 2013 at 20:52
  • 1
    @NReilingh then redesign your DB :) but it should work the same I think
    – JNK
    Mar 26, 2013 at 20:53
  • I keep getting Incorrect syntax near the keyword 'values'. when running SELECT * FROM (values ('search string'),('other string')) as T(ID)
    – NReilingh
    Mar 26, 2013 at 20:59
  • Your syntax works fine for me in SQL Server 2008r2 - I pasted your comment in and it ran.
    – JNK
    Mar 26, 2013 at 21:01
  • I'm on 2005. Christ.
    – NReilingh
    Mar 26, 2013 at 21:02
6

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];
5

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.