Joins & Relationships

Connect data across multiple tables

The Power of Relational Databases

The "relational" in relational databases means tables can relate to each other. Instead of storing everything in one massive table, we split data into logical pieces and connect them using JOINs. This prevents data duplication, maintains consistency, and makes databases scalable. Mastering joins is what separates basic SQL users from those who can build real applications.

Our Sample Tables

We'll use these two related tables throughout this lesson:

customers table:

┌─────────────┬────────┬──────────────────┐
│ customer_id │ name   │ email            │
├─────────────┼────────┼──────────────────┤
│ 1           │ Alice  │ alice@email.com  │
│ 2           │ Bob    │ bob@email.com    │
│ 3           │ Charlie│ charlie@email.com│
│ 4           │ Diana  │ diana@email.com  │
└─────────────┴────────┴──────────────────┘

orders table:

┌──────────┬─────────────┬────────────┬────────┐
│ order_id │ customer_id │ order_date │ amount │
├──────────┼─────────────┼────────────┼────────┤
│ 101      │ 1           │ 2024-01-15 │ 250.00 │
│ 102      │ 2           │ 2024-01-16 │ 180.50 │
│ 103      │ 1           │ 2024-01-20 │ 99.99  │
│ 104      │ 3           │ 2024-01-22 │ 350.00 │
└──────────┴─────────────┴────────────┴────────┘
The Connection: customer_id in the orders table is a foreign key that references customer_id in the customers table. This links each order to a customer.

INNER JOIN: Matching Records Only

INNER JOIN returns only rows where there's a match in both tables. This is the most common type of join.

Basic INNER JOIN

Show orders with customer information.

SELECT 
    orders.order_id,
    customers.name,
    orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
┌──────────┬─────────┬────────┐
│ order_id │ name    │ amount │
├──────────┼─────────┼────────┤
│ 101      │ Alice   │ 250.00 │
│ 102      │ Bob     │ 180.50 │
│ 103      │ Alice   │ 99.99  │
│ 104      │ Charlie │ 350.00 │
└──────────┴─────────┴────────┘

Using Table Aliases

Shorten table names for cleaner queries.

SELECT 
    o.order_id,
    c.name,
    o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Same result as above, but with shorter alias names (o for orders, c for customers)

JOIN with WHERE

Find Alice's orders only.

SELECT 
    c.name,
    o.order_date,
    o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.name = 'Alice';
┌───────┬────────────┬────────┐
│ name  │ order_date │ amount │
├───────┼────────────┼────────┤
│ Alice │ 2024-01-15 │ 250.00 │
│ Alice │ 2024-01-20 │ 99.99  │
└───────┴────────────┴────────┘
How INNER JOIN Works

It matches rows where customer_id exists in BOTH tables. Diana (customer_id=4) has no orders, so she doesn't appear in the results.

LEFT JOIN: Keep All Left Table Rows

LEFT JOIN returns all rows from the left table, even if there's no match in the right table. Missing data shows as NULL.

Basic LEFT JOIN

Show all customers and their orders (if any).

SELECT 
    c.name,
    o.order_id,
    o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
┌─────────┬──────────┬────────┐
│ name    │ order_id │ amount │
├─────────┼──────────┼────────┤
│ Alice   │ 101      │ 250.00 │
│ Alice   │ 103      │ 99.99  │
│ Bob     │ 102      │ 180.50 │
│ Charlie │ 104      │ 350.00 │
│ Diana   │ NULL     │ NULL   │ ← No orders
└─────────┴──────────┴────────┘

Find Customers Without Orders

Use LEFT JOIN + NULL check to find unmatched rows.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
┌───────┐
│ name  │
├───────┤
│ Diana │ ← Has no orders
└───────┘
Common Use Case: LEFT JOIN is perfect for "show me everything from table A, plus related data from table B if it exists." Example: all customers with their order count (even if count is zero).

RIGHT JOIN: Keep All Right Table Rows

RIGHT JOIN is the opposite of LEFT JOIN, it keeps all rows from the right table.

SELECT 
    c.name,
    o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
┌─────────┬──────────┐
│ name    │ order_id │
├─────────┼──────────┤
│ Alice   │ 101      │
│ Bob     │ 102      │
│ Alice   │ 103      │
│ Charlie │ 104      │
└─────────┴──────────┘

Note: RIGHT JOIN is rarely used. Most developers just flip the table order and use LEFT JOIN instead.

FULL OUTER JOIN: Keep Everything

Returns all rows from both tables, with NULL where there's no match.

SELECT 
    c.name,
    o.order_id,
    o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
┌─────────┬──────────┬────────┐
│ name    │ order_id │ amount │
├─────────┼──────────┼────────┤
│ Alice   │ 101      │ 250.00 │
│ Alice   │ 103      │ 99.99  │
│ Bob     │ 102      │ 180.50 │
│ Charlie │ 104      │ 350.00 │
│ Diana   │ NULL     │ NULL   │ ← Customer without orders
└─────────┴──────────┴────────┘

