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

August 11, 2004

[From Builder UK]

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,39026540,39214308,00.htm

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers