-> (SELECT engineer_name FROM engineer)

| producer_name |
+---------------+
| George Martin |
+---------------+
1 row inset sec)
Of course, for this particular example, you could also have used a join query:
mysql> SELECT producer_name FROM producer INNER JOIN engineer
-> ON (producer_name = engineer_name);
+---------------+
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row inset sec)
Again, nested queries are expressive but typically slow in MySQL, souse a join where you can.
Using ALL
Suppose you want to find engineers who are more experienced than all of the producers
—that is, more experienced than the most experienced producer. You can do this with the
ALL
keyword in place of
ANY
:
mysql> SELECT engineer_name, years FROM engineer
-> WHERE years > ALL (SELECT years FROM producer);
Empty set (0.00 sec)
You can see that there are no answers looking at the data, we see that George Martin has been a producer for 40 years, equal to or longer than the time any engineer has been engineering. While the
ANY
keyword returns values that satisfy at least one condition
(Boolean OR, the
ALL
keyword returns values when all the conditions are satisfied
(Boolean AND).
We can use the alias NOT IN
in place of
<> ANY
or !
= ANY. Let's find all the engineers who aren't producers:
mysql> SELECT engineer_name FROM engineer WHERE

Download 4.24 Mb.

Share with your friends: