Database Reference
In-Depth Information
corresponding qualifiers. If the default database is
db1
, you can omit the
db1
qualifiers.
Conversely, if the default database is
db2
, no
db2
qualifiers are necessary.
14.2. Finding Mismatches Between Tables
Problem
You want to find rows in one table that have no match in another. Or you want to produce
a list on the basis of a join between tables, and you want the list to include an entry for
every row in the first table, including those for which no match occurs in the second
table.
Solution
Use an outer join (a
LEFT
JOIN
or a
RIGHT
JOIN
) or a
NOT
IN
subquery.
Discussion
Recipe 14.1
focuses on inner joins, which find matches between two tables. However,
the answers to some questions require determining which rows do
not
have a match
(or, stated another way, which rows have values missing from the other table). For ex‐
ample, you might want to know artists in the
artist
table for whom you have no paint‐
ings yet. Similar questions occur in other contexts:
• You have a list of potential customers, and another list of people who have placed
orders. To focus sales efforts on people who are not yet actual customers, produce
the set of people who are in the first list but not the second.
• You have one list of baseball players, and another list of players who have hit home
runs. To determine which players in the first list have
not
hit a home run, produce
the set of players who are in the first list but not the second.
These types of questions require use of an outer join. Like inner joins, an outer join
finds matches between tables. But unlike an inner join, an outer join also determines
which rows in one table have no match in another. Two types of outer join are
LEFT
JOIN
and
RIGHT
JOIN
.
To see how outer joins are useful, consider the problem of determining which artists in
the
artist
table are missing from the
painting
table. At present, the tables are small,
so it's easy to examine them visually and see that you have no paintings by Monet (there
are no
painting
rows with an
a_id
value of 2):
