SQL Joins Explained
BeginnerA join combines rows from two or more tables based on a related column. Because relational databases split data across tables to avoid duplication, joins are how you stitch it back together — an order with its customer, a user with their posts. The join type (INNER, LEFT, RIGHT, FULL) decides which rows survive when there is no match. Understanding joins is fundamental to writing correct SQL and to reading query plans.
Think of a join as matching two guest lists
You have a list of invitees and a list of people who RSVP-ed. An INNER join is everyone on both lists (invited AND replied). A LEFT join keeps every invitee, marking those who never replied as blank. A RIGHT join keeps every reply, even from crashers not on the invite list. A FULL join keeps everyone from both lists. The join type is simply your rule for what to do with people who appear on only one list.
Step by Step
Key Concepts
INNER vs OUTER JOIN
INNER returns only matching rows. OUTER joins (LEFT, RIGHT, FULL) also keep unmatched rows from one or both sides, filling missing columns with NULL.
Join Condition (ON)
The predicate that defines how rows relate, usually a foreign key matching a primary key (ON orders.customer_id = customers.id). A missing or wrong condition produces incorrect or exploded results.
Cartesian Product
Every combination of rows from both tables — what CROSS JOIN produces, and what an accidental missing join condition creates, causing a huge, wrong result set.
Join Algorithms
The database executes a join as a nested-loop, hash, or merge join depending on data size and indexes. EXPLAIN shows which one is used and whether an index is helping.
Key Facts
- A LEFT JOIN plus WHERE right.column IS NULL is the standard trick to find rows in the left table with no match on the right.
- Forgetting the ON condition (or an incorrect one) can produce a Cartesian product — millions of rows and a very slow query.
- Indexing the columns used in join conditions is usually the single biggest performance win for join-heavy queries.
Real-World Applications
Fetching related data
SELECT o.*, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id returns each order with its customer name in one query, instead of a second lookup per order.
Finding gaps
A LEFT JOIN from users to subscriptions, filtered by subscriptions.id IS NULL, lists users who have never subscribed — a common reporting pattern.
Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, plus matching data from the right table or NULLs where there is no match. Use INNER when you only want matched pairs, and LEFT when you must keep all left-table rows regardless of matches.
What is a self join?
A self join joins a table to itself, using table aliases to treat it as two copies. It is used for hierarchical or comparative data within one table — for example, joining employees to employees on manager_id = id to list each employee alongside their manager.
Why is my join returning too many rows?
Usually the join condition is missing or not specific enough, creating a Cartesian product (every row paired with every other), or you are joining on a non-unique column so each left row matches many right rows. Check the ON clause and the uniqueness of the join columns.
How do I make joins faster?
Index the columns used in the join condition (typically the foreign key), select only the columns you need, and filter early. Use EXPLAIN to confirm the database uses those indexes and to see which join algorithm it chose.