-> IN (SELECT engineer_name FROM engineer WHERE

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 necessary. 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 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 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 inset 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

Download 4.24 Mb.

Share with your friends: