Java Reference
In-Depth Information
(SELECT *
FROM Ordered_Items oi
WHERE c.Customer_Number = o.Customer_Number AND
oi.Order_Number = o.Order_Number AND
oi.Item_Number = i.Item_Number);
In this example, most of the tables the subquery accesses are defined in the main
query. This query returns the following result set:
First_Name
Last_Name
Order_Number
Item_Number
Name
Fredo
Corleone
2
1005
Chocolate Chip
Francis
Corleone
3
1006
Fig Bar
Kay
Adams
5
1006
Fig Bar
Kay
Adams
5
1007
Sugar Cookies
Correlated subqueries depend on a value in the outer query. A reference to a table in
the outer query is called a
correlated reference
. Correlated queries are executed
repeatedly, once for each row of the table identified in the outer-level query, so they
can be extremely inefficient. It is frequently worth rewriting correlated queries as joins
where possible, though in some cases the SQL engine may be able to optimize the
correlated subquery.
Nesting subqueries
Just as you can use a subquery within a query, you can also use a subquery within
another subquery. Subqueries can be nested as deeply as your implementation of
SQL allows. The syntax for nesting subqueries looks like this:
SELECT *
FROM Tables
WHERE
( SUBQUERY
(SUBQUERY
(SUBQUERY)));
Additional uses of subqueries
Just as you can use calculated values, or even literals, in place of simple data-field
values in the SELECT clause of a query, you can also use the results subqueries
return. This can be useful if you want to create a summary comparing the cost of an
item against another value retrieved from the table, such as an average cost of all
similar items. Here's an example:
