CODEWITHSUNDEEP
mysqlsqlselect
robsch
robsch

Reputation: 9728

How to realize reverse "NOT IN"?

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

Upvotes: 2

Views: 830

Answers (3)

matthewd
matthewd

Reputation: 331

One quite similar to the OP's non-wokring expectation and a bit simpler than @juergen's suggestion (in terms of syntax) I've found to work quite well for me personally is using the VALUES statement like so:

SELECT value FROM (
    VALUES ('value'), ('a'), ('b'), ('c'), ('e')
) tmp 
WHERE 
    tmp.value != 'value' 
    AND NOT IN (SELECT name FROM A)

You can use this either with a subquery like in the exmaple above as well as with LEFT JOIN which is how I prefer using it:

SELECT value FROM (
    VALUES ('value'), ('a'), ('b'), ('c'), ('e')
) tmp 
LEFT JOIN A ON (tmp.value = A.name)
WHERE 
    tmp.value != 'value'
    AND A.name IS NULL

As you can see in the examples the only thing to account for here is that the first value of this newly created list is used by MySQL as a column name so you need to skip it in the WHERE clause. Hope this helps somebody!

Upvotes: 0

yatin parab
yatin parab

Reputation: 174

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

Upvotes: 0

juergen d
juergen d

Reputation: 204924

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.

Upvotes: 4

Related Questions