When to Use Which Join

Introduction

You've learned all the JOIN types: INNER, LEFT, RIGHT, FULL OUTER, and SELF. But when do you use each one? This lesson provides practical decision-making guidelines to help you choose the right JOIN for any situation.

The JOIN Decision Tree

START: What data do you need?

├─ Only rows with matches in both tables?
│  └─ Use INNER JOIN
│
├─ All rows from one table + matches from another?
│  ├─ All from left table?
│  │  └─ Use LEFT JOIN
│  │
│  └─ All from right table?
│     └─ Use RIGHT JOIN (or swap tables + LEFT JOIN)
│
├─ All rows from both tables (matched and unmatched)?
│  └─ Use FULL OUTER JOIN
│
└─ Comparing rows within the same table?
   └─ Use SELF JOIN

INNER JOIN - When You Need Only Matches

Use INNER JOIN When:

1. Both sides must exist
2. You only care about related data
3. Missing relationships should be excluded

Examples

✅ Good use of INNER JOIN:

-- Get orders with customer details
-- (Orders without customers shouldn't exist)
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

✅ Product sales report:

-- Only products that have been sold
SELECT
    p.name,
    SUM(oi.quantity) AS units_sold
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

✅ Active user sessions:

-- Users currently logged in
SELECT u.username, s.login_time
FROM users u
INNER JOIN sessions s ON u.id = s.user_id
WHERE s.active = TRUE;

❌ Don't Use INNER JOIN When:

-- ❌ Want all customers, including those without orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
-- Missing customers with 0 orders!

-- ✅ Use LEFT JOIN instead
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

LEFT JOIN - When You Need All from Left

Use LEFT JOIN When:

1. Left table is primary (all rows needed)
2. Right table data is supplementary (optional)
3. You want to find missing relationships

Examples

✅ Customer list with order counts:

-- All customers, even those without orders
SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

✅ Find customers who haven't ordered:

-- Customers without any orders
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

✅ User profiles (optional data):

-- All users, with profiles if they exist
SELECT
    u.username,
    COALESCE(p.bio, 'No bio') AS bio
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id;

✅ Products with sales (including unsold):

-- All products, including those never purchased
SELECT
    p.name,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

❌ Don't Use LEFT JOIN When:

-- ❌ Only want customers who have ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NOT NULL;
-- Inefficient! Filter negates LEFT JOIN benefit

-- ✅ Use INNER JOIN instead
SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

RIGHT JOIN - Rarely Needed

Use RIGHT JOIN When:

Rarely! RIGHT JOIN is almost always replaceable with LEFT JOIN by swapping table order.

-- These are equivalent:

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

-- LEFT JOIN (preferred)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Recommendation: Stick with LEFT JOIN for consistency.

The Only Time to Use RIGHT JOIN:

When adding to an existing query and you can't easily rearrange:

-- Existing query using LEFT JOIN
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
-- Need to add locations, but want all locations
RIGHT JOIN locations l ON d.location_id = l.id;
-- (But even this can be refactored!)

FULL OUTER JOIN - When You Need Everything

Use FULL OUTER JOIN When:

1. Need all rows from both tables
2. Finding orphaned/unmatched records on either side
3. Data reconciliation between systems

Examples

✅ Find mismatches between two systems:

-- Compare expected vs actual inventory
SELECT
    COALESCE(e.product_id, a.product_id) AS product_id,
    e.quantity AS expected,
    a.quantity AS actual
FROM expected_inventory e
FULL OUTER JOIN actual_inventory a ON e.product_id = a.product_id
WHERE e.quantity IS NULL  -- Only in actual
   OR a.quantity IS NULL  -- Only in expected
   OR e.quantity <> a.quantity;  -- Different quantities

✅ Audit report (all entities):

-- All employees and all departments
SELECT
    COALESCE(e.name, 'No employee') AS employee,
    COALESCE(d.name, 'No department') AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

✅ Merge two user lists:

-- Combine users from two databases
SELECT COALESCE(db1.email, db2.email) AS email
FROM database1_users db1
FULL OUTER JOIN database2_users db2 ON db1.email = db2.email;

❌ Don't Use FULL OUTER JOIN When:

-- ❌ Only need matches (INNER JOIN is faster)
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NOT NULL AND d.id IS NOT NULL;

-- ✅ Use INNER JOIN
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

SELF JOIN - Comparing Within Same Table

Use SELF JOIN When:

1. Hierarchical data (managers, categories, threads)
2. Finding pairs (same date, same price, etc.)
3. Comparing versions (current vs previous)

Examples

✅ Employee-manager hierarchy:

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

✅ Find duplicate emails:

SELECT DISTINCT e1.email
FROM users e1
INNER JOIN users e2 ON e1.email = e2.email
WHERE e1.id < e2.id;

✅ Find employees hired on same day:

SELECT
    e1.name AS employee1,
    e2.name AS employee2,
    e1.hire_date
FROM employees e1
INNER JOIN employees e2 ON e1.hire_date = e2.hire_date
WHERE e1.id < e2.id;

Quick Reference Guide

Common Scenarios

Scenario 1: Customer Order Report

Need: List all customers with their total spent

-- ✅ LEFT JOIN (include customers with $0 spent)
SELECT
    c.name,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Scenario 2: Product Sales Leaderboard

Need: Top 10 products by revenue (only products sold)

-- ✅ INNER JOIN (exclude products never sold)
SELECT
    p.name,
    SUM(oi.quantity * oi.price) AS revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10;

Scenario 3: User Activity Dashboard

Need: All users with their last login (even if never logged in)

-- ✅ LEFT JOIN (include users who never logged in)
SELECT
    u.username,
    MAX(l.login_at) AS last_login
FROM users u
LEFT JOIN logins l ON u.id = l.user_id
GROUP BY u.id, u.username;

Scenario 4: Inventory Reconciliation

Need: Find differences between two systems

-- ✅ FULL OUTER JOIN (find items in either system)
SELECT
    COALESCE(sys1.sku, sys2.sku) AS sku,
    sys1.quantity AS system1_qty,
    sys2.quantity AS system2_qty
FROM system1_inventory sys1
FULL OUTER JOIN system2_inventory sys2 ON sys1.sku = sys2.sku
WHERE sys1.quantity <> sys2.quantity
   OR sys1.sku IS NULL
   OR sys2.sku IS NULL;

Performance Tips by JOIN Type

INNER JOIN

• Fastest (smallest result set)
• Index both join columns
• Use when possible

LEFT JOIN

• Slower than INNER (larger result set)
• Index right table's join column
• Filter in ON (not WHERE) to preserve left rows

FULL OUTER JOIN

• Slowest (largest result set)
• Index both join columns
• Only use when necessary

SELF JOIN

• Can be slow on large tables
• Index the join column
• Use WHERE to limit comparisons

Practice Scenarios

For each scenario, decide which JOIN to use:

Scenario A

List all blog posts with author names. Some posts might not have authors.

SELECT p.title, a.name
FROM posts p
LEFT JOIN authors a ON p.author_id = a.id;

Scenario B

Show active orders with customer and product details.

SELECT c.name, p.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'active';

Scenario C

Find employees and their direct reports (one level down).

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

Scenario D

Compare two price lists to find SKUs that exist in one but not the other.

SELECT COALESCE(p1.sku, p2.sku) AS sku
FROM pricelist1 p1
FULL OUTER JOIN pricelist2 p2 ON p1.sku = p2.sku
WHERE p1.sku IS NULL OR p2.sku IS NULL;

Key Takeaways

• ✅ INNER JOIN: Only matches (default choice)
• ✅ LEFT JOIN: All from left + matches from right
• ✅ RIGHT JOIN: Rarely needed (use LEFT instead)
• ✅ FULL OUTER JOIN: All from both (uncommon)
• ✅ SELF JOIN: Compare within same table
• ✅ Start with INNER, switch to LEFT if you need unmatched rows
• ✅ Performance: INNER fastest, FULL OUTER slowest

Next Steps

Congratulations! You've completed Module 4 and mastered JOINs—one of SQL's most powerful features. In Module 5, we'll learn Aggregate Functions and Grouping—how to summarize data with COUNT, SUM, AVG, GROUP BY, and HAVING.

Get ready to analyze data like a pro!