EXPERT RESPONSE
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.
|
