CODEWITHSUNDEEP
sqlsql-server
pirmas naujas
pirmas naujas

Reputation: 300

WHERE IN sql query

I need to find which items in WHERE IN clause do not exist in the database. in below example cc33 does not exist and I need the query to give back cc33. how would I do that ?

SELECT id FROM tblList WHERE field1 IN ('aa11','bb22','cc33')

Upvotes: 3

Views: 30480

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

For SQl-Server versions of 2008+, you can use a Table Value Constructor:

SELECT field1
FROM
  ( VALUES
     ('aa11'),('bb22'),('cc33')
  ) AS x (field1)
WHERE field1 NOT IN
      ( SELECT field1 FROM tblList ) ;

Tested at SQL-Fiddle

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You need to put the values into a table rather than a list:

with list as (
    select 'aa11' as val union all
    select 'bb22' union all
    select 'cc33'
)
select l.val
from list l left outer join
     tbllist t 
     on l.val = t.field1
where t.field1 is null

Upvotes: 4

Related Questions