Erik Darling shares some advice:
I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:
- When I have a list of literal values
That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.
I’m typically a lot more flexible about using IN, though I do agree with NOT IN: that clause is usually more trouble than it’s worth.