Brabster Brabster - 9 months ago 38
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


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.


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.