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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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);

BOOK_K STOR   QUANTITY
------ ---- ----------
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.

select
  book_key
from
  book_author
where
  author_key in (select
                   NVL(author_key,'A000')
                 from
                   author);

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);

BOOK_K
------
B111
B109
 

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
                                  using
(author_key)                       
  8                 where author_state = 'MO')
  9  and
 10    store_key  in (select store_key
 12                   from store
 13                   where store_state = 'SC');

ORDER_NUMBER
--------------------
O179
O116      
O146
O183
O159
O161
O200
O162
O109

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:

www.oracle-script.com

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.