Lesson 12 of 15 · 18 min
Diagnosing joins: counts, keys and totals
By the end of this lesson you can run a short diagnostic routine on any join (row counts before and after, key uniqueness, unmatched rows, total reconciliation) and explain from the numbers whether rows were lost or multiplied.
Every join can go wrong in exactly two directions:
- Rows are lost: some rows found no match and an inner join dropped them (cancelled orders with no shipment, customers with no city to join on).
- Rows are multiplied: the side you assumed had one match per row actually has several (two parcels per order, two warehouses in one city, a customer registered twice).
Both can happen in the same join, and they can partly cancel each other out, which is why “the total looks about right” proves nothing. The routine below takes a few minutes and tells you exactly what a join did.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons