||Oracle Tips by Burleson
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.
6 sales s
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
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.
A multi-column subquery
is simply a subquery that returns more than one column.
7 order_date) in (select
11 sales join store using (store_key)
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
3 from sales
5 book_key in (select book_key
7 author join book_author
8 where author_state = 'MO')
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
by John Garmany
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA