Q

EXISTS subquery or OUTER JOIN?

In what case will referencing a table as an EXISTS be faster than referencing the table as an OUTER JOIN?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close