P-6.3 Joins, Grouping and Aggregates

What this is and why it exists

Joins are where SQL becomes genuinely powerful. They are also where most people's understanding stops being solid, and stays that way for years.

The model that works is simple to state. A join produces the combinations that match. An outer join additionally keeps unmatched rows from one side, with the other side coming back empty.

Grouping then collapses rows into summaries. The two filtering steps around it are distinct, because one runs on rows and the other on finished groups.

The vocabulary

• Inner join — keeps only rows matching on both sides.
• Outer join — keeps unmatched rows from one side too.
• Join condition — the rule saying which rows correspond.
• Aggregate — a function collapsing many rows into one value.
• Group — the set of rows an aggregate is computed over.
• Row filter — a condition deciding which rows enter a group.
• Group filter — a condition deciding which finished groups survive.

The mental model

An inner join matches rows from two tables on a shared key and returns the combinations that exist in both. Anything unmatched disappears. That is correct behaviour, and it is exactly the trap in the next paragraph.

An outer join keeps every row of one table whether or not it matched. This is how you find customers with no orders, or students with no submissions. The unmatched columns come back empty, and that emptiness is the answer, not a fault. People new to this often treat those blank columns as a sign something went wrong; they are the finding.

Joining more than two tables is where it goes wrong quietly. Each join multiplies the rows that match, so a missing condition produces a result far larger than either input. The habit that catches this immediately is checking the row count after every join you add. If it jumped by a factor of forty, you have found the problem while it is still one line old.

Grouping collapses rows into one per group, with counting, summing or averaging within it. That answers most reporting questions. There is one rule to internalise: every selected column must either be grouped by or aggregated. A column that is neither has no single value for the group, and the question you asked has no answer.

Finally, the two filters, which is the part that produces wrong numbers rather than errors. One condition decides which rows enter a group. The other decides which finished groups survive. "Orders over a hundred rupees, grouped by customer" and "customers whose total is over a hundred rupees" are different questions with different answers. Putting a condition in the wrong place gives you a number rather than a complaint, and the number is wrong.

What you should now be able to explain or do

Combine tables with an inner join and say what disappears. Use an outer join to find things with no matches, and read the empty columns as the answer. Join three tables and check the row count after each. Group and aggregate, and say why every column must be grouped or aggregated. Say which filter runs on rows and which on groups, and give a question for each.

Check yourself