 |
|
Oracle SQL: Basic SQL subqueries
Oracle Tips by Burleson Consulting
|
When we see a SQL statement that specifies a subquery, we first
need to carefully check the where clause and determine if the
subquery is a noncorrelated subquery or a correlated subquery. A
correlated subquery is a query whereby the key in the subquery is
correlated (using the = operator) with a column that is selected in
the outer query.
On the other hand, a noncorrelated subquery is a
query where the subquery executes independently of the outer query,
passing a result set to the outer query at the end of its execution.
Noncorrelated subqueries are commonly seen when using the IN, NOT
IN, EXISTS, and NOT EXISTS SQL clauses.
In a basic subquery, an SQL statement is embedded inside the
WHERE clause of the query. In this example, we locate all authors
who have not yet published a book.
select
author_last_name
from
author
where
author_key not in
(select author_key from book_author);
This type of query is called a non-correlated subquery because
the subquery does not make any references to the outside query. To
see how this works, copy this code from this screen and run it
against your pubs database. You should get the following output:
AUTHOR_LAST_NAME
----------------------------------------
clark
mee
In this example, we select all authors who have not yet published
their first book. Internally, this query reads all author_key values
from the book_author table and then compares this result set with
the author_key value in the author table. The savvy Oracle SQL tuner
is always on the lookout for both correlated and noncorrelated
subqueries for several reasons.
The foremost is to search for
opportunities for replacing the subquery with a standard join, and
the other is to examine the uniqueness of the indexes in the
subquery to see if changing the index structure can change the table
access method.
Note:
Prior to Oracle10g there was a bug that caused a huge
execution difference between EXISTS and IN. Starting in 10g
release 2 and beyond, Oracle \will automatically rewrite an IN
subquery to use the EXISTS clause.
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
|
