Database Reference
In-Depth Information
+----------+---------------------+-------------+---------------+
| name | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 2 | 115 | 57.5000 |
+----------+---------------------+-------------+---------------+
The preceding summary statements produce output only for those artists in the
ar
tist
table for whom you actually have acquired paintings. (For example, Monet is listed
in the
artist
table but is not present in the summary because you have none of his
paintings yet.) To summarize all artists, including those for whom you have no paintings,
you must use a different kind of join—specifically, an outer join:
• Joins written with
INNER
JOIN
are inner joins. They produce a result only for values
in one table that match values in another table.
• An outer join can produce those matches as well, but also can show you which
values in one table are missing from the other.
Recipe 14.2
introduces outer joins.
The
tbl_name.col_name
notation that qualifies a column name with a table name is
always permitted in a join but can be shortened to just
col_name
if the name appears in
only one of the joined tables. In that case, MySQL can determine without ambiguity
which table the column comes from, and no table name qualifier is necessary. We can't
do that for the following join. Both tables have an
a_id
column, so the
ON
clause column
references are ambiguous:
mysql>
SELECT * FROM artist INNER JOIN painting ON a_id = a_id;
ERROR 1052 (23000): Column 'a_id' in on clause is ambiguous
By contrast, the following query is unambiguous. Each instance of
a_id
is qualified with
the appropriate table name, only
artist
has a
name
column, and only
painting
has
title
and
state
columns:
mysql>
SELECT name, title, state FROM artist INNER JOIN painting
->
ON artist.a_id = painting.a_id
->
ORDER BY name;
+----------+-------------------+-------+
| name | title | state |
+----------+-------------------+-------+
| Da Vinci | The Last Supper | IN |
| Da Vinci | Mona Lisa | MI |
| Renoir | Les Deux Soeurs | NE |
| Van Gogh | Starry Night | KY |
| Van Gogh | The Potato Eaters | KY |
+----------+-------------------+-------+