Note: Not all databases support FULL OUTER JOIN (MySQL doesn't, PostgreSQL does)

Multiple JOINs: Connecting 3+ Tables

Real applications often need to join many tables together.

Adding a products table:

order_items:
┌──────────┬────────────┬────────┐
│ order_id │ product_id │ qty    │
├──────────┼────────────┼────────┤
│ 101      │ 1          │ 2      │
│ 102      │ 2          │ 1      │
│ 103      │ 1          │ 1      │
└──────────┴────────────┴────────┘

products:
┌────────────┬────────────┬────────┐
│ product_id │ name       │ price  │
├────────────┼────────────┼────────┤
│ 1          │ Laptop     │ 999.99 │
│ 2          │ Mouse      │ 29.99  │
└────────────┴────────────┴────────┘

Join Three Tables

Show order details with customer and product info.

SELECT 
    c.name AS customer,
    p.name AS product,
    oi.qty,
    p.price
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id;
┌──────────┬────────┬─────┬────────┐
│ customer │ product│ qty │ price  │
├──────────┼────────┼─────┼────────┤
│ Alice    │ Laptop │ 2   │ 999.99 │
│ Bob      │ Mouse  │ 1   │ 29.99  │
│ Alice    │ Laptop │ 1   │ 999.99 │
└──────────┴────────┴─────┴────────┘
Join Chain

order_items → orders → customers (via customer_id)
order_items → products (via product_id)

Self JOIN: Table Joining Itself

Sometimes you need to join a table to itself, like finding employee-manager relationships.

employees table with manager_id:

┌────┬────────┬────────────┐
│ id │ name   │ manager_id │
├────┼────────┼────────────┤
│ 1  │ Alice  │ NULL       │ (CEO, no manager)
│ 2  │ Bob    │ 1          │ (reports to Alice)
│ 3  │ Charlie│ 1          │ (reports to Alice)
│ 4  │ Diana  │ 2          │ (reports to Bob)
└────┴────────┴────────────┘

Find Employee and Their Manager

SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
┌──────────┬─────────┐
│ employee │ manager │
├──────────┼─────────┤
│ Alice    │ NULL    │ (no manager)
│ Bob      │ Alice   │
│ Charlie  │ Alice   │
│ Diana    │ Bob     │
└──────────┴─────────┘

CROSS JOIN: Every Combination

Creates every possible combination of rows from both tables (Cartesian product).

Example tables:

sizes:              colors:
┌──────┐           ┌────────┐
│ size │           │ color  │
├──────┤           ├────────┤
│ S    │           │ Red    │
│ M    │           │ Blue   │
│ L    │           │ Green  │
└──────┘           └────────┘

All Size-Color Combinations

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
┌──────┬────────┐
│ size │ color  │
├──────┼────────┤
│ S    │ Red    │
│ S    │ Blue   │
│ S    │ Green  │
│ M    │ Red    │
│ M    │ Blue   │
│ M    │ Green  │
│ L    │ Red    │
│ L    │ Blue   │
│ L    │ Green  │ ← 3 × 3 = 9 rows
└──────┴────────┘
Use Sparingly: CROSS JOIN can create huge result sets (1000 rows × 1000 rows = 1,000,000 rows). Use only when you genuinely need all combinations.

JOINs vs Subqueries

Sometimes you can solve a problem with either a JOIN or a subquery.

Using JOIN

SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Using Subquery

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

Both return customers who have placed orders

When to Use Each
  • JOINs: Better for retrieving columns from multiple tables
  • Subqueries: Better for filtering based on aggregate results
  • Performance: Usually similar, but JOINs often faster

Common JOIN Mistakes

❌ Forgetting the ON Clause
-- WRONG: Creates accidental CROSS JOIN
SELECT * FROM orders
INNER JOIN customers;

-- CORRECT: Specify how to join
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
❌ Ambiguous Column Names
-- WRONG: Which table's customer_id?
SELECT customer_id FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

-- CORRECT: Always prefix with table name or alias
SELECT o.customer_id FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
❌ Wrong JOIN Type
-- WRONG: INNER JOIN excludes customers without orders
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

-- CORRECT: LEFT JOIN includes all customers
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

JOIN Performance Tips

✅ Index Foreign Keys

Always create indexes on columns used in JOIN conditions. Joining on unindexed columns is extremely slow on large tables.

✅ Join Order Matters

Start with the smallest table when joining multiple tables. Modern databases optimize this automatically, but it's still good practice.

✅ Select Only What You Need

Don't SELECT * when joining. Specify only the columns you actually need to reduce data transfer and memory usage.

✅ Filter Early

Apply WHERE conditions before joining when possible to reduce the dataset being joined.

Practical Example: E-commerce Report

Generate a customer order summary with total spent.

SELECT 
    c.name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY total_spent DESC;
┌─────────┬──────────────────┬──────────────┬─────────────┐
│ name    │ email            │ total_orders │ total_spent │
├─────────┼──────────────────┼──────────────┼─────────────┤
│ Charlie │ charlie@email.com│ 1            │ 350.00      │
│ Alice   │ alice@email.com  │ 2            │ 349.99      │
│ Bob     │ bob@email.com    │ 1            │ 180.50      │
│ Diana   │ diana@email.com  │ 0            │ 0.00        │
└─────────┴──────────────────┴──────────────┴─────────────┘

Note: LEFT JOIN ensures Diana appears even with no orders. COALESCE converts NULL to 0.

Key Takeaways

  • INNER JOIN returns only matching rows from both tables
  • LEFT JOIN keeps all left table rows, adds NULL for missing matches
  • RIGHT JOIN keeps all right table rows (rarely used)
  • FULL OUTER JOIN keeps all rows from both tables
  • Multiple JOINs let you connect 3+ tables in one query
  • Self JOIN joins a table to itself (for hierarchies)
  • Always specify ON conditions to avoid accidental CROSS JOIN
  • Index foreign keys for fast JOIN performance
  • JOINs are the heart of relational databases, mastering them lets you model complex real-world relationships
Database FundamentalsLesson 5