Danny Ocean (8/6/2013)
Most of time i try to avoid Exists clause. I will prefer join or sub query.
Good question 🙂
Exists IS (or rather, uses) a subquery. If you mean that you prefer to use a subquery with IN rather than EXISTS, you're opening yourself up for another surprise - NULL values don't behave as many people expect in a NOT IN. And you cant use [NOT] IN if you need a match on two or more columns.
Joining avoids the subquery (but would produce the same unwanted results if you fail to qualify the columns with table names). Joining also has the problems that (a) if you use it to simulate a NOT IN, you use a bit of logic that many people don't immediately understand (the socalled "anti semi join") - a risk for future maintenance; and (b) if you use it to simulate an IN, you can get extra rows if the match criterium is non-unique in the joined table.
My coding style is to:
* Use joins only when I actually need columns from all joined tables;
* Use EXISTS and NOT EXISTS for any check of existence in a different table (or view) source;
* Use IN and NOT IN only with a list of constants, never with a subquery (because that can be done with IN).
And to avoid the problem highlighted in this QotD, I religiously follow these two additional guidelines:
* For any query involving two or more tables, I assign an alias to each table (not really required, but makes the next guideline easier); and
* For any query involving two or more tables, I prefix every column with the proper table alias.
This not only prevents unexpected results when misspelling a column name, it also makes my queries easier to understand and maintain for whoever inherits them when I leave.