MySQL: select items not in a list

Question

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?

Answer 1

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.