-> FROM engineer WHERE years > ANY

+---------------+-------+
4 rows inset sec)
The subquery finds the years that the producers have worked:
mysql> SELECT years FROM producer;
+-------+
| years |
+-------+
| 36 |
| 40 |
| 20 |
| 20 |
| 15 |
+-------+
5 rows inset 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, 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 previous 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

Download 4.24 Mb.

Share with your friends: