CODEWITHSUNDEEP
mysql
luqita
luqita

Reputation: 4085

MySQL: select items not in a list

How can I select items that are in a list but not in the table? For example, if I am given a list like: 'apple', 'banana', 'carrot' and I have a table like:

fruit:
------
apple
banana

I want the result of my query to come as 'carrot', since it was the element in the list provided that is not present in the table. How do I do this?

Upvotes: 5

Views: 5492

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53880

The best way to accomplish this is to put your list in a table as well:

Table fruitable:
fruit 
------
apple
banana

Table fruitable_list:
fruit
------
apple
banana
carrot

Then your query becomes:

SELECT fruitable_list.fruit FROM fruitable_list
LEFT JOIN fruitable
  ON fruitable.fruit = fruitable_list.fruit
WHERE fruitable.fruit IS NULL

Results in:

fruit
------
carrot

It's hard to return a record in a result set that's not in the data set.

Upvotes: 5

zerkms
zerkms

Reputation: 255155

SELECT v.fruit FROM (
    SELECT 'apple' AS fruit
    UNION ALL
    SELECT 'banana'
    UNION ALL
    SELECT 'carrot') v
LEFT JOIN friuttable ft ON ft.fruit = v.fruit
    WHERE ft.fruit IS NULL

Another:

SELECT fruit
  FROM fruittable
 WHERE FIND_IN_SET(fruit, 'apple,banana,carrot') = 0

Upvotes: 3

Related Questions