SQL: JOIN fundamentals
This course explains how SQL JOINs combine rows from separate tables through foreign keys. It starts with normalization and the foreign key contract. It then covers INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN, including NULL handling and the anti-join pattern. Later lessons cover multi-column conditions, chained joins, self-joins and non-equi joins. It also shows how PostgreSQL picks nested loop, hash or merge joins, how to read EXPLAIN ANALYZE, and what SQL ActiveRecord methods such as joins, includes and preload generate.
What you'll learn
- ✓How normalization and foreign keys connect tables
- ✓How INNER, LEFT, RIGHT and FULL OUTER JOIN behave
- ✓The anti-join pattern and NULL pitfalls with ON, WHERE and COUNT
- ✓Chained joins, composite keys, self-joins and non-equi joins
- ✓Reading EXPLAIN ANALYZE and indexing foreign key columns
- ✓Choosing between joins, left_joins, includes, eager_load and preload in ActiveRecord
What is a JOIN?
You run a small online bookstore. Every day, customers place orders. Your accounting software stores two separate lists: one for customers (name, email, phone) and one for orders (order number, total, date). At month-end, you need a report showing each order alongside the customer who placed it.
Read the full lesson free →Curriculum
7 lessonsWhat is a JOIN?
Explains normalization, foreign keys, the three join algorithms, explicit join syntax and cartesian products.
INNER JOIN: matching rows
Shows how INNER JOIN keeps only matching rows, multiplies rows on multiple matches, and uses aliases and DISTINCT.
LEFT JOIN: keeping all left rows
Covers LEFT JOIN, the anti-join pattern, and NULL pitfalls with WHERE filters, COUNT and arithmetic using COALESCE.
RIGHT JOIN & FULL OUTER JOIN
Explains RIGHT JOIN as a flipped LEFT JOIN and FULL OUTER JOIN for data reconciliation and audits.
JOIN conditions & multiple JOINs
Covers the ON clause in depth, composite keys, chained joins, join order, self-joins and non-equi joins.
JOIN performance & query plans
Teaches reading EXPLAIN ANALYZE, comparing join algorithms, indexing foreign keys and spotting the N+1 query problem.
JOINs in Rails: ActiveRecord associations
Compares joins, left_joins, includes, eager_load and preload, showing the SQL each generates and when to use it.