 |
|
Oracle Tips by Burleson |
Single Row Subquery
A single row subquery returns only one row.
It can be used with the equal comparison operators (=,<,>,<>,
etc).
SQL> select
2 order_number
3 from
4 sales
5 where quantity = (select
6 max(quantity)
7 from
8 sales);
ORDER_NUMBER
--------------------
O161
Here, we get the order number for the order
that contains the max quantity value. The outer query can return
multiply rows, but the subquery can only return one row.
SQL> select
2 order_number
3 from
4 sales
5 where quantity > (select
6 avg(quantity)
7 from
8 sales);
ORDER_NUMBER
--------------------
O118
O149
O157
O161
O167
O179
O183
O186
O189
O193
O196
O197
O198
O199
14 rows selected.
Here, we want the order numbers where the
order quantity is above the average quantity in the sales table.
Notice that the subquery returns one and only one row that used in
the filter.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
