Database Reference
In-Depth Information
▼
Output
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
▼
Analysis
This
SELECT
statement returns three columns for every customer in the
customers
table:
cust_name
,
cust_state
, and
orders
.
orders
is a cal-
culated field that is set by a subquery provided in parentheses. That subquery
is executed once for every customer retrieved. In this example, the subquery is
executed five times because five customers were retrieved.
The
WHERE
clause in the subquery is a little different from the
WHERE
clauses
used previously because it uses fully qualified column names (first mentioned in
Chapter 4, “Retrieving Data”). The following clause tells SQL to compare the
cust_id
in the
orders
table to the one currently being retrieved from the
customers
table:
WHERE orders.cust_id = customers.cust_id
New Term
Correlated subquery A subquery that refers to the outer query.
The type of subquery is called a
correlated subquery
. This syntax—the table name
and the column name separated by a period—must be used whenever there is
possible ambiguity about column names. Why? Well, let's look at what happens
if fully qualified column names are not used:
▼
Input
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;
