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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: