Using Joins and Subqueries - MySQL - page 484

at all in painting , but an inner join can only produce results based on values that are
present in both tables. It can't tell you anything about values that are missing from one
of them.
When faced with the need to find values in one table with no match in (or missing from)
another table, you should get in the habit of thinking, “Aha, that's a LEFT JOIN prob‐
lem.” A LEFT JOIN is one type of outer join: it's similar to an inner join in that it matches
rows in the first (left) table with rows in the second (right) table. In addition, if a left
table row has no match in the right table, a LEFT JOIN still produces a row—one in which
all the columns from the right table are set to NULL . This means you can find values that
are missing from the right table by looking for NULL . It's easier to understand how this
happens by working in stages. Begin with an inner join that displays matching rows:
mysql> SELECT * FROM artist INNER JOIN painting
-> ON artist.a_id = painting.a_id
-> ORDER BY artist.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+
In this output, the first a_id column comes from the artist table and the second one
comes from the painting table.
Now substitute LEFT for INNER to see the result you get from an outer join:
mysql> SELECT * FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> ORDER BY artist.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+
Compared to the inner join, the outer join produces an additional row for every ar
tist row that has no painting table match, with all painting columns set to NULL .
Next, to restrict the output only to the unnmatched artist rows, add a WHERE clause
that looks for NULL values in any painting column that cannot otherwise contain