Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Correlated Subqueries

A correlated subquery  is a subquery that uses values from the outer query.  The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query.  With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.

SQL> select
  2    book_key,
  3    store_key,
  4    quantity
  5  from
  6    sales s
  7  where
  8    quantity < (select max(quantity)
  9                 from sales
 10                 where book_key = s.book_key);

------ ---- ----------
B101   S101       1000
B102   S102         10
B102   S103        200

B116   S105        100
B101   S105       8000
B109   S109        100 

81 rows selected.

In the example above, the subquery references the book_key in the outer query.  The value of the book_key changes by row of the outer query, so the database must rerun the subquery for each row comparison.  This has a significant performance impact on the execution time of the query, and for that reason, correlated subqueries should be avoided if possible.

The outer query knows nothing about the inner query except its results.  For that reason, the outer query cannot reference any columns in the subquery.  However, the subquery has access to the outer query and can reference outer query columns, thus the correlated subquery.

NULLs in Subqueries

If the subquery returns a NULL as one of its values, it is treated like any other NULL in the database.  You cannot compare nor do math on NULLs.  Therefore, if your subquery returns a NULL, you will need to use the NVL function to handle it.

  author_key in (select

In the example above, I used the NVL function to change any NULL author_key into the key A000.

Multi-Column Subqueries

A multi-column subquery  is simply a subquery that returns more than one column. 

SQL> select
  2    book_key
  3  from
  4   sales
  5   where
  6    (store_key,
  7     order_date) in (select
  8                       store_key,
  9                       max(order_date)
 10                     from
 11                       sales join store using (store_key)
 12                     where
 13                       store_state = 'SC'
 14                     group by store_key);


The query lists the books in the latest order from stores in South Carolina.  The subquery returns two columns, a store_key and the date of the latest order.  The comparison is a pair-wise comparison, meaning that the column values are compared as a pair and not individually.  Both must match for the filter to return TRUE.

If I wanted a listing of order numbers that contained sales of book written by authors that live in Missouri or stores that sold the books in South Carolina, I would use a non-pair-wise comparison. 

SQL> select
  2    order_number
  3  from sales
  4  where
  5    book_key in (select book_key
  6                 from
  7                   author join book_author
  8                 where author_state = 'MO')
  9  and
 10    store_key  in (select store_key
 12                   from store
 13                   where store_state = 'SC');


9 rows selected.

In this example, I am comparing two columns, but they are unrelated, and so they must be compared in separate filters.

The above text is an excerpt from:

Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

by John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.