Using Joins and Subqueries - MySQL - page 495

Solution
This is a one-to-many relationship. The solution to this problem involves a join, but the
type of join depends on the question you want answered. To produce a list containing
only master rows for which some detail row exists, use an inner join based on the
primary key in the master table. To produce a list that includes all master rows, even
those with no detail rows, use an outer join.
Discussion
To produce a list from two tables that have a master-detail or parent-child relationship,
a given row in one table might be matched by several rows in the other. These relation‐
ships occur frequently. For example, in business contexts, one-to-many relationships
involve invoices per customer or items per invoice.
This section suggests some master-detail questions that you can ask (and answer) using
the artist and painting tables from earlier in the chapter.
One form of master-detail question for these tables is, “Which paintings did each artist
paint?” This is a simple inner join (see Recipe 14.1 ). Match each artist row to its
corresponding painting rows based on the artist ID values:
mysql> SELECT artist.name, painting.title
-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id
-> ORDER BY name, title;
+----------+-------------------+
| name | title |
+----------+-------------------+
| Da Vinci | Mona Lisa |
| Da Vinci | The Last Supper |
| Renoir | Les Deux Soeurs |
| Van Gogh | Starry Night |
| Van Gogh | The Potato Eaters |
+----------+-------------------+
To also list artists for whom you have no paintings, the join output should include rows
in one table that have no match in the other. That's a form of “find the nonmatching
rows” problem that requires an outer join (see Recipe 14.2 ). Thus, to list each artist
row, whether or not any painting rows match, use a LEFT JOIN :
mysql> SELECT artist.name, painting.title
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> ORDER BY name, title;
+----------+-------------------+
| name | title |
+----------+-------------------+
| Da Vinci | Mona Lisa |
| Da Vinci | The Last Supper |
| Monet | NULL |
| Renoir | Les Deux Soeurs |