Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Please don't make us guess the version of SQL Server? –  Aaron Bertrand Mar 26 '13 at 20:41
    
NOT IN / EXISTS? stackoverflow.com/questions/173041/not-in-vs-not-exists –  Eric Higgins Mar 26 '13 at 20:42
    
Apologies. [edited] It's old. The problem with NOT IN is it'll return everything else in the table... –  NReilingh Mar 26 '13 at 20:45
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
Whoops, should have specified. What if ID is a varchar? –  NReilingh Mar 26 '13 at 20:52
1  
@NReilingh then redesign your DB :) but it should work the same I think –  JNK Mar 26 '13 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 '13 at 20:59
    
Your syntax works fine for me in SQL Server 2008r2 - I pasted your comment in and it ran. –  JNK Mar 26 '13 at 21:01
    
I'm on 2005. Christ. –  NReilingh Mar 26 '13 at 21:02
show 2 more comments

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];
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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