Database Reference
In-Depth Information
| 1 | London, United Kingdom |
| 2 | Madrid, Spain |
| 3 | Berlin, Germany |
| 4 | Athens, Greece |
+-----------+------------------------+
The other dataset contains sales volume figures. Each row contains the amount of sales
for a given quarter of a year and indicates the sales region to which the row applies:
mysql>
SELECT * FROM sales_volume ORDER BY region_id, year, quarter;
+-----------+------+---------+--------+
| region_id | year | quarter | volume |
+-----------+------+---------+--------+
| 1 | 2014 | 1 | 100400 |
| 1 | 2014 | 2 | 120000 |
| 3 | 2014 | 1 | 280000 |
| 3 | 2014 | 2 | 250000 |
| 5 | 2014 | 1 | 18000 |
| 5 | 2014 | 2 | 32000 |
+-----------+------+---------+--------+
A little visual inspection reveals that neither table is fully matched by the other. Sales
regions 2 and 4 are not represented in the sales volume table, and the sales volume table
contains rows for region 5, which is not in the sales region table. But we don't want to
check the tables by inspection. We want to find unmatched rows by using SQL state‐
ments that do the work.
Mismatch identification is a matter of using outer joins. For example, to find sales re‐
gions for which there are no sales volume rows, use the following
LEFT
JOIN
:
mysql>
SELECT sales_region.region_id AS 'unmatched region row IDs'
->
FROM sales_region LEFT JOIN sales_volume
->
ON sales_region.region_id = sales_volume.region_id
->
WHERE sales_volume.region_id IS NULL;
+--------------------------+
| unmatched region row IDs |
+--------------------------+
| 2 |
| 4 |
+--------------------------+
Conversely, to find sales volume rows that are not associated with any known region,
reverse the roles of the two tables:
mysql>
SELECT 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 |
