mysql不在列表中_MySQL：选择不在列表中的项目

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?

解决方案

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.