Joins, grouping, and subqueries
Real questions span several tables and need summaries. Joins combine tables, GROUP BY summarises them, and subqueries nest one query inside another.
Inner joins
An inner join returns rows where the join condition matches in both tables: SELECT s.name, e.course FROM students s JOIN enrolments e ON s.id = e.student_id. Students with no enrolments do not appear. Table aliases such as s and e keep queries short and are essential when the same table appears twice.
Outer joins
A LEFT OUTER JOIN keeps every row of the left table, filling the right table's columns with NULL where there is no match: students with no enrolments appear with a null course. RIGHT JOIN mirrors this, and FULL OUTER JOIN keeps unmatched rows from both sides. Finding rows with no match, such as customers who never ordered, uses LEFT JOIN plus WHERE right_table.id IS NULL.
Other joins
A CROSS JOIN gives the Cartesian product, every pairing, which is useful for generating combinations such as all sizes times all colours. A self-join joins a table to itself, for example employees e JOIN employees m ON e.manager_id = m.id to list each employee with their manager's name.
GROUP BY and HAVING
GROUP BY collects rows with equal values into groups, and aggregates are then computed per group: SELECT department, AVG(salary) FROM employees GROUP BY department. Every selected column must either be grouped or aggregated. HAVING filters groups after aggregation, such as HAVING COUNT(*) >= 5, whereas WHERE filters individual rows before grouping.
Subqueries
A subquery is a query inside another. Scalar subqueries return one value: WHERE salary > (SELECT AVG(salary) FROM employees). IN subqueries test membership: WHERE id IN (SELECT student_id FROM enrolments WHERE course = 'DB101'). EXISTS tests whether a correlated subquery returns any rows, and NOT EXISTS finds rows lacking related data.
Common table expressions
A common table expression (CTE) names a temporary result with WITH: WITH big_orders AS (SELECT * FROM orders WHERE amount > 1000) SELECT customer, COUNT(*) FROM big_orders GROUP BY customer. CTEs make complex queries readable by breaking them into named steps. Recursive CTEs can traverse hierarchies, such as an organisation chart or a parts list, to any depth.
Window functions
Window functions compute values across related rows without collapsing them into groups. SUM(amount) OVER (PARTITION BY customer ORDER BY placed_on) gives each customer's running total; RANK() OVER (ORDER BY score DESC) ranks rows; LAG and LEAD access previous and next rows, useful for month-over-month changes. They are among SQL's most powerful analytical features.
Worked example
Departments with average salary above 60,000 and at least 3 staff: SELECT d.name, AVG(e.salary) AS avg_salary, COUNT(*) AS staff FROM departments d JOIN employees e ON e.dept_id = d.id GROUP BY d.name HAVING COUNT(*) >= 3 AND AVG(e.salary) > 60000 ORDER BY avg_salary DESC. The join attaches department names, grouping summarises each department, and HAVING keeps qualifying groups.
Key takeaways
Inner joins return matching rows; left, right, and full outer joins keep unmatched rows with NULLs, and LEFT JOIN with IS NULL finds missing relationships. Cross joins form all pairings; self-joins relate a table to itself. GROUP BY forms groups for aggregates; selected columns must be grouped or aggregated; HAVING filters groups and WHERE filters rows. Subqueries can be scalar, IN, or EXISTS, possibly correlated. CTEs name intermediate steps, and recursive CTEs walk hierarchies. Window functions compute running totals, ranks, and row-to-row comparisons without collapsing rows.
Flashcard
Rows matching in both tables
Check your understanding
Which query lists customers who have never placed an order?