Database Reference
In-Depth Information
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+
The column names in the
WHERE
clause include table qualifiers to make it clear which
a_id
values to compare. The result indicates who painted each painting, and, conversely,
which paintings by each artist are in your collection.
Joins and Indexes
A join can easily cause MySQL to process large numbers of row combinations, so it's a
good idea to index the comparison columns. Otherwise, performance drops off quickly
as table sizes increase. For the
artist
and
painting
tables, joins are made by comparing
the
a_id
columns. If you look back at the
CREATE
TABLE
statements for those tables, you
see that
a_id
is indexed in each table.
Another way to write the same join indicates the matching conditions with an
ON
clause:
SELECT
*
FROM
artist
INNER
JOIN
painting
ON
artist
.
a_id
=
painting
.
a_id
ORDER
BY
artist
.
a_id
;
In the special case of equality comparisons between columns with the same name in
both tables, you can use an
INNER
JOIN
with a
USING
clause instead. This requires no
table qualifiers and names each joined column only once:
SELECT
*
FROM
artist
INNER
JOIN
painting
USING
(
a_id
)
ORDER
BY
a_id
;
For
SELECT
*
queries, the
USING
form produces a result that differs from the
ON
form: it
returns only one instance of each join column, so
a_id
appears once, not twice.
Any of
ON
,
USING
, or
WHERE
can include comparisons, so how do you know which join
conditions to put in each clause? As a rule of thumb, it's conventional to use
ON
or
USING
to specify how to join the tables, and the
WHERE
clause to restrict which of the joined
rows to select. For example, to join tables based on the
a_id
column, but select only
rows for paintings obtained in Kentucky, use an
ON
(or
USING
) clause to match the rows
in the two tables, and a
WHERE
clause to test the
state
column:
mysql>
SELECT * FROM artist INNER JOIN painting
->
ON artist.a_id = painting.a_id
->
WHERE painting.state = 'KY';
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
