 |
|
Oracle Concepts by Burleson
Consulting |
SQL Subqueries and Aggregation
A subquery is a condition where an SQL query is
“nested” or placed inside another SQL query. The ISO 99 SQL standard
allows for SQL queries to be embedded inside other SQL statements in
several ways. SQL queries can be placed inside the SELECT clause
(scalar subqueries), inside the FROM clause (in-line views), and
inside the WHERE clause (basic subqueries).
The ability to nest SQL statements inside each
other provides tremendous power to SQL and allows SQL statements to
perform extremely complex processing without using a procedural
language such as PL/SQL.
In relational terminology, the main query is
called the outer query, and the subquery is often referred to as the
inner query.
There are several
types of basic SQL subqueries, all defined by the types of SQL
predicate in the WHERE clause, so let’s take a closer look at
these types of subqueries:
Basic subqueries
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 sample 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.
Where column > (subquery)
The following subquery selects those employees
whose salary is greater than the minimum salary for their jobs.
select
emp_last_name
from
emp
where
emp_salary > (select job_min_sal from job
where
emp.job_key = job.job_key)
;
EMP_LAST_NAME
------------------------------
king
jackson
korn
linus
tokheim
johnson
baker
coleman
brannigan
9 rows selected.
This is known as
a correlated subquery because the subquery references the outer query
in the subqueries WHERE clause. Internally, correlated subqueries are
very expensive to process because the inner query must be executed for
every row returned by the outer query.
Where column = (subquery)
This type of
subquery checks where a condition in the outer query is equal to a
condition in the subquery. In this example we are locating all book
titles that belong to a publisher.
select
book_title
from
book
where
pub_key =
(select pub_key from publisher
where
publisher.pub_key = book.pub_key)
;
BOOK_TITLE
-----------------------------------------------------------------
windows
sucks
piano
greats
DOS for
dummies
The zen of
auto repair
zero loss
finance
operations
research theory
non violins
in the workplace
UNIX for
experts
pay no taxes
and go to jail
the fall of
microsoft
writers
market
managing
stress
bears are
people too
reduce
spending the republican
way
the willow
weeps no more
Oracle 10g
sql tuning
was george
washington gay?
cooking
light
never eat
boogers
how to
housebreak your
horse
20 rows
selected.
Where column < (subquery)
In this example,
we display the last names of all employees whose salary is less than
the minimum salary for their job grade. Note that this is a
correlated subquery because the inner query references the outer
query.
select
emp_last_name
from
emp
where
emp_salary < (select job_min_sal from job
where
emp.job_key = job.job_key)
;
EMP_LAST_NAME
------------------------------
levender
Where exists (subquery)
The WHERE EXISTS
subquery is used when we want to display all rows where we have a
matching column in both tables. In most cases, this type of subquery
can be re-written with a standard join to improve performance.
select
book_key
from
book
where
exists
(select book_key from sales)
;
BOOK_K
------
B101
B102
B103
B104
B105
B106
B107
B108
B109
B110
B111
B112
B113
B114
B115
B116
B117
B118
B119
B120
Where not exists (subquery)
The WHERE NOT
EXISTS subquery is used to display cases where a selected column does
not appear in another table.
select
author_key
from
author
where
not
exists (select author_key from book_author)
;
As a general
rule, the use of the NOT EXISTS subqueries are discouraged because the
query can often be re-written as a standard join with much faster
performance.
Where column in (subquery)
select
book_title
from
book
where
pub_key
in (select pub_key from publisher
where
publisher.pub_key = book.pub_key)
;
BOOK_TITLE
-----------------------------------------------------------------
windows
sucks
piano
greats
DOS for
dummies
The zen of
auto repair
zero loss
finance
operations
research theory
non violins
in the workplace
UNIX for
experts
pay no taxes
and go to jail
the fall of
microsoft
writers
market
managing
stress
bears are
people too
reduce
spending the republican way
the willow
weeps no more
Oracle 10g sql tuning
was george
washington gay?
cooking
light
never eat boogers
how to
housebreak your
horse
20 rows
selected.
Where column not in (subquery)
In this example, we select all books that do not
have sales. Note that this is a non-correlated subquery.
select
book_key
from
book
where
book_key not in (select book_key from sales)
;
BOOK_K
------
B117
B118
B119
B120
There are serious problems with subqueries that
may return NULL values. It is a good idea to discourage the use of the
NOT IN clause (which invokes a subquery) and to prefer NOT EXISTS
(which invokes a correlated subquery), since the query returns no rows
if any rows returned by the subquery contain null values.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
