Using Joins and Subqueries - MySQL - page 485

NULL . This filters out the rows that the inner join produces, leaving those produced only
by the outer join:
mysql> SELECT * FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;
+------+-------+------+------+-------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+-------+------+------+-------+-------+-------+
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
+------+-------+------+------+-------+-------+-------+
Finally, to show only the artist table values that are missing from the painting table,
write the output column list to name only columns from the artist table. The result is
that the LEFT JOIN lists those left-table rows containing a_id values not present in the
right table:
mysql> SELECT artist.* FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;
+------+-------+
| a_id | name |
+------+-------+
| 2 | Monet |
+------+-------+
A similar kind of operation reports each left-table value along with an indicator as to
whether it's present in the right table. To do this, perform a LEFT JOIN that counts the
number of times each left-table value occurs in the right table. A count of zero indicates
that the value is not present. The following statement lists each artist from the artist
table and shows whether you have any paintings by the artist:
mysql> SELECT artist.name,
-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection?'
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;
+----------+----------------+
| name | in collection? |
+----------+----------------+
| Da Vinci | yes |
| Monet | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+----------------+
A RIGHT JOIN is an outer join that is like LEFT JOIN but reverses the roles of the left and
right tables. Semantically, RIGHT JOIN forces the matching process to produce a row
from each table in the right table, even in the absence of a corresponding row in the left
table. Syntactically, tbl1 LEFT JOIN tbl2 is equivalent to tbl2 RIGHT JOIN tbl1 . There‐
fore, references to LEFT JOIN in this topic apply to RIGHT JOIN as well if you reverse the
roles of the tables.