SQL Joins Explained: INNER, LEFT, RIGHT & FULL with Examples

A SQL join combines rows from two tables wherever a column matches. The four standard types differ only in what they do with rows that don't match: INNER JOIN drops them, LEFT JOIN keeps every row from the left table, RIGHT JOIN keeps every row from the right table, and FULL OUTER JOIN keeps everything from both sides, filling the gaps with NULL.

The Two Tables We'll Use

customers

orders

Note the two deliberate mismatches: customer 3 (Chen) has no orders, and order 104 points to customer 9, who doesn't exist.

INNER JOIN — Matches Only

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Chen disappears (no order), and order 104 disappears (no customer). INNER JOIN answers: "show me only the rows that exist on both sides." Note that Aisha appears twice — a join returns one output row per matching pair, not per customer.

LEFT JOIN — Keep Everything on the Left

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Same as before, plus Chen with NULL in the order columns. Use LEFT JOIN when the left table is your "roster" and you cannot afford to lose anyone from it.

RIGHT JOIN — Keep Everything on the Right

SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Now the orphan order 104 survives with NULL for the customer. In practice RIGHT JOIN is rare: A RIGHT JOIN B is identical to B LEFT JOIN A, and most teams standardize on LEFT for readability.

FULL OUTER JOIN — Keep Both Sides

SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Both mismatches appear. This is the join for reconciliation jobs — "show me everything in either system and flag what doesn't line up." (MySQL doesn't support it natively; emulate it with a LEFT JOIN UNION RIGHT JOIN.)

Joins in Set Notation

If $A$ and $B$ are the sets of rows that find a match, then:

• INNER JOIN returns $A \cap B$ — the overlap of the classic Venn diagram
• LEFT JOIN returns $A \cap B$ plus the unmatched part of $A$
• FULL OUTER JOIN returns $A \cup B$

Row counts can surprise you. With duplicate keys, a join can return up to

$|A| \times |B|$

rows — if 3 rows in $A$ share a key with 4 rows in $B$, that one key alone produces $3 \times 4 = 12$ output rows.

Worked Example 1: Total Spend per Customer (Including Zero)

"Report every customer's total spending — customers with no orders must show 0."

SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spend
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total_spend DESC;

Two decisions matter here: LEFT JOIN (not INNER) so Chen isn't silently dropped, and COALESCE to turn SUM(NULL) into a readable 0.

Worked Example 2: Finding Rows With No Match (Anti-Join)

"Which customers have never ordered?" Keep everyone with LEFT JOIN, then filter down to the NULL rows:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The filter must test a column that can't legitimately be NULL in a matched row — the right table's primary key is the safe choice. The same pattern flipped (orders LEFT JOIN customers ... WHERE c.id IS NULL) finds the orphan order 104.

Common Mistakes

Filtering the right table in WHERE after a LEFT JOIN. WHERE o.amount > 25 discards the NULL rows, silently turning your LEFT JOIN into an INNER JOIN. Put right-table conditions in the ON clause instead: ON o.customer_id = c.id AND o.amount > 25.

Joining on a non-unique column. If the join key repeats on both sides, rows multiply (the $|A| \times |B|$ effect) and every SUM and COUNT downstream inflates. Always know which side of your join is unique.

Expecting NULL = NULL to match. In SQL, NULL never equals anything, including itself — rows with NULL join keys match nothing in every join type. Filter them explicitly or use IS NOT DISTINCT FROM where supported.

Forgetting the ON clause. SELECT * FROM a, b or a CROSS JOIN b pairs every row with every row — 1,000 × 1,000 rows is a million-row result before you notice.

Picking the Right Join in Ten Seconds

Ask one question: whose rows am I not allowed to lose? Nobody's → INNER. The left table's → LEFT. Both sides' → FULL OUTER. And if you're hunting for the rows that don't match, reach for the LEFT JOIN ... WHERE key IS NULL anti-join pattern from Example 2 — it comes up in interviews and production code alike.