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