Advanced Querying - Learning MySQL - page 260

However, it's legal when executed as a subquery because tables listed in the outer query
are allowed to be accessed in the subquery. So, in this example, the current value of
artist_name in the outer query is supplied to the subquery as a constant, scalar value
and compared to the album name. If the album name matches the artist name, the
subquery is true, and so the outer query outputs a row. Consider two cases that illustrate
this more clearly:
• When the artist_name being processed by the outer query is New Order , the sub-
query is false because SELECT * FROM album WHERE album_name = "New Order"
doesn't return any rows, and so the artist row for New Order isn't output as an
answer.
• When the artist_name being processed by the outer query is The Beatles , the sub-
query is true because SELECT * FROM album WHERE album_name = "The Beatles"
returns at least one row. Overall, the artist row for The Beatles is output as an
answer.
Can you see the power of correlated subqueries? You can use values from the outer
query in the inner query to evaluate complex information needs.
We'll now explore another example using EXISTS . Let's try to find all artists from whom
we own at least two albums. To do this with EXISTS , we need to think through what
the inner and outer queries should do. The inner query should produce a result only
when the condition we're checking is true; in this case, it should produce output when
the artist has at least two albums in the database. The outer query should produce the
artist name whenever the inner query is true. Here's the query:
mysql> SELECT artist_name FROM artist WHERE EXISTS
-> (SELECT * FROM album WHERE artist.artist_id = album.artist_id
-> GROUP BY artist.artist_id HAVING COUNT(*) >= 2);
+-------------+
| artist_name |
+-------------+
| New Order |
| Miles Davis |
+-------------+
2 rows in set (0.12 sec)
This is yet another query where nesting isn't necessary and a join would suffice, but
let's stick with this version for the purpose of explanation. Have a look at the inner
query: you can see that the WHERE clause ensures only album rows for the artist being
referenced by the outer query—the current artist—are considered by the subquery. The
GROUP BY clause clusters the rows for that artist, but only if there are at least two albums.
Therefore, the inner query only produces output when there are at least two albums
for the current artist. The outer query is straightforward: it outputs an artist's name
when the subquery produces output.
Here's one more example before we move on and discuss other issues. We've already
shown you a query that uses IN and finds producers who are also engineers: