Skip to content
Advertisement

How can I perform a SQL ‘NOT IN’ query faster?

I have a table (EMAIL) of email addresses:

and a table (BLACKLIST) of blacklisted email addresses:

and I want to select those email addresses that are in the EMAIL table but NOT in the BLACKLIST table. I’m doing:

but when the row counts get very high the performance is terrible.

How can I better do this? (Assume generic SQL if possible. If not, assume T-SQL.)

Advertisement

Answer

You can use a left outer join, or a not exists clause.

Left outer join:

Not Exists:

Both are quite generic SQL solutions (don’t depend on a specific DB engine). I would say that the latter is a little bit more performant (not by much though). But definitely more performant than the not in one.

As commenters stated, you can also try creating an index on BLACKLIST(EmailAddress), that should help speed up the execution of your query.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement