Advanced Querying - Learning MySQL - page 257

You can see there's a different syntax being used in this nested query: a list of two
column names in parentheses follows the WHERE statement, and the inner query returns
two columns. We'll explain this syntax next.
The row subquery syntax allows you to compare multiple values per row. The expres-
sion (producer_name, years) means two values per row are compared to the output of
the subquery. You can see following the IN keyword that the subquery returns two
values, engineer_name and years . So, the fragment:
(producer_name, years) IN (SELECT engineer_name, years FROM engineer)
matches producer names and years to engineer names and years, and returns a true
value when a match is found. The result is that if a matching pair is found, the overall
query outputs a result. This is a typical row subquery: it finds rows that exist in two
tables.
To explain the syntax further, let's consider another example. Suppose you want to see
if you own the Brotherhood album by New Order. You can do this with the following
query:
mysql> SELECT artist_name, album_name FROM artist, album WHERE
-> (artist.artist_id, artist_name, album_name) =
-> (album.artist_id, "New Order", "Brotherhood");
+-------------+-------------+
| artist_name | album_name |
+-------------+-------------+
| New Order | Brotherhood |
+-------------+-------------+
1 row in set (0.16 sec)
It's not a nested query, but it shows you how the new row subquery syntax works. You
can see that the query matches the list of columns before the equals sign,
(artist.artist_id, artist_name, album_name) , to the list of columns and values after
the equals sign, (album.artist_id, "New Order", "Brotherhood") . So, when the
artist_id values match, the artist is New Order, and the album is Brotherhood , we get
output from the query. We don't recommend writing queries like this—use a WHERE
clause instead—but it does illustrate exactly what's going on. For an exercise, try writ-
ing this query using a join.
Row subqueries require that the number, order, and type of values in the columns
match. So, for example, our previous example matches a SMALLINT to a SMALLINT , and
two character strings to two character strings.
The EXISTS and NOT EXISTS Clauses
You've now seen three types of subquery: scalar subqueries, column subqueries, and
row subqueries. In this section, you'll learn about a fourth type, the correlated sub-
query , where a table used in the outer query is referenced in the subquery. Correlated