Joins and Subqueries
Joins and Subqueries
This domain tests combining rows from multiple tables and using queries inside queries. Know inner, outer, cross, self, and natural joins, plus single-row, multi-row, correlated, and scalar subqueries.
Core rules
- Inner joins return matching rows. LEFT/RIGHT/FULL OUTER JOIN preserves unmatched rows from one or both sides, filling the other side with NULL.
- Join conditions belong in ON; filters on the preserved table in WHERE can turn an outer join into an inner join.
- A cross join produces a Cartesian product; a missing join condition is a common cause.
- USING requires identically named columns; ON allows arbitrary conditions.
- Single-row subqueries use =, <, >; multi-row subqueries use IN, ANY, ALL, EXISTS.
- A correlated subquery references the outer query and is evaluated per outer row. A scalar subquery returns one column and one row.
Worked example
SELECT d.department_id, COUNT(e.employee_id) AS employees FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id GROUP BY d.department_id; This keeps departments with no employees. COUNT(e.employee_id) returns 0 for those rows because the padded employee column is NULL; COUNT(*) would return 1.
Traps
- Forgetting the join condition and producing a Cartesian product.
- Using NOT IN when the subquery returns NULL; the whole predicate can return no rows.
- Filtering the preserved side in WHERE after a LEFT JOIN.
- Using = with a subquery that returns multiple rows.
- Ambiguous column names without a table alias.