SQL Joins, Subqueries & Advanced Queries
Real data is spread across multiple tables. Joins combine rows from two or more tables based on a related column. Once you understand joins, you can answer almost any question your data holds.
Why Joins?#
With separate users and orders tables, you can't answer "what did Alice order?" with a single table. A join combines them:
INNER JOIN#
Returns only rows that have a match in both tables. The most common join.
LEFT JOIN (LEFT OUTER JOIN)#
Returns all rows from the left table, plus matching rows from the right. Where there's no match, right-side columns are NULL.
Use LEFT JOIN when you want to keep all rows from the left table regardless of matches.
RIGHT JOIN#
Same as LEFT JOIN but keeps all rows from the right table. Rarely used — you can always rewrite it as a LEFT JOIN by swapping the table order.
FULL OUTER JOIN#
Returns all rows from both tables. NULLs fill in where there's no match on either side.
CROSS JOIN#
Every row from the left table paired with every row from the right — a Cartesian product. Rarely intentional; usually a mistake.
Self Join#
A table joined to itself. Useful for hierarchical or comparative data.
Multiple Joins#
Subqueries#
A subquery is a SELECT inside another SELECT. Useful when the condition depends on an aggregate.
CTEs (Common Table Expressions)#
CTEs make complex queries readable by naming intermediate results.
Window Functions#
Window functions compute values across a set of rows related to the current row, without collapsing them into groups.
Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM(), AVG().
Join Visual Summary#
Key Takeaways#
INNER JOIN— only matching rows from both tablesLEFT JOIN— all rows from the left table, NULLs for unmatched right side- Join on foreign key = primary key:
ON child.fk = parent.pk - Subqueries in
WHERE/HAVINGallow filtering on aggregated or derived values - CTEs make multi-step queries readable — think of them as named temporary views
- Window functions compute per-row analytics without collapsing the result set