Database Reference
In-Depth Information
Although usually used in conjunction with the
IN
operator, subqueries can also
be used to test for equality (using
=
), nonequality (using
<>
), and so on.
Caution
Subqueries and Performance The code shown here works, and it achieves the desired
result. However, using subqueries is not always the most efficient way to perform this
type of data retrieval, although it might be. More on this is in Chapter 15, “Joining
Tables,” where you revisit this same example.
Another way to use subqueries is in creating calculated fields. Suppose you
want to display the total number of orders placed by every customer in your
customers
table. Orders are stored in the
orders
table along with the appro-
priate customer ID.
To perform this operation, follow these steps:
1.
Retrieve the list of customers from the
customers
table.
2.
For each customer retrieved, count the number of associated orders in
the
orders
table.
As you learned in the previous two chapters, you can use
SELECT COUNT(*)
to count rows in a table, and by providing a
WHERE
clause to filter a specific
customer ID, you can count just that customer's orders. For example, the fol-
lowing code counts the number of orders placed by customer
10001
:
▼
Input
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;
To perform that
COUNT(*)
calculation for each customer, use
COUNT*
as a
subquery. Look at the following code:
▼
Input
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
