SQL JOIN Types Explained

SQL JOIN Types Explained (INNER, LEFT, RIGHT, FULL) with Examples

JOINs are the single most-used SQL concept and also the most commonly half-understood one. Once you can picture what each JOIN type actually keeps and discards, the syntax stops being something to memorize and starts being something you can reason through.

📘 The Setup

Imagine two tables:

-- customers
customer_id | name
1           | Alice
2           | Bob
3           | Carol

-- orders
order_id | customer_id | amount
101      | 1           | 50
102      | 1           | 20
103      | 4           | 75   -- note: customer_id 4 doesn't exist in customers

Carol (id 3) has no orders. Order 103 belongs to customer_id 4, who doesn't exist in the customers table. This mismatch is exactly what makes JOIN behavior visible.

🧪 INNER JOIN — only matching rows

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

Result: Alice's two orders only. Carol is excluded (no matching order), and order 103 is excluded (no matching customer). INNER JOIN keeps only rows that match on both sides.

🧪 LEFT JOIN — everything from the left table, matched or not

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

Result: Alice's two orders, plus Carol with NULLs for order_id and amount (she has no matching order, but she's still included because she's in the "left" table). Order 103 still doesn't appear.

🧪 RIGHT JOIN — everything from the right table, matched or not

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

Result: Alice's two orders, plus order 103 with NULL for name (its customer_id doesn't exist in customers). Carol disappears — she's not in the "right" table.

In practice, RIGHT JOIN is rarely used — most people just swap the table order and use LEFT JOIN instead, since it reads more naturally.

🧪 FULL OUTER JOIN — everything from both sides

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

Result: Alice's two orders, Carol with NULLs, and order 103 with NULL for name — every row from both tables is present, with NULLs filling in wherever there's no match. (Note: MySQL doesn't support FULL OUTER JOIN directly — you simulate it with a LEFT JOIN UNION RIGHT JOIN.)

🎯 Quick Reference

JOIN Type	Keeps
INNER JOIN	Only rows matching on both sides
LEFT JOIN	All left rows + matches from right (NULL if no match)
RIGHT JOIN	All right rows + matches from left (NULL if no match)
FULL OUTER JOIN	All rows from both sides, matched or not

⚠️ Common Mistakes

• Using INNER JOIN when you actually need LEFT JOIN — silently dropping rows that have no match (a very common source of "missing data" bugs in reports)
• Forgetting that a LEFT JOIN followed by a WHERE clause on the right table's column can accidentally turn it back into an INNER JOIN (filtering out the NULLs you wanted to keep)
• Not indexing the JOIN columns, causing full table scans on large tables

🔗 Related Posts

• Common Table Expressions (CTEs) in SQL — A Beginner's Guide
• Indexing in SQL Explained: How to Speed Up Slow Queries