Databases Reference
In-Depth Information
| producer_name |
+---------------+
| George Martin |
+---------------+
1 row in set (0.06 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 in set (0.17 sec)
Again, nested queries are expressive but typically slow in MySQL, so use 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
-> engineer_name NOT IN
-> (SELECT producer_name FROM producer);
+---------------+
| engineer_name |
+---------------+
| Eddie Kramer |
| Jeff Jarratt |
| Ed Stasium |
+---------------+
3 rows in set (0.25 sec)
As an exercise, try writing the above query using the
ANY
syntax and in at least two ways
as a join query.
