Database Reference
In-Depth Information
table.
Recipe 14.3
discusses how to identify (and optionally delete) these unattached
rows.
14.3. Identifying and Removing Mismatched or
Unattached Rows
Problem
You have two datasets that are related, but possibly imperfectly so. You want to determine
whether there are records in either dataset that are “unattached” (not matched by any
record in the other dataset), and perhaps remove them if so.
Solution
To identify unmatched values in each table, use a
LEFT
JOIN
or a
NOT
IN
subquery. To
remove them, use
DELETE
with a
NOT
IN
subquery.
Discussion
Inner joins are useful for identifying matches, and outer joins are useful for identifying
mismatches. This property of outer joins is valuable when you have related datasets for
which the relationship might be imperfect. Mismatches might be found, for example,
when you must verify the integrity of two datafiles received from an external source.
When you have related tables with unmatched rows, you can analyze and modify them
using SQL statements. Specifically, restoring their relationship is a matter of identifying
the unattached rows and then deleting them:
• To identify unattached rows, use a
LEFT
JOIN
, because this is a “find unmatched
rows” problem; alternatively, use a
NOT
IN
subquery (see
Recipe 14.2
).
• To delete rows that are unmatched, use
DELETE
with a
NOT
IN
subquery.
It's useful to know about unmatched data because you can alert whoever gave you the
data. The data collection method might have a flaw that must be corrected. For example,
with sales data, a missing region might mean that some regional manager didn't report
in and the omission was overlooked.
The following example shows how to identify and remove mismatched rows using two
datasets that describe sales regions and volume of sales per region. One dataset contains
the ID and location of each region:
mysql>
SELECT * FROM sales_region ORDER BY region_id;
+-----------+------------------------+
| region_id | name |
+-----------+------------------------+
