Oracle Tip: Understand the difference between IN and EXISTS in subqueries

Choosing the right clause in complex queries can have a large effect on performance. Determine which to use in your code

When coding a SQL statement with tables in master-detail relationships, it's common to have to decide whether to write the query using the WHERE EXISTS (. . .) clause or the WHERE value IN (. . .) clause. You may resist using WHERE EXISTS because it has the awkward syntax of returning a value, which you always ignore.

However, there's a difference when using rule-based optimisation. You can determine the performance of a rule-based query by understanding which table is the driving table and how many rows each part returns.

The article continues at http://uk.builder.com/0,39026540,39214308,00.htm