SQL Joins Complete Guide

Inner, Left, Right, Full Outer, Cross, Self & Natural Joins — clearly explained with examples

Master SQL joins using a single, consistent scenario. This guide removes repetition, keeps every important detail, and presents joins in a compact, practical, and reusable format.

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column.

Indexes on join columns significantly affect performance.

Common join families

INNER JOIN /Equi Join / Simple Join – only matching rows [intersection] (oftern equi join)
OUTER JOIN / Non Equi Join – keeps non‑matching rows
- LEFT JOIN / LEFT OUTER JOIN / Include Left – keep all rows from the left table [NULLs where no match]
- RIGHT JOIN / RIGHT OUTER JOIN / Include Right – keep all rows from the right table [NULLs where no match]
- FULL OUTER JOIN / Include Both – keep all rows from both tables [union(with gaps)] [NULLs where no match]
CROSS JOIN / Cartesian Product – all possible combinations [cartesian product]
SELF JOIN /Recursive Join / Reflexive Join – table joined to itself (relate within one dataset) [alias]
NATURAL JOIN – auto joins by shared names/ automatic join on same‑named columns [risky, avoid in production]
SEMI / ANTI JOIN – exists / not exists

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- Typical join forms / JOIN Syntax – Quick Summary
-- Examples of different JOIN types (each used separately, not together)
SELECT c.customer_id, c.name, o.order_id, o.total_amount
FROM customers AS c
INNER JOIN orders AS o      ON c.customer_id = o.customer_id;  -- Match records in both
LEFT JOIN orders AS o       ON c.customer_id = o.customer_id;  -- Keep all customers
RIGHT JOIN orders AS o      ON c.customer_id = o.customer_id;  -- Keep all orders
FULL OUTER JOIN orders AS o ON c.customer_id = o.customer_id;  -- Keep all records
LEFT JOIN customers AS r    ON c.referred_by = r.customer_id;  -- Self join example (useing table alias)
NATURAL JOIN orders;                                           -- Auto-match by shared columns

---

## Scenario and Base Tables

Imagine a small **online store**. All Tables are based on the below schema.:
- customers (`customer_id`, `name`, `city`, `referred_by`)
- orders (`order_id`, `customer_id`, `order_date`, `total_amount`)
- order_items (`order_id`, `product_id`, `quantity`, `unit_price`)
- products (`product_id`, `product_name`, `category`)
- cities (`city`)
- categories (`category`)

---

## Sample Data

### customers

| customer_id | name  | city     | referred_by |
|-------------|-------|----------|-------------|
| 1           | Alice | New York | NULL        |
| 2           | Bob   | Boston   | 1           |
| 3           | Carol | Chicago  | 1           |
| 4           | David | Denver   | 2           |
-- Observation: David has no orders (important for outer joins).


### orders

| order_id | customer_id | order_date | total_amount |
| -------- | ----------- | ---------- | ------------ |
| 101      | 1           | 2026-01-01 | 120.00       |
| 102      | 1           | 2026-01-05 | 60.00        |
| 103      | 2           | 2026-01-07 | 80.00        |
| 104      | 2           | 2026-01-09 | 40.00        |
| 105      | 3           | 2026-01-10 | 55.00        |
| 106      | 99          | 2026-01-12 | 100.00       |
-- customer_id = 99 does not exist in customers


### order_items

| order_id | product_id | quantity | unit_price |
| -------- | ---------- | -------- | ---------- |
| 101      | 10         | 1        | 100.00     |
| 101      | 11         | 2        | 10.00      |
| 102      | 11         | 3        | 20.00      |
| 103      | 12         | 1        | 80.00      |
| 105      | 13         | 5        | 11.00      |


### products

| product_id | product_name | category    |
| ---------- | ------------ | ----------- |
| 10         | Laptop       | Electronics |
| 11         | Mouse        | Electronics |
| 12         | Headphones   | Electronics |
| 13         | Notebook     | Stationery  |
| 14         | Pen          | Stationery  |
-- Observation:** Product 14 has never been ordered.

