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.
This was first published in April 2006