Advanced Querying - Learning MySQL - page 254

+---------------+-------+
4 rows in set (0.08 sec)
The subquery finds the years that the producers have worked:
mysql> SELECT years FROM producer;
+-------+
| years |
+-------+
| 36 |
| 40 |
| 20 |
| 20 |
| 15 |
+-------+
5 rows in set (0.00 sec)
The outer query goes through each engineer, returning the engineer if their number of
years is greater than any of the values in the set returned by the subquery. So, for
example, Eddie Kramer is output because 38 is greater than at least one value in the set
(36, 40, 20, 15) . The ANY keyword means just that: it's true if the column or expression
preceding it is true for any of the values in the set returned by the subquery. Used in
this way, ANY has the alias SOME , which was included so that some queries can be read
more clearly as English expressions; it doesn't do anything different and you'll rarely
see it used.
The ANY keyword gives you more power in expressing nested queries. Indeed, the pre-
vious query is the first nested query in this section with a column subquery —that is, the
results returned by the subquery are one or more values from a column, instead of a
single scalar value as in the previous section. With this, you can now compare a column
value from an outer query to a set of values returned from a subquery.
Consider another example using ANY . Suppose you want to know the producers who
are also engineers. You can do this with the following nested query:
mysql> SELECT producer_name FROM producer WHERE
-> producer_name = ANY
-> (SELECT engineer_name FROM engineer);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.04 sec)
The = ANY causes the outer query to return a producer when the producer_name is equal
to any of the engineer names returned by the subquery. The = ANY keyphrase has the
alias IN , which you'll see commonly used in nested queries. Using IN , the previous
example can be rewritten:
mysql> SELECT producer_name FROM producer WHERE producer_name
-> IN (SELECT engineer_name FROM engineer);
+---------------+