1. INNER JOIN

Concept: Only rows with matches in both tables.
Use case: Find active users, existing matches, or linked records.

Example: Customers with at least one order

1
2
3
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

1
2
3
4
5
6
7
| customer_id | name  | order_id | order_date | total_amount |
|-------------|-------|----------|------------|--------------|
| 1           | Alice | 101      | 2026-01-01 | 120.00       |
| 1           | Alice | 102      | 2026-01-05 | 60.00        |
| 2           | Bob   | 103      | 2026-01-07 | 80.00        |
| 2           | Bob   | 104      | 2026-01-09 | 40.00        |
| 3           | Carol | 105      | 2026-01-10 | 55.00        |

Result: Alice, Bob, Carol appear; David does not.

Example: Order lines with product info

1
2
3
SELECT oi.order_id, p.product_name, oi.quantity, oi.unit_price
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id;

Note: Product 14 is excluded, as it was never ordered.

2. LEFT (OUTER) JOIN

Concept: All rows from the left table; unmatched right columns become NULL.
Use case: Identify customers who never purchased.

Example: All customers, including those with no orders

1
2
3
4
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

1
2
3
4
5
6
7
8
| customer_id | name   | order_id | order_date  | total_amount |
|------------:|--------|---------:|------------|-------------:|
| 1           | Alice  | 101      | 2026-01-01 | 120.00       |
| 1           | Alice  | 102      | 2026-01-05 | 60.00        |
| 2           | Bob    | 103      | 2026-01-07 | 80.00        |
| 2           | Bob    | 104      | 2026-01-09 | 40.00        |
| 3           | Carol  | 105      | 2026-01-10 | 55.00        |
| 4           | David  | NULL     | NULL       | NULL         |

Meaning: Shows customers who never ordered (David).

Include even if no detail

1
2
3
SELECT o.order_id, o.customer_id, oi.product_id, oi.quantity
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

Note: Unordered or draft orders show NULL for product fields.

3. RIGHT (OUTER) JOIN

Concept: Mirror of LEFT JOIN; keeps all rows from the right table.
Use case: Audits, orphaned records.

Example: All orders, even with missing customers

1
2
3
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

1
2
3
4
5
6
7
8
| customer_id | name   | order_id | order_date  | total_amount |
|------------:|--------|---------:|------------|-------------:|
| 1           | Alice  | 101      | 2026-01-01 | 120.00       |
| 1           | Alice  | 102      | 2026-01-05 | 60.00        |
| 2           | Bob    | 103      | 2026-01-07 | 80.00        |
| 2           | Bob    | 104      | 2026-01-09 | 40.00        |
| 3           | Carol  | 105      | 2026-01-10 | 55.00        |
| NULL        | NULL   | 106      | 2026-01-12 | 100.00       |

If order_id=106 has customer_id=99 (missing), customer fields appear NULL. Use case: Audit data completeness or orphan records.

Tip: Often avoided by swapping tables and using LEFT JOIN.

4. FULL OUTER JOIN

Concept: Keeps all rows from both tables. Use case: Audits, orphaned records, reconciliations and completeness checks.

1
2
3
4
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY COALESCE(c.customer_id, o.customer_id);

1
2
3
4
5
6
7
8
9
| customer_id | name   | order_id | order_date  | total_amount |
|------------:|--------|---------:|------------|-------------:|
| 1           | Alice  | 101      | 2026-01-01 | 120.00       |
| 1           | Alice  | 102      | 2026-01-05 | 60.00        |
| 2           | Bob    | 103      | 2026-01-07 | 80.00        |
| 2           | Bob    | 104      | 2026-01-09 | 40.00        |
| 3           | Carol  | 105      | 2026-01-10 | 55.00        |
| 4           | David  | NULL     | NULL       | NULL         |
| NULL        | NULL   | 106      | 2026-01-12 | 100.00       |

David (no orders) and order 106 (no customer) both appear. Not supported directly in MySQL (use UNION of left + right joins).

