Ch 3 · Combining & Summarising · SQL
Topic 3 of 8 in SQL — Query a Real Database — 3 lessons.
JOIN
Data is split across tables. A JOIN stitches them together on a matching column — here, each employee with their department name.
GROUP BY & Aggregates
Summarise with COUNT, SUM, AVG and break it down per group with GROUP BY. The example joins employees to their departments, then counts the people in each department and works out their average salary. Each department becomes one row in the result, sorted by average pay. This is how you turn a long list of rows into a short, useful summary.
LEFT JOIN: Keeping Rows With No Match
A plain JOIN only returns rows that match in both tables. Our company has four departments, but Operations has no employees yet, so a plain JOIN between employees and departments quietly leaves Operations out. A LEFT JOIN keeps every row from the table on the left, which is the one named after FROM, even when nothing matches on the right. Where there is no match, the columns from the right-hand table are filled with NULL. In the example, departments is the left table, so all four departments appear. Look carefully at the count: COUNT(e.id) only counts rows where an employee id exists, so Operations correctly shows 0. If you used COUNT(*) instead, it would count the single row of NULLs and wrongly report 1.