Oracle - Correlated Subqueries - Nulls in Subqueries

We are top for USA Oracle Training Clients

Oracle support

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.