Note: Not supported natively in MySQL.

5. CROSS JOIN

Concept: Cartesian product — every combination.
Use case: Generate all input combinations (e.g., campaign targets by city × product).

Example: City × Category combinations

1
2
3
SELECT ci.city, ca.category
FROM cities ci
CROSS JOIN categories ca;

Use case: Planning matrices, forecasts.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

| city       |
|------------|
| New York   |
| Boston     |
| Chicago    |
| Denver     |


| category    |
|-------------|
| Electronics |
| Stationery  |


SELECT
    ci.city,
    ca.category
FROM cities AS ci
CROSS JOIN categories AS ca
ORDER BY ci.city, ca.category;


Result:

| city     | category    |
|----------|-------------|
| Boston   | Electronics |
| Boston   | Stationery  |
| Chicago  | Electronics |
| Chicago  | Stationery  |
| Denver   | Electronics |
| Denver   | Stationery  |
| New York | Electronics |
| New York | Stationery  |

6. SELF JOIN

Concept: Table joined to itself.
Use case: Organizational hierarchies, referral relationships.

Example: Customer referrals

1
2
3
4
5
SELECT c.customer_id,
       c.name AS customer_name,
       r.name AS referrer_name
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id;

Output: Alice has none; Bob and Carol referred by Alice; David by Bob.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
table: customers (extended)
| customer_id | name   | city      | referred_by |
|------------:|--------|-----------|------------:|
| 1           | Alice  | New York  | NULL        |
| 2           | Bob    | Boston    | 1           |
| 3           | Carol  | Chicago   | 1           |
| 4           | David  | Denver    | 2           |


SELECT
    c.customer_id,
    c.name          AS customer_name,
    r.name          AS referrer_name
FROM customers AS c
LEFT JOIN customers AS r
    ON c.referred_by = r.customer_id
ORDER BY c.customer_id;


Result:

| customer_id | customer_name | referrer_name |
|------------:|---------------|--------------|
| 1           | Alice         | NULL         |
| 2           | Bob           | Alice        |
| 3           | Carol         | Alice        |
| 4           | David         | Bob          |

Real-life meaning: Same table holds both "customer" and "referrer," so you join it to itself with different aliases.

7. USING vs ON

Most joins use explicit ON. Some databases allow USING to simplify joins using identical column names.

ON (recommended)

1
JOIN orders o ON customers.customer_id = orders.customer_id    -- explicit

Explicit
Clear
Production‑safe

USING

1
JOIN orders USING (customer_id)    -- implicit (shorthand for identical names)

Shorter
Requires same column name
Hides duplicate column

8. NATURAL JOIN

Concept: Automatically joins on same‑named columns.
Automatically joins tables on all same-named columns of compatible data types.
No need to specify ON or USING.
Duplicate join columns appear only once.
⚠️ Hidden join logic makes this risky for production use.

1
2
3
SELECT customer_id, name, order_id, order_date
FROM customers
NATURAL JOIN orders;

NATURAL LEFT / RIGHT JOIN

1
SELECT * FROM customers NATURAL LEFT JOIN orders;

Why it’s risky

Implicit join logic
Breaks if schema changes
Hard to read and debug

Rule: If the join matters, write it explicitly.

Why NATURAL JOIN is Risky

Issue	Why it Matters
Implicit join columns	Adding new same-named columns changes results silently
Hard to read	Join logic not visible in query
Fragile schema dependency	Renaming columns breaks behavior
Debugging pain	Results differ from expectation without explicit control

👉 Best Practice:
Write joins explicitly unless the dataset is temporary or for quick exploration.

Safe (✅): Ad-hoc queries, teaching demos
Unsafe (❌): Production code, analytics, shared systems

NATURAL vs USING vs ON (Quick Comparison)

Feature	NATURAL JOIN	USING	ON
Join columns explicit	❌ No	✅ Yes	✅ Yes
Safe for production	❌ No	⚠️ Sometimes	✅ Yes
Readability	Low	Medium	High
Column control	Low	Medium	High
One-line mental model	“Database, guess my join condition”	“Join on this shared column”	“Join exactly as I specify”

