Query: find rows that do not belong to a list of values

Question

Lets consider I have a table 'Tab' which has a column 'Col'

The table 'Tab' has this data -

Col
1
2
3
4
5

If I have a set of values (2,3,6,7). I can query the values that are present in the table and the list by suing the query

Select Col from Tab where col IN (2,3,6,7)

But, if I want to return the values in the list that are not present in the table i.e. only (6,7) in this case. What query should I use?

Answer 1

The problem I believe is that your trying to find values from you in statement. What you need to do is turn your in statement into a table and then you can determine which values are different.

create table #temp
(
value int
)

insert into #temp values 1
insert into #temp values 2
insert into #temp values 3
insert into #temp values 4

select
 id
from
 #temp
where
 not exists (select 1 from Tab where Col = id)

A better alternative would be to create a table-valued function to turn your comma-delimited string into a table. I don't have any code handy, but it should be easy to find on Google. In that case you would only need to use the syntax below.

select
 id
from
 dbo.SplitStringToTable('2,3,6,7')
where
 not exists (select 1 from Tab where Col = id)

Hope this helps