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

One method is

declare @table table(col int)
insert into @table
select 1 union all
select 2  union all
select 3  union all
select 4  union all
select 5 


declare @t table(col int)
insert into @t
select 2 union all
select 3 union all
select 6 union all
select 7 

select t1.col from @t as t1 left join @table as t2 on t1.col=t2.col
where t2.col is null

Answer 2

one way would be to use a temp table:

DECLARE @t1 TABLE (i INT) 
INSERT @t1 VALUES(2) 
INSERT @t1 VALUES(3)
INSERT @t1 VALUES(6) 
INSERT @t1 VALUES(7)

SELECT i FROM @t1 WHERE i NOT IN (Select Col from Tab)

Answer 3

A SQL Server 2008 method

SELECT N FROM (VALUES(2),(3),(6),(7)) AS D (N)
EXCEPT
Select Col from Tab

Or SQL Server 2005

DECLARE @Values XML

SET @Values = 
'<r>
    <v>2</v>
    <v>3</v>
    <v>6</v>
    <v>7</v>
</r>' 


SELECT 
    vals.item.value('.[1]', 'INT') AS Val
FROM @Values.nodes('/r/v') vals(item)
EXCEPT
Select Col from Tab

Answer 4

Do you have a [numbers] table in your database? (See Why should I consider using an auxiliary numbers table?)

SELECT
    [Tab].*
FROM
    [numbers]
    LEFT JOIN [Tab]
        ON [numbers].[num] = [Tab].[Col]
WHERE
    [numbers].[num] IN (2, 3, 6, 7)
    AND [Tab].[Col] IS NULL

Answer 5

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

Answer 6

I think there are many ways to achive this, here is one.

SELECT a.col 
FROM 
  (SELECT 2 AS col UNION ALL SELECT 3 UNION ALL SELECT 6 UNION ALL SELECT 7) AS a
WHERE a.col NOT IN (SELECT col FROM Tab)

Answer 7

Late to the party...

SELECT 
    '2s' = SUM(CASE WHEN Tab.Col = 2 THEN 1 ELSE 0 END),
    '3s' = SUM(CASE WHEN Tab.Col = 3 THEN 1 ELSE 0 END),
    '6s' = SUM(CASE WHEN Tab.Col = 6 THEN 1 ELSE 0 END),
    '7s' = SUM(CASE WHEN Tab.Col = 7 THEN 1 ELSE 0 END)
FROM
(SELECT 1 AS Col, 'Nums' = 1 UNION SELECT 2 AS Col,'Nums' = 1 UNION SELECT 3 AS Col,      'Nums' = 1 UNION SELECT 4 AS Col, 'Nums' = 1 UNION SELECT 5 AS Col, 'Nums' = 1 ) AS Tab
GROUP BY Tab.Nums

---

BTW, mine also gives counts of each, useful if you need it. Like if you were checking a product list against what you have in inventory. Though you can write a pivot for that better, just don't know how off the top of my head.