 |
|
Oracle Tips by Burleson |
Subqueries
Subqueries
are queries within queries. They
can exist in almost any part of the main query to include the SELECT,
FROM, WHERE and HAVING
clauses. In the SELECT clause, a subquery can return a value that becomes a column in your results.
SQL> select
2 author_last_name,
3 book_title,
4 (select
5 max(order_date)
6 from
7 sales) "Last Order"
8 from
9 author a join book_author ba using (author_key)
10 join book b using (book_key);
AUTHOR_LAST_NAME
----------------------------------------
BOOK_TITLE
---------------------------------------------------------------
Last Order
---------
hester
windows success
21-MAY-04
jones
windows success
21-MAY-04
jeckle
piano greats
21-MAY-04
weaton
piano greats
21-MAY-04
…
The subquery returns the max order date from the
sales table. Notice that the sub query returns a single value.
A subquery in the FROM clause will create a table
in memory that can be used by the outer query to select from.
SQL> select
2 name,
3 sum(quantity)
4 from
5 sales join (select
6 store_name name,
7 store_key key
8 from
9 store) str on (sales.store_key = str.key)
10 group by name;
NAME SUM(QUANTITY)
---------------------------------------- -------------
barnes and Noble 2180
blue ride booksellers 5400
books for dummies 13000
borders 21860
eaton books 12120
hot wet and mushy books 24700
ignoramus and dufus 3610
quagmire books 7900
specialty bookstore 6080
wee bee books 13700
10 rows selected.
Here, my subquery becomes the table STR for the
outer query. Subqueries
in the FROM clause can be sorted. Subqueries in other clauses cannot contain an ORDER BYclause.
The most common place for a subquery is in the
WHERE clause.
SQL> select
2 store_key,
3 book_key
4 from
5 sales
6 where
7 book_key in (select
8 book_key
9 from
10 book_author
11 where
12 author_key in ('A101','A103','A106'));
STOR BOOK_K
---- ------
S104 B101
S105 B101
S110 B101
S103 B101
S101 B101
S106 B102
S107 B102
S110 B102
…
S102 B114
S101 B114
S102 B115
S104 B115
S104 B115
S106 B115
S101 B115
32 rows selected.
Here my subquery provided a list of
book_key to use in filtering
the outer query’s results. This subquery returned multiple values.
As you can see,
subqueries can be a very powerful feature of SQL; however, it can also
cause your SQL to be confusing and to perform poorly. A subquery
follows the format of a normal SELECT query statement, but it is
contained in parentheses and does not end in a semicolon. Subqueriescan be nested to any
level. The inner most suquery is evaluated first. As we have seen in
the examples above, a subquery can return a single row or multiple
rows. Each type of subquery must be handled correctly for the query
to execute correctly.
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
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
