INNER JOIN vs LEFT JOIN

Technical Explanation

JOINs combine data from multiple tables based on a related column. The type of JOIN determines which rows are included in the result.

INNER JOIN

Returns only the rows where the join condition matches in both tables. Rows that don't have a match are excluded entirely.

SELECT *
FROM table_A
INNER JOIN table_B
ON table_A.id = table_B.foreign_key

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table. If there's no match, the result contains NULL values for the right table's columns.

SELECT *
FROM table_A
LEFT JOIN table_B
ON table_A.id = table_B.foreign_key

Visual Representation

INNER JOIN (only matching rows):
┌─────┐     ┌─────┐
│  A  │     │  B  │
├─────┤     ├─────┤
│  1  │     │  a  │
│  2  │     │  b  │
│  3  │     │  c  │
└─────┘     └─────┘
     ↓
┌─────────────────────┐
│  A.val │ B.val      │
├────────┼────────────┤
│   1    │ a (match)  │
│   2    │ b (match)  │
│   3    │ c (match)  │ ← Only 3 matches exist
└─────────────────────┘

LEFT JOIN (all from A, matches from B):
┌─────────────────────┐
│  A.val │ B.val      │
├────────┼────────────┤
│   1    │ a (match)  │
│   2    │ b (match)  │
│   3    │ c (match)  │
│   4    │ NULL       │ ← No match in B
│   5    │ NULL       │ ← No match in B
└─────────────────────┘

When to Use Each

JOIN Type	Use When
INNER	You only want rows that have matches in both tables
LEFT	You want all rows from the primary table, even without matches

---

Code Examples

Using the CatCafe dataset:

-- Tables:
-- cats: id, name, breed, age, adoption_status
-- adoptions: id, cat_id, customer_id, adoption_date, fee

-- INNER JOIN: Cats that have been adopted
-- Only cats who actually have an adoption record
SELECT
    cats.name,
    cats.breed,
    adoptions.adoption_date,
    adoptions.fee
FROM cats
INNER JOIN adoptions
    ON cats.id = adoptions.cat_id;
-- Result: Only cats with adoption records

-- LEFT JOIN: All cats and their adoption status
-- Include cats regardless of whether they're adopted
SELECT
    cats.name,
    cats.breed,
    cats.adoption_status,
    adoptions.adoption_date,
    adoptions.fee
FROM cats
LEFT JOIN adoptions
    ON cats.id = adoptions.cat_id;
-- Result: Every cat, with adoption data if available, NULL otherwise

-- Practical example: Find customers who have never adopted
SELECT
    customers.name,
    customers.email
FROM customers
LEFT JOIN adoptions
    ON customers.id = adoptions.customer_id
WHERE adoptions.id IS NULL;
-- Customers with no matching adoption record

-- Multiple LEFT JOINs: Customers with their adoptions and cats
SELECT
    customers.name as customer_name,
    cats.name as cat_name,
    cats.breed,
    adoptions.adoption_date
FROM customers
LEFT JOIN adoptions
    ON customers.id = adoptions.customer_id
LEFT JOIN cats
    ON adoptions.cat_id = cats.id;
-- All customers, their adopted cats (if any)

---

The Cat Analogy

Think of JOINs like a cat shelter matching system:

INNER JOIN is like a "matched only" list:

• You only include cats that have been adopted
• You only include customers who actually adopted
• If a cat is in the shelter but no one adopted it, it's not on your list
• If a customer exists but never adopted, they're not on your list

LEFT JOIN is like a "all cats, matched info" board:

• Every cat in the shelter appears on the board
• Next to each cat, you show adoption info IF they were adopted
• Cats without adoptions show "Not adopted yet"
• But cats without matches still appear!

RIGHT JOIN (mirror of LEFT) would be "all customers, with their cats if they adopted."

---

Common Pitfalls

Unexpected NULLs in LEFT JOIN

-- Finding "unmatched" rows with NULL check
SELECT *
FROM cats
LEFT JOIN adoptions
    ON cats.id = adoptions.cat_id
WHERE adoptions.id IS NULL;  -- Cats without adoptions

-- WRONG: Accidentally filtering out matches
SELECT *
FROM cats
LEFT JOIN adoptions
    ON cats.id = adoptions.cat_id
WHERE adoptions.fee > 0;  -- This REMOVES all NULL matches!
-- Always be careful with WHERE on LEFT JOIN tables

Row Multiplication

-- If a cat has multiple adoption attempts:
-- (maybe previous failed attempts)
SELECT *
FROM cats
LEFT JOIN adoption_attempts
    ON cats.id = adoption_attempts.cat_id;
-- If cat "Whiskers" has 3 attempts, she appears 3 times!
-- This is a common source of duplicate counting

---

Exercises

Exercise 1

Write a query to find all customers and the total number of cats they've adopted. Include customers who have adopted zero cats.

Exercise 2

Write a query to find all cats in the CatCafe database. Mark whether each cat is:

• "Adopted" (has adoption record with fee > 0)
• "Not yet adopted" (no adoption record)

Exercise 3

Explain why this query might give unexpected results:

SELECT cats.name, adoptions.adoption_date
FROM cats
LEFT JOIN adoptions
    ON cats.id = adoptions.cat_id
WHERE cats.age > 5
    AND adoptions.fee > 100;

Exercise 4

Using the customers and orders tables, find:

• All customers
• Their total spending (if they've placed orders)
• Only customers whose total spending exceeds $200

Exercise 5

What type of JOIN would you use to:

1. Find all cats and their owners (if adopted)?
2. Find only customers who have made at least one purchase?
3. Find products that have never been ordered?

---

Key Takeaways

• INNER JOIN = only matching rows from both tables
• LEFT JOIN = all rows from left table + matches from right (NULL if no match)
• Be careful with WHERE clauses on LEFT JOIN tables—they can eliminate your NULL matches
• LEFT JOINs can cause row multiplication if the right table has multiple matches per key
• Use IS NULL to find unmatched rows in a LEFT JOIN