In what case will referencing a table as an EXISTS be faster than referencing the table as an OUTER JOIN?
When you reference a table in an EXISTS subquery, the optimizer will break out of whatever it's doing to evaluate the subquery as soon as even one row is discovered which satisfies the subquery.
select foo, bar from table1 where exists ( select 1 from table2 where foo = table1.foo and bar = 937 )
Note that the inverse, a NOT EXISTS subquery, does not always have this speed advantage. Sometimes the optimizer ends up retrieving a lot of rows in the subquery before concluding that the condition it's looking for doesn't exist.
An OUTER JOIN, on the other hand, retrieves all the joined rows, and this could be wasteful. An OUTER JOIN would typically be used for the NOT EXISTS scenario, since for the EXISTS scenario, you'd use an INNER JOIN.
select table1.foo, table1.bar from table1 left outer join table2 on table2.foo = table1.foo and table2.bar = 937 where table2.foo is not null
This example might seem a bit contrived, but it does happen in the real world a lot. Before MySQL came out with version 4.1 (which supports subqueries), the OUTER JOIN was the only solution.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading