In what case will referencing a table as an EXISTS be faster than referencing the table as an OUTER JOIN?
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation