Databases Reference
In-Depth Information
This works because the subquery returns one scalar value—there's only one engineer
and producer with each name—and so the column subquery operator
IN
isn't neces-
sary. Of course, if names are duplicated, you'd need to use
IN
,
ANY
, or
ALL
instead.
Nested Queries in the FROM Clause
The techniques we've shown all use nested queries in the
WHERE
clause. This section
shows you how they can alternatively be used in the
FROM
clause. This is useful when
you want to manipulate the source of the data you're using in a query.
The
producer
and
engineer
tables store the number of years that a person has been
producing and engineering, respectively. If you want that value in months, there are
several ways you can obtain it. One way—which we'll show you in Chapter 8—is to
use a date and time function to do the conversion. Another way is to do some math in
the query; one option in this class is to do it with a subquery:
mysql>
SELECT producer_name, months FROM
-> (SELECT producer_name, years*12 AS months FROM producer) AS prod;
+---------------+--------+
| producer_name | months |
+---------------+--------+
| Phil Spector | 432 |
| George Martin | 480 |
| Tina Weymouth | 240 |
| Chris Frantz | 240 |
| Ed Kuepper | 180 |
+---------------+--------+
5 rows in set (0.05 sec)
Focus on what follows the
FROM
clause: the subquery uses the
producer
table and returns
two columns. The first column is the
producer_name
; the second column is aliased as
months
, and is the
years
value multiplied by 12. The outer query is straightforward: it
just returns the
producer_name
and the
month
value created through the subquery. Note
that we've added the table alias
as prod
for the subquery. When we use a subquery as
a table, that is, we use a
SELECT FROM
operation on it—this “derived table” must have
an alias—even if we don't use the alias in our query. MySQL complains if we omit the
alias:
mysql>
SELECT producer_name, months FROM
-> (SELECT producer_name, years*12 AS months FROM producer);
ERROR 1248 (42000): Every derived table must have its own alias
Here's another example, where we'll find out the average number of albums that we
own by each artist. Let's begin by thinking through the subquery. It should return the
number of albums that we own by each artist. Then, the outer query should average
the values to give the answer. Here's the query:
mysql>
SELECT AVG(albums) FROM
-> (SELECT COUNT(*) AS albums FROM artist INNER JOIN album
-> USING (artist_id) GROUP BY artist.artist_id) AS alb;
+-------------+
