Database Reference
In-Depth Information
| 5 |
+--------------------------+
In this case, an ID appears more than once in the list if there are multiple volume rows
for a missing region. To see each unmatched ID only once, use
SELECT
DISTINCT
:
mysql>
SELECT DISTINCT sales_volume.region_id AS 'unmatched volume row IDs'
->
FROM sales_volume LEFT JOIN sales_region
->
ON sales_volume.region_id = sales_region.region_id
->
WHERE sales_region.region_id IS NULL
+--------------------------+
| unmatched volume row IDs |
+--------------------------+
| 5 |
+--------------------------+
You can also identify mismatches using
NOT
IN
subqueries:
mysql>
SELECT region_id AS 'unmatched region row IDs'
->
FROM sales_region
->
WHERE region_id NOT IN (SELECT region_id FROM sales_volume);
+--------------------------+
| unmatched region row IDs |
+--------------------------+
| 2 |
| 4 |
+--------------------------+
mysql>
SELECT region_id AS 'unmatched volume row IDs'
->
FROM sales_volume
->
WHERE region_id NOT IN (SELECT region_id FROM sales_region);
+--------------------------+
| unmatched volume row IDs |
+--------------------------+
| 5 |
| 5 |
+--------------------------+
To get rid of unmatched rows, use a
NOT
IN
subquery in a
DELETE
statement. To remove
sales_region
rows that match no
sales_volume
rows, do this:
DELETE
FROM
sales_region
WHERE
region_id
NOT
IN
(
SELECT
region_id
FROM
sales_volume
);
To remove mismatched
sales_volume
rows that match no
sales_region
rows, the
statement is similar but with the table roles reversed:
DELETE
FROM
sales_volume
WHERE
region_id
NOT
IN
(
SELECT
region_id
FROM
sales_region
);
