A join is how SQL stitches related rows from different tables back into one result set. Master the six join shapes and the two clauses that control them (ON and WHERE) and roughly 80% of real-world SQL stops being mysterious. This is the complete, runnable reference.
Joins are the feature that makes a relational database relational. Without them you would be stuck with either one gigantic table full of repeated data or a scatter of disconnected tables you can never query together. Understanding joins deeply pays off on every non-trivial query you will ever write, from a simple customer-and-orders report to a dashboard spanning a dozen tables. Relational databases deliberately split data across many narrow tables so each fact is stored once. That is great for correctness but useless for reading: nobody wants a customer name in one table and their orders in another with no way to see them together. Joins put the pieces back together at query time. If you want the fast, picture-first tour, read the visual version first, then come back here for the deep, complete guide with every join type, the classic traps, and how joins behave under load.
What this guide covers
- What a join is: keys and the join key
- INNER JOIN
- LEFT JOIN and RIGHT JOIN
- FULL OUTER JOIN (and the MySQL workaround)
- CROSS JOIN (the Cartesian product)
- SELF JOIN (hierarchies)
- Joining more than two tables
- ON vs WHERE: the LEFT JOIN gotcha
- USING and NATURAL JOIN
- Non-equi joins (ranges and BETWEEN)
- Semi-joins and anti-joins
- Joins vs subqueries; joins with GROUP BY
- Performance: indexes, join order, fan-out
- Every join type compared
1. What a join is: keys and the join key
A join takes two tables, compares a column (or expression) from one against a column from the other, and produces a combined row wherever the comparison is true. The column you compare on is the join key. In a well-designed schema the join key is a primary key on one side and a foreign key on the other.
- A primary key uniquely identifies each row of a table (for example
customers.id). It is never NULL and never duplicated. - A foreign key is a column in another table that points at a primary key (for example
orders.customer_id). It is how the database records "this order belongs to that customer".
Joining on orders.customer_id = customers.id simply says: line each order up with the customer whose id it stores. Every example below uses one small three-table schema. Learn it once and the rest of the guide reads cleanly. For the wider SELECT toolkit around joins, see SELECT, WHERE and GROUP BY.
It helps to picture what a join does mechanically. Conceptually the database looks at each row on the left, scans the right table for rows whose join key satisfies the ON condition, and emits one combined row for every pair that matches. That "one row per matching pair" rule is the mental model to keep: a join never magically collapses many child rows into one, and it never invents rows that do not satisfy the condition. Everything a join does, and every surprise it produces, follows from that single rule plus what you decide to do with rows that find no partner. The join types differ only in that last decision.
One more piece of vocabulary. The table written first (after FROM) is the left table; the table written after the JOIN keyword is the right table. "Left" and "right" are purely about the order you type them, not about anything in the data. That ordering is what gives LEFT JOIN and RIGHT JOIN their names, and it is why swapping the two tables turns one into the other.
-- One customer has many orders; one order has many order_items.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT, -- foreign key -> customers.id
ordered_on DATE,
total DECIMAL(8,2)
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT, -- foreign key -> orders.id
product VARCHAR(50),
qty INT
);Here is the sample data used throughout. Notice deliberately that customer 4 (Dana) has no orders, and order 103 belongs to customer 9 who is not in the customers table. Those two edge rows are what make the different join types visibly different.
| customers.id | name | city |
|---|---|---|
| 1 | Ava | London |
| 2 | Ben | Leeds |
| 3 | Cara | London |
| 4 | Dana | Bath |
| orders.id | customer_id | ordered_on | total |
|---|---|---|---|
| 100 | 1 | 2026-01-04 | 40.00 |
| 101 | 1 | 2026-02-11 | 18.50 |
| 102 | 2 | 2026-02-19 | 75.00 |
| 103 | 9 | 2026-03-02 | 60.00 |
2. INNER JOIN
An INNER JOIN keeps only rows that match on both sides. Customers with no orders vanish; orders with no matching customer vanish. It is the default and by far the most common join. The word INNER is optional: JOIN alone means INNER JOIN.
SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;Dana (no orders) and order 103 (no customer) are both absent from the result:
| name | order_id | total |
|---|---|---|
| Ava | 100 | 40.00 |
| Ava | 101 | 18.50 |
| Ben | 102 | 75.00 |
Ava appears twice because she has two orders. That is correct and expected: a join produces one row per matching pair, not one row per customer. Keep that in mind for section 13, where duplicate rows quietly corrupt sums.
Because Ava has two orders, she owns two of the three result rows, which is the first hint of a theme that runs through this whole guide: the shape of your result is driven as much by the cardinality of the relationship (one-to-one, one-to-many, many-to-many) as by the join keyword. A one-to-many join like customers-to-orders naturally multiplies the "one" side, and forgetting that is behind most of the surprising row counts and inflated totals people hit in practice.
Two syntactic points worth internalising early. First, table aliases (c for customers, o for orders) are not optional cosmetics; once two tables are in play, a bare id is ambiguous and the database will reject it. Aliasing every table and prefixing every column makes queries both legal and readable. Second, the ON condition can be any boolean expression, not just a single equality. You can join on o.customer_id = c.id AND o.total > 20, and for inner joins that extra term behaves just like a filter. The distinction only becomes load-bearing with outer joins, which is exactly the subject of section 8.
3. LEFT JOIN and RIGHT JOIN
A LEFT JOIN (full name LEFT OUTER JOIN) keeps every row from the left table and attaches matches from the right where they exist. Where there is no match, the right-hand columns come back as NULL. This is the join for "all customers, and their orders if any".
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;Dana now survives, padded with NULLs. Order 103 is still gone, because it has no customer on the left side:
| name | order_id | total |
|---|---|---|
| Ava | 100 | 40.00 |
| Ava | 101 | 18.50 |
| Ben | 102 | 75.00 |
| Cara | NULL | NULL |
| Dana | NULL | NULL |
Finding rows with no match
Because unmatched rows get NULLs on the right, a WHERE ... IS NULL filter isolates exactly the left rows that never matched. This is the canonical "anti-join" pattern (more in section 11):
-- Customers who have never placed an order.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- -> Cara, DanaRIGHT JOIN
A RIGHT JOIN is the exact mirror image: it keeps every row from the right table. The query below keeps all orders, including order 103 whose customer is missing:
SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;| name | order_id |
|---|---|
| Ava | 100 |
| Ava | 101 |
| Ben | 102 |
| NULL | 103 |
Style tip: almost nobody writes RIGHT JOIN in practice. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the two tables, and reading left-to-right ("keep everything from the first table") is easier to reason about. Pick LEFT JOIN and stay consistent.
4. FULL OUTER JOIN (and the MySQL workaround)
A FULL OUTER JOIN keeps all rows from both tables: matched where possible, NULL-padded on whichever side is missing. It is the reconciliation join, "show me everything on either side and flag what does not line up". PostgreSQL, SQL Server and Oracle support it directly:
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;| name | order_id |
|---|---|
| Ava | 100 |
| Ava | 101 |
| Ben | 102 |
| Cara | NULL |
| Dana | NULL |
| NULL | 103 |
Notice both edge rows now appear: Cara and Dana (customers with no orders) and order 103 (an order with no customer). That is the defining feature of a full outer join, and it is precisely what makes it the go-to tool for reconciliation. When you are comparing two systems that should agree, a full outer join on the shared key lists everything, and the rows with a NULL on one side are exactly the discrepancies to investigate.
MySQL and MariaDB have no FULL OUTER JOIN. Emulate it by taking the LEFT JOIN and unioning the rows that only a RIGHT JOIN would add. UNION (not UNION ALL) removes the duplicated matched rows:
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;5. CROSS JOIN (the Cartesian product)
A CROSS JOIN has no ON condition. It pairs every row of the first table with every row of the second, producing a Cartesian product: 3 rows × 4 rows = 12 rows. It is the right tool for generating combinations, such as every size in every colour, or a calendar grid of every product for every day.
SELECT s.label AS size, c.label AS colour
FROM sizes s
CROSS JOIN colours c; -- 3 sizes x 2 colours = 6 rows| size | colour |
|---|---|
| S | Red |
| S | Blue |
| M | Red |
| M | Blue |
| L | Red |
| L | Blue |
Accidental Cartesian products: if you list two tables with a comma (FROM a, b) and forget the join condition in WHERE, you get a full cross join. On two 100,000-row tables that is 10 billion rows and a hung server. Always write explicit JOIN ... ON so a missing condition is a visible mistake, not a silent one.
6. SELF JOIN (hierarchies)
A self join is not a separate keyword. It is a table joined to itself using two aliases, so you can relate rows within one table. The classic case is an employees table where each row stores its own manager's id in the same table.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager;Using LEFT JOIN (rather than INNER) keeps the top of the hierarchy: the CEO has no manager, so manager comes back NULL instead of dropping the row.
| employee | manager |
|---|---|
| Priya | NULL |
| Sam | Priya |
| Tomas | Priya |
| Uma | Sam |
Self joins also find pairs within a table, for example two customers in the same city (a.city = b.city AND a.id < b.id avoids pairing a row with itself and avoids listing each pair twice). The two things that trip people up with self joins are forgetting one of the two aliases (you must name the table twice, as e and m, or the columns are ambiguous) and forgetting the inequality that stops a row matching itself. A self join only walks one level of a hierarchy per join; to traverse an arbitrary depth (employee → manager → manager's manager) you need a recursive common table expression, which is a step beyond a plain join.
7. Joining more than two tables
Real queries rarely stop at two tables. You chain joins: each new JOIN ... ON attaches one more table to the growing result. Here we walk customers → orders → order_items to list what was actually purchased:
SELECT c.name, o.id AS order_id, i.product, i.qty
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;Reading a chain like this is easiest if you think of it as building a single wide virtual table one attachment at a time: start with customers, glue on the matching orders, then glue on the matching order_items. Each join sees the running result of everything to its left, which is why the order you write the joins reads as a natural narrative even though the engine may run them differently. The order matters for readability but the optimiser is free to execute the joins in whatever physical order is cheapest (see section 13). A few rules keep multi-table joins sane:
- Alias every table (
c,o,i) and prefix every column, so ambiguous names likeidare never a guessing game. - Mind the join type across the chain. One
INNER JOINin a chain ofLEFT JOINs can re-filter earlier optional rows back out (the same trap as section 8). - Watch for fan-out. If one order has three items, that order's row is repeated three times, which distorts any sum of
o.total. Aggregate items separately when needed.
8. ON vs WHERE: the LEFT JOIN gotcha
This is the single most common join bug in production SQL, so it earns its own section. The ON clause decides how rows are matched; the WHERE clause filters the final result after joining. For INNER JOIN the distinction rarely matters. For LEFT JOIN it changes the answer.
Say you want every customer plus their January orders, keeping customers who had none. The instinct is to add the date filter in WHERE:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_on >= '2026-01-01'
AND o.ordered_on < '2026-02-01';For customers with no January order, o.ordered_on is NULL, and NULL >= '2026-01-01' is not true, so WHERE throws those rows away. The LEFT JOIN has silently collapsed into an INNER JOIN. Put the condition where it belongs, in ON:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.ordered_on >= '2026-01-01'
AND o.ordered_on < '2026-02-01';The rule: conditions on the right (optional) table of a LEFT JOIN belong in ON, so they change what matches without deleting unmatched left rows. A condition in WHERE that references the right table (other than IS NULL) turns a LEFT JOIN back into an INNER JOIN. This is also number one on our list of common SQL slip-ups.
9. USING and NATURAL JOIN
When both tables name the join column identically, USING is a tidy shorthand for ON a.col = b.col. It also merges the two columns into one in the output, so you select customer_id unqualified:
-- Requires the column to be named customer_id in BOTH tables.
SELECT customer_id, name, total
FROM customers
JOIN orders USING (customer_id);NATURAL JOIN goes further: it automatically joins on every column that shares a name across the two tables, with no ON or USING at all. That convenience is exactly why it is dangerous.
SELECT * FROM customers NATURAL JOIN orders;Avoid NATURAL JOIN. It joins on whatever columns happen to share a name. Add an innocuous created_at or notes column to both tables later and the join silently starts matching on that too, quietly changing results with no code change and no error. Prefer explicit ON (or USING) so the join key is written down and safe from schema drift.
10. Non-equi joins (ranges and BETWEEN)
Nothing says a join condition must be an equals sign. A non-equi join matches on <, >, BETWEEN or any expression. The textbook example is bucketing a value into a tier or band using a lookup table of ranges:
-- tiers(name, min_total, max_total): Bronze 0-49, Silver 50-99, Gold 100+
SELECT o.id, o.total, t.name AS tier
FROM orders o
JOIN tiers t
ON o.total BETWEEN t.min_total AND t.max_total;| id | total | tier |
|---|---|---|
| 100 | 40.00 | Bronze |
| 101 | 18.50 | Bronze |
| 102 | 75.00 | Silver |
| 103 | 60.00 | Silver |
Range joins are powerful but the database usually cannot use a plain index for them as tightly as it can for equality. Keep the range table small, make sure the bands do not overlap (or you get duplicate rows), and cover the full domain so nothing falls between two bands. Other everyday non-equi joins include matching an event to the pricing row that was effective on its date (e.happened_on BETWEEN p.valid_from AND p.valid_to), and joining a row to the "next" row after it (b.seq > a.seq) to compute gaps. Whenever the relationship between two tables is "falls within" rather than "equals", a non-equi join expresses it directly, without a pile of correlated subqueries.
11. Semi-joins and anti-joins
Sometimes you want to test existence without pulling in any columns from the other table. A semi-join returns left rows that have at least one match; an anti-join returns left rows that have none. SQL expresses these with EXISTS / NOT EXISTS and IN / NOT IN, not with an explicit JOIN keyword.
-- Customers who HAVE ordered (no duplicate rows, no order columns).
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);-- Customers who have NOT ordered.
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);A semi-join is often cleaner than an INNER JOIN when you only care "does a match exist?", because a join would produce one row per match (Ava twice) and force a DISTINCT. EXISTS returns each customer once by construction. Under the hood most optimisers recognise the EXISTS pattern and stop scanning the inner table as soon as the first match is found, so it can also be faster than the join-plus-DISTINCT approach. The subquery returns SELECT 1 because EXISTS only cares whether a row comes back, never about its contents; the literal is a convention, not a requirement.
IN and NOT IN express the same idea against a single column: WHERE c.id IN (SELECT customer_id FROM orders) is a semi-join, and its negation an anti-join. They read naturally and are perfectly fine for the positive IN case, but the negative form hides a sharp edge with NULLs, covered in the note below.
The NOT IN / NULL trap: NOT IN (SELECT customer_id FROM orders) returns nothing if that subquery contains a single NULL, because x NOT IN (1, NULL) evaluates to UNKNOWN rather than true. Order 103 has a real id, but a nullable column will bite you here. Prefer NOT EXISTS, which handles NULLs correctly and usually optimises at least as well.
12. Joins vs subqueries; joins with GROUP BY
Many questions can be answered either by a join or by a subquery. For pulling in columns from a related table, a join is usually clearer and lets the optimiser pick the best plan. For pure existence tests, EXISTS often wins. They are tools, not rivals; the advanced SQL queries lesson digs into when each shines.
The most important pairing is join then aggregate. To total each customer's spend, join customers to orders and GROUP BY the customer, wrapping the money column in SUM:
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spend DESC;| name | orders | spend |
|---|---|---|
| Ben | 1 | 75.00 |
| Ava | 2 | 58.50 |
| Cara | 0 | 0.00 |
| Dana | 0 | 0.00 |
The result gives every customer exactly one summary row, including Cara and Dana who never ordered. That "keep everyone, even with zero activity" behaviour is why the query uses LEFT JOIN rather than INNER JOIN; an inner join would have dropped the two customers with no orders and quietly turned a customer report into an "active customers" report.
Two details make this correct. COUNT(o.id) counts orders, not rows, so a customer with no orders scores 0 rather than 1 (because COUNT ignores NULLs, whereas COUNT(*) would count the single NULL-padded row). And COALESCE(SUM(...), 0) turns a NULL sum into a clean zero. This is also the safe way to avoid the fan-out problem: when you must aggregate across two child tables at once, aggregate each in its own subquery first, then join the summaries, or the second table multiplies the first's rows and inflates every total.
13. Performance: indexes, join order, fan-out
Joins are where slow queries are born. The reason is combinatorial: a join has to match rows across tables, and if the database has no fast path to the matching rows, it falls back to comparing far more pairs than the result actually needs. Three habits prevent most of the pain.
Index the join keys
The single biggest win. The database needs a fast way to find matching rows on the other side. Primary keys are indexed automatically, but foreign keys often are not. Add an index on every column you join on, for example CREATE INDEX ix_orders_customer ON orders(customer_id). Without it, each lookup becomes a full table scan. See SQL indexes for how they work.
Let the optimiser choose join order, but help it
You write the joins in a readable order; the optimiser reorders them by estimated cost, usually starting from the most selective table (the one whose filters cut the row count fastest). Keep statistics fresh and filter early in WHERE so its estimates are good. Read the plan with EXPLAIN to see what it actually did.
Avoid fan-out and duplicate rows
Joining a parent to a child that has many rows per parent multiplies the parent's rows. Sum a parent column across that and you over-count. Aggregate the child in a subquery first, or use COUNT(DISTINCT ...), so totals stay honest.
It is also worth knowing how the engine actually executes a join, because it explains why indexes matter so much. Databases pick between a nested loop (for each left row, look up matches on the right, which is superb when the right side is indexed), a hash join (build a hash table of one side, then probe it, which is strong for large unindexed joins), and a merge join (walk both sides in sorted key order). Indexing the join key is what lets the planner choose the cheap nested loop instead of scanning entire tables, and it is why an index on the foreign-key side so often takes a query from seconds to milliseconds.
The chart below reflects the rough impact each fix tends to have on join-heavy query time. Indexing the join keys is almost always the highest-leverage change; the deeper playbook lives in performance tuning.
14. Every join type compared
Here is the whole family on one page. Bookmark this table; it answers "which join do I reach for?" faster than re-reading the sections.
| Join | Returns | Keeps unmatched? | Typical use |
|---|---|---|---|
| INNER | Only matching pairs | Neither side | The default; rows that exist in both tables |
| LEFT | All left + matches | Left only (right = NULL) | All customers, with orders if any |
| RIGHT | All right + matches | Right only (left = NULL) | Mirror of LEFT; usually rewritten as LEFT |
| FULL OUTER | All rows, both sides | Both sides | Reconciliation; spot mismatches on either side |
| CROSS | Every combination | N/A (no condition) | Generate grids and combinations |
| SELF | Rows related within one table | Depends on inner/outer | Hierarchies; pairs within a table |
| SEMI (EXISTS) | Left rows that have a match | No, and no duplicates | "Has at least one" existence tests |
| ANTI (NOT EXISTS) | Left rows with no match | Left only | "Has none"; find orphans |
How often does each turn up in everyday application SQL? Very roughly, most queries are INNER or LEFT joins, with the rest sharing a long tail. The bar chart gives a feel for the distribution.
That is the complete map. If you want the picture-based refresher, keep the visual version open alongside this page, work through the hands-on SQL Joins lesson, and when you are ready to make them fast, index the keys per SQL indexes. Every example above is runnable against the three-table schema at the top, so paste them into your own database and watch each join reshape the result.