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 expression
(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 atypical 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 inset 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 writing 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.
Share with your friends: 
