How to realize reverse "NOT IN"?

Question

I've got a table A with some data:

name
------
a
b
d

And I've got a list of names ('a', 'b', 'c', 'e') from which I would like to get those values that are not in the table. How can I do this? Which options do I have? This is not working (of course):

SELECT value FROM ('a', 'b', 'c', 'e') WHERE value NOT IN (SELECT name FROM A)

In this example the expected result would be:

value
------
c
e

score 4 · Answer 1 · answered Jul 02 '16 at 10:11

4

You can build a temp table with union and then join against it

select tmp.name
from
(
  select 'a' as name
  union all 
  select 'b'
  union all 
  select 'c'
  union all 
  select 'e'
) tmp
left join your_table t on t.name = tmp.name
where t.name is null

When you have more than just a few values I recommend putting those values into a real table and then join against it.

answered Jul 02 '16 at 10:11

juergen d

201,996
37
293
362

I see. Yes, I have more values. So another table is the only option then? – robsch Jul 02 '16 at 10:20
Another table is the only real option here. – juergen d Jul 02 '16 at 10:21

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

Go to given below link you will get SPLIT function , just create this function it will be for future developement also. How to split string and insert values into table in SQL Server

After that Excecute the below query.

SELECT * 
into #temp
FROM [dbo].Split('a,b,c,e', ',')

select * from #temp where item not in (select * from A)

drop table #temp

edited May 23 '17 at 12:30

Community

1
1

answered Jul 02 '16 at 12:50

yatin parab

174
6

How to realize reverse "NOT IN"?

2 Answers2