Joins and Subqueries
Domain 6 tests combining rows from multiple tables and nesting queries to answer multi-step questions. You must know join types, join conditions, subquery cardinality, and correlated references.
Core rules
- An inner join returns only matching rows. LEFT OUTER JOIN keeps unmatched rows from the left table and fills right-side columns with NULL; RIGHT OUTER JOIN does the reverse; FULL OUTER JOIN keeps unmatched rows from both sides.
- Join conditions belong in ON. USING requires identically named columns and merges those columns for unqualified references. NATURAL JOIN joins all same-named columns, which can be risky.
- A cross join produces a Cartesian product. A missing join condition usually causes one by accident.
- A single-row subquery may use =, <, >. A multi-row subquery must use IN, ANY, ALL, or EXISTS. A scalar subquery returns one column and one row.
- Correlated subqueries reference a column from the outer query and are evaluated per outer row. EXISTS tests whether the subquery returns any row.
Worked example
SELECT d.department_name, e.last_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > (SELECT AVG(salary) FROM employees);
The join matches employees to departments by department_id. The subquery returns one scalar average salary for the whole company, so the comparison is valid. Only employees earning above that average survive the WHERE clause.
Traps
A multi-row subquery with = raises an error. Filtering an outer-joined table in WHERE can turn the outer join into an inner join; put such conditions in ON when appropriate. Using USING means you should not qualify the shared column with a table alias. Correlated subqueries can be slow and must reference the correct alias.
Key points
- Outer joins preserve unmatched rows.
- Put join conditions in ON, not WHERE, for outer joins.
- Multi-row subqueries need IN, ANY, ALL, or EXISTS.
- Scalar subqueries return one row and one column.
- Correlated subqueries reference the outer query.