SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL with Real Examples

What Is a SQL JOIN?

A SQL JOIN combines rows from two or more tables based on a related column. JOINs are the foundation of relational databases — they let you query data that's split across multiple tables without duplicating it.

Imagine an e-commerce database. You have an orders table and a customers table. A JOIN lets you fetch an order and the customer who placed it in a single query.

---

The Sample Tables

Throughout this guide we'll use two tables:

customers

id	name	email
1	Alice Brown	alice@example.com
2	Bob Smith	bob@example.com
3	Carol White	carol@example.com

orders

id	customer_id	product	amount
1	1	Laptop	999.00
2	1	Mouse	29.00
3	2	Keyboard	79.00
4	5	Monitor	349.00

Notice: Carol (id=3) has no orders. Order 4 has customer_id=5 which doesn't exist in customers.

---

INNER JOIN

Returns only rows where there is a match in both tables.

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

Result:

name	product	amount
Alice Brown	Laptop	999.00
Alice Brown	Mouse	29.00
Bob Smith	Keyboard	79.00

Carol has no orders — excluded. Order 4 has no matching customer — excluded.

Use INNER JOIN when you only want rows with data on both sides.

---

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and matching rows from the right. If no match, right-side columns are NULL.

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

Result:

name	product	amount
Alice Brown	Laptop	999.00
Alice Brown	Mouse	29.00
Bob Smith	Keyboard	79.00
Carol White	NULL	NULL

Carol appears with NULLs. Use LEFT JOIN when you want all records from the left table, even if they have no related data.

Practical use case: Find customers who have never placed an order:

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

---

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN — all rows from the right table, matching rows from the left.

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

Result:

name	product	amount
Alice Brown	Laptop	999.00
Alice Brown	Mouse	29.00
Bob Smith	Keyboard	79.00
NULL	Monitor	349.00

Order 4 appears with NULL customer. In practice, RIGHT JOIN is rare — most developers flip the table order and use LEFT JOIN instead for readability.

---

FULL OUTER JOIN

Returns all rows from both tables. NULLs fill in where there's no match on either side.

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

Result:

name	product	amount
Alice Brown	Laptop	999.00
Alice Brown	Mouse	29.00
Bob Smith	Keyboard	79.00
Carol White	NULL	NULL
NULL	Monitor	349.00

Note: MySQL doesn't support FULL OUTER JOIN natively — you can simulate it with UNION of LEFT and RIGHT JOINs.

---

SELF JOIN

A table joining itself. Useful for hierarchical data like employee-manager relationships.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

---

CROSS JOIN

Returns the Cartesian product — every row from the left table combined with every row from the right. Rarely used intentionally.

SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

If colors has 3 rows and sizes has 4, you get 12 rows.

---

Performance Tips

1. Always JOIN on indexed columns. Primary and foreign keys are indexed by default — make sure your join columns have indexes if they're not PKs/FKs.
2. Filter early. Add WHERE conditions to reduce rows before the JOIN, not after.
3. *Avoid SELECT . Only select the columns you need.
4. Use EXPLAIN. EXPLAIN SELECT ... shows you how the database plans to execute your query.

---

Quick Reference

JOIN Type	Left table rows	Right table rows
INNER JOIN	Matching only	Matching only
LEFT JOIN	All	Matching only
RIGHT JOIN	Matching only	All
FULL OUTER	All	All

---

Conclusion

JOINs are the backbone of SQL. INNER JOIN is what you'll use 80% of the time. LEFT JOIN is indispensable for finding missing relationships. Once you internalize the mental model — which rows survive, which become NULL — you'll write complex multi-table queries confidently.