Database Reference
In-Depth Information
Another way to identify values present in one table but missing from another is to use
a
NOT
IN
subquery. The following example finds artists not represented in the
paint
ing
table; compare it to the earlier
LEFT
JOIN
that answers the same question:
mysql>
SELECT * FROM artist
->
WHERE a_id NOT IN (SELECT a_id FROM painting);
+------+-------+
| a_id | name |
+------+-------+
| 2 | Monet |
+------+-------+
Other Ways to Write LEFT JOIN and RIGHT JOIN Queries
As with
INNER
JOIN
, if the names of the columns to be matched in an outer join are the
same in both tables and you compare them with the
=
operator, you can use a
USING
clause rather than
ON
. For example, the following two statements are equivalent:
SELECT
*
FROM
t1
LEFT
JOIN
t2
ON
t1
.
n
=
t2
.
n
;
SELECT
*
FROM
t1
LEFT
JOIN
t2
USING
(
n
);
As are these:
SELECT
*
FROM
t1
RIGHT
JOIN
t2
ON
t1
.
n
=
t2
.
n
;
SELECT
*
FROM
t1
RIGHT
JOIN
t2
USING
(
n
);
In the special case that you want to base the comparison on every column that appears
in both tables, you can use
NATURAL
LEFT
JOIN
or
NATURAL
RIGHT
JOIN
and omit the
ON
or
USING
clause:
SELECT
*
FROM
t1
NATURAL
LEFT
JOIN
t2
;
SELECT
*
FROM
t1
NATURAL
RIGHT
JOIN
t2
;
See Also
As shown in this section,
LEFT
JOIN
is useful for finding values with no match in another
table or for showing whether each value is matched.
LEFT
JOIN
may also be used to
produce a summary that includes all items in a list, even those for which there's nothing
to summarize. This is very common for relationships between a master table and a detail
table. For example, a
LEFT
JOIN
can produce “total sales per customer” reports that list
all customers, even those who bought nothing during the summary period. (For infor‐
mation about master-detail lists, see
Recipe 14.5
.)
LEFT
JOIN
is also useful for consistency checking when you receive two datafiles that
are supposed to be related, and you want to determine whether they really are. (That is,
you want to check the integrity of their relationship.) Import each file into a MySQL
table, and then run a couple
LEFT
JOIN
statements to determine whether there are un‐
attached rows in one table or the other—that is, rows that have no match in the other
