Brabster Brabster - 2 months ago 6
SQL Question

Best way to check that a list of items exists in an SQL database column?

If I have a list of items, say

apples
pairs
pomegranites


and I want to identify any that don't exist in the 'fruit' column in an SQL DB table.


  • Fast performance is the main concern.

  • Needs to be portable over different SQL implementations.

  • The input list could contain an arbitrary number of entries.



I can think of a few ways to do it, thought I'd throw it out there and see what you folks think.

Answer

Since the list of fruits you are selecting from can be arbitrarily long, I would suggest the following:

create table FruitList (FruitName char(30))
insert into FruitList values ('apples'), ('pears'), ('oranges')

select * from FruitList left outer join AllFruits on AllFruits.fruit = FruitList.FruitName
where AllFruits.fruit is null

A left outer join should be much faster than "not in" or other kinds of queries.