9. Which JOIN should I use?

Goal	Join	Why
Only matching rows	INNER	Exact matches only
Keep all left rows	LEFT	Show missing details
Keep all right rows	RIGHT	Audit / cleanup
Everything	FULL	Reconciliation
Only existence check	SEMI	Fast, no duplication
Find missing data	ANTI	Clean, explicit
All combinations	CROSS	Planning
Hierarchies	SELF	Same table roles

Goal / Question	Recommended Join	Why
List customers who have placed orders	INNER JOIN	Only matched rows
List all customers, even without orders	LEFT JOIN	Keeps all customers
Show all orders, even if missing customer info	RIGHT (or swapped LEFT)	Keeps all orders
Show every customer & every order	FULL OUTER JOIN	Returns everything
Generate all city × category combinations	CROSS JOIN	Cartesian product
Show customers with their referrers	SELF JOIN	Join within same table

Join Type	MySQL	PostgreSQL	SQL Server	Oracle
INNER	✅	✅	✅	✅
LEFT	✅	✅	✅	✅
RIGHT	✅	✅	✅	✅
FULL OUTER	❌	✅	✅	✅
CROSS	✅	✅	✅	✅
NATURAL	✅	✅	❌	✅
USING	✅	✅	❌	✅

SEMI and ANTI JOIN (EXISTS / NOT EXISTS)

SEMI and ANTI joins are logical patterns, not actual SQL JOIN keywords. They are implemented using EXISTS and NOT EXISTS.

SEMI JOIN → return left-table rows only if a match exists in the right table
ANTI JOIN → return left-table rows only if no match exists in the right table
Right-table columns are never returned

SEMI JOIN — customers who have placed at least one order

Definition Return rows from the left table only when at least one matching row exists in the right table.

1
2
3
4
5
6
7
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Result

customer_id	name
1	Alice
2	Bob
3	Carol

Explanation

David is excluded because no matching order exists
Similar to INNER JOIN, but:
- no row duplication
- only left-table columns are returned

ANTI JOIN — customers who have never placed an order

Definition Return rows from the left table only when no matching row exists in the right table.

1
2
3
4
5
6
7
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Result

customer_id	name
4	David

Explanation

David has no matching rows in orders
Common use cases:
- finding missing data
- audits
- cleanup and validation queries

SEMI / ANTI vs JOINs (important distinction)

Pattern	Returns right-table columns	Can duplicate rows
INNER JOIN	✅ Yes	✅ Yes
LEFT JOIN + IS NULL	❌ No	❌ No
SEMI JOIN (`EXISTS`)	❌ No	❌ No
ANTI JOIN (`NOT EXISTS`)	❌ No	❌ No

Rule of thumb

SEMI JOIN → “I only care if a match exists”
ANTI JOIN → “Show me what’s missing”

SQL Joins Complete Guide#

What is a JOIN?#

1. INNER JOIN#

Example: Customers with at least one order#

Example: Order lines with product info#

2. LEFT (OUTER) JOIN#

Example: All customers, including those with no orders#

Include even if no detail#

3. RIGHT (OUTER) JOIN#

Example: All orders, even with missing customers#

4. FULL OUTER JOIN#

5. CROSS JOIN#

Example: City × Category combinations#

6. SELF JOIN#

Example: Customer referrals#

7. USING vs ON#

ON (recommended)#

USING#

8. NATURAL JOIN#

NATURAL LEFT / RIGHT JOIN#

Why it’s risky#

Why NATURAL JOIN is Risky#

NATURAL vs USING vs ON (Quick Comparison)#

9. Which JOIN should I use?#

SEMI and ANTI JOIN (EXISTS / NOT EXISTS)#

SEMI JOIN — customers who have placed at least one order#

ANTI JOIN — customers who have never placed an order#

SEMI / ANTI vs JOINs (important distinction)#