Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in April 2006

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.