Joins and combining data sets

Why this matters

Useful questions almost always span more than one table. Revenue by region needs orders, products for the price, and customers for the region. Combining them is a join, and joins are where analyses most often go wrong without any error message: a join can add rows, drop rows, or do both at once, and the result still looks like a perfectly reasonable table.

The exam tests joins directly: which join returns which rows, what happens to unmatched records, and why a total changed after tables were combined. This lesson covers the join types, the fan-out that duplicates rows, appending with UNION, the row-count check that catches join errors, and joining sources whose keys do not match cleanly.

The lesson

Inner, left, right and full outer joins

A join matches rows from two tables using a condition, usually that a key in one equals a key in the other:

SELECT o.order_id, o.order_date, c.region
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id;

The join type decides what happens to rows that find no match.

• An inner join (JOIN or INNER JOIN) returns only rows that match in both tables. An order whose customer is missing from the customer table disappears from the result.
• A left join (LEFT JOIN or LEFT OUTER JOIN) returns every row from the left table, with matching data from the right where it exists and NULLs where it does not. It is the usual choice when the left table is the one you are reporting on and must not lose rows.
• A right join is the mirror image, keeping every row from the right table. It is rarely needed, because the same result comes from swapping the tables in a left join.
• A full outer join keeps every row from both tables, matched where possible and filled with NULLs where not. It is useful for comparing two lists to see what each has that the other lacks.

A cross join pairs every row of one table with every row of the other, producing rows equal to the two counts multiplied together. It is occasionally intended, and much more often the result of a forgotten join condition.

Left joins also answer "what is missing" questions. A left join from products to orders, keeping rows where the order side is NULL, lists products that have never been ordered. In the practice data that returns none, because every product has sold at least once, which is itself worth checking rather than assuming.

Join keys, and the fan-out a many-to-many join causes

A join matches every row on one side with every matching row on the other. When the key is unique on at least one side, that is exactly what you want: each order matches one customer, and the result has one row per order.

When a key repeats on both sides, rows multiply. This is fan-out. If a customer appears twice in the customer table, every one of their orders matches twice and appears twice in the result. Any sum over the result, such as total revenue, is inflated by exactly those duplicated orders, and nothing in the output says so.

Fan-out has three usual causes: duplicate records in a table that should have unique keys, joining on a column that is not actually a key (such as a name or a region), and joining two tables that are both at a detailed level, such as orders and payments, where one order can have several payments. Before joining, check whether the key is unique where you expect it to be, which a single query can confirm:

SELECT customer_id, COUNT(*) FROM customers
GROUP BY customer_id HAVING COUNT(*) > 1;

No rows back means the key is unique.

UNION against UNION ALL, and appending data sets

A join combines tables side by side, adding columns. Appending combines them end to end, adding rows, and in SQL it is done with UNION.

SELECT customer_id, email FROM customers_2024
UNION ALL
SELECT customer_id, email FROM customers_2025;

Both queries must return the same number of columns with compatible types, and columns are matched by position, not by name, so the order of columns must agree in each part.

The two forms differ in one important way. UNION ALL keeps every row, including rows that appear in both parts. UNION removes duplicate rows, which means extra work for the database and, more importantly, a changed result: two genuinely separate records that happen to have identical values collapse into one. Use UNION ALL by default when appending periods or sources, and UNION only when removing exact duplicates is the intention.

Checking row counts before and after every join

The single most useful habit with joins is to count rows before and after.

Before joining, know how many rows the main table has and what the result should have. Joining orders to customers should return exactly one row per order: in the practice data, 2,000 rows in, 2,000 rows out. More rows means fan-out; fewer means an inner join dropped unmatched records.

Counts catch problems that a glance at the result never will. For example, joining the 2,000 practice orders to the raw customer file on email address returns 1,953 rows. That looks nearly right, and it is wrong in two directions at once: 126 orders vanished because their customers' emails are blank and match nothing, and 79 orders were added because 14 customers appear twice in the raw file under slightly different details. A difference of 47 rows hides two errors totalling 205.

The fuller check therefore compares more than the total: count unmatched rows explicitly with a left join, and count duplicated keys on each side before joining.

Joining across sources whose keys do not quite match

Joins are exact: 'C0001' does not equal 'C0001 ' with a trailing space, and 'North' does not equal 'north'. Data from different sources often identifies the same thing in slightly different ways, and an exact join silently drops the near-misses.

Common mismatches and their fixes:

• Whitespace and case: trim spaces and convert both sides to the same case before matching.
• Formatting: leading zeros, hyphens in codes, or different date formats must be standardised first.
• Different identifiers: one system uses an account number, another an email; a mapping table that links the two is often needed.
• No shared key at all: matching on a combination of fields such as name, postcode and date of birth is possible, but it is probabilistic, and every such match should be reviewed rather than trusted.

The safest approach is to clean keys in a separate, documented step before joining, then check how many rows failed to match and look at a sample of them. A join that silently loses 5% of its rows can shift a result more than any modelling choice made later.