Very often I hear people wondering if it’s better the EXISTS or the IN operator. Let’s start by saying that the two operators behave differently when NULL values are involved (see this post). If you are sure that the field used to join the tables does not include NULL values then the IN operator “generally” performs better than EXISTS. I say “generally” because this is not always the case. For example consider the following query:
When you were at the university or in any programming course, you–like me–were probably thought of the importance of optimization. In the real world though, you should strive for writing good code–possibly using bullet-proof Design Patterns–and defer optimization until later. Actually, you should optimize your code only if, after testing, you realize that optimization is strongly necessary. There are three aphorisms concerning optimization that you cannot but know: More computing sins are committed in the name of efficiency (without necessarily achieving it) than for any other single reason, including blind stupidity.