Why JOINs Matter

Technical Explanation

JOINs are the foundation of relational data modeling. They allow you to combine data from multiple tables based on relationships, enabling analysis that spans entities. Without JOINs, you'd need to store redundant data or couldn't analyze relationships at all.

The Problem JOINs Solve

customers table:          orders table:
┌────┬────────┐           ┌────┬────────────┬──────────┐
│ id │ name   │           │ id │ customer_id│ amount   │
├────┼────────┤           ├────┼────────────┼──────────┤
│ 1  │ Alice  │           │101 │ 1          │ 50.00    │
│ 2  │ Bob    │           │102 │ 1          │ 30.00    │
│ 3  │ Carol  │           │103 │ 2          │ 75.00    │
└────┴────────┘           └────┴────────────┴──────────┘

Without JOIN: Can I see "Alice's orders"? No.
With JOIN: I can relate customers to their orders.

Types of Relationships

Relationship	Description	Example
One-to-One	Each row in A matches one row in B	user ↔ user_profile
One-to-Many	Each row in A matches multiple rows in B	customer → orders
Many-to-Many	Multiple rows in A match multiple rows in B	cats ↔ toys (via adoption_history)

Why Denormalization Fails

Storing all data in one table ("denormalized") causes:

• Redundancy: Customer name repeated in every order
• Update anomalies: Changing a name requires updates everywhere
• Insert anomalies: Can't have an order without a customer

JOINs let you normalize data while maintaining the ability to query across relationships.

---

Code Examples

Using the CatCafe dataset:

-- cats: id, name, breed, age, adoption_status
-- customers: id, name, email, city
-- adoptions: id, cat_id, customer_id, adoption_date, fee

-- One-to-Many: Customer to their adoptions
SELECT
    customers.name as customer_name,
    customers.email,
    cats.name as cat_name,
    cats.breed,
    adoptions.adoption_date,
    adoptions.fee
FROM customers
JOIN adoptions
    ON customers.id = adoptions.customer_id
JOIN cats
    ON adoptions.cat_id = cats.id
ORDER BY adoptions.adoption_date DESC;

-- Analysis: Which cities have the most cat adoptions?
SELECT
    customers.city,
    COUNT(adoptions.id) as adoption_count,
    SUM(adoptions.fee) as total_fees
FROM customers
JOIN adoptions
    ON customers.id = adoptions.customer_id
GROUP BY customers.city
HAVING COUNT(adoptions.id) > 2
ORDER BY adoption_count DESC;

-- Multiple JOINs: Complex relationships
-- Find all customers who adopted cats of a specific breed in their city
SELECT DISTINCT
    customers.name,
    customers.city,
    cats.breed
FROM customers
JOIN adoptions
    ON customers.id = adoptions.customer_id
JOIN cats
    ON adoptions.cat_id = cats.id
WHERE cats.breed = 'Siamese'
    AND customers.city = cats.city;  -- Adopted in their city

---

The Cat Analogy

Think of JOINs like a cat shelter's record system:

Without JOINs (flat file):

Cat Name | Breed   | Owner Name | Owner Phone | Adoption Fee | Owner Address
---------|---------|------------|-------------|--------------|---------------
Whiskers | Siamese | Alice      | 555-0101    | 200          | 123 Cat Lane
Whiskers | Siamese | Alice      | 555-0101    | 200          | 123 Cat Lane
Whiskers | Siamese | Alice      | 555-0101    | 200          | 123 Cat Lane

Same info repeated everywhere. If Alice moves, update everywhere!

With JOINs (normalized):

cats table:        | customers table: | adoptions table:
Whiskers|Siamese   | Alice|555-0101  | Whiskers|Alice|200

You can connect them when needed:

SELECT cats.name, customers.owner_name
FROM cats
JOIN adoptions ON cats.name = adoptions.cat_name
JOIN customers ON adoptions.owner_name = customers.name;

The cat record exists once. The owner exists once. The adoption connects them.

---

Common Pitfalls

Accidental CROSS JOIN (Cartesian Product)

-- WRONG: No JOIN condition = every combination
SELECT customers.name, cats.name
FROM customers, cats;  -- 100 customers × 20 cats = 2000 rows!

-- RIGHT: Always specify the relationship
SELECT customers.name, cats.name
FROM customers
JOIN adoptions ON customers.id = adoptions.customer_id
JOIN cats ON adoptions.cat_id = cats.id;

Missing JOIN Condition

-- This creates a Cartesian product!
SELECT
    customers.name,
    orders.total_amount
FROM customers, orders
WHERE customers.city = 'Brooklyn';  -- Still missing ON clause!

-- Always use proper ON:
SELECT
    customers.name,
    orders.total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE customers.city = 'Brooklyn';

Wrong JOIN Type

-- INNER JOIN excludes customers with no orders
SELECT DISTINCT
    customers.name,
    customers.email
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
-- Customers with NO orders are excluded!

-- LEFT JOIN includes all customers
SELECT
    customers.name,
    customers.email,
    orders.total_amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
-- All customers appear, orders columns are NULL if no order exists

---

Exercises

Exercise 1

Write a query to find all customers and the total they've spent (including customers who never ordered, showing 0).

Exercise 2

Explain why this query returns too many rows:

SELECT customers.name, orders.id
FROM customers, orders
WHERE customers.city = 'Brooklyn';

Exercise 3

Write a query showing each cat, how many times it's been ordered, and total revenue from that cat.

Exercise 4

What type of JOIN would you use to:

1. Find all products and their categories?
2. Find only customers who have placed orders?
3. Find all cats and their adopters (if any)?

Exercise 5

Write a query to find customers who adopted cats but have no orders in the orders table.

---

Key Takeaways

• JOINs connect data across normalized tables, avoiding redundancy
• Always specify the ON condition to avoid Cartesian products
• INNER JOIN excludes non-matching rows; LEFT JOIN keeps all left rows
• JOIN order matters for readability (join tables in logical sequence)
• One-to-Many is most common; Many-to-Many requires a junction table
• Missing JOIN conditions are a common source of incorrect results