Joins & Relationships

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 │
└──────────┴─────────────┴────────────┴────────┘

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 │
└───────┴────────────┴────────┘

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 │
│ Bob     │      102 │ 180.50 │
│ Alice   │      103 │  99.99 │
│ Charlie │      104 │ 350.00 │
│ Diana   │          │        │
└─────────┴──────────┴────────┘

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 │
└───────┘

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 │
│ Bob     │      102 │ 180.50 │
│ Alice   │      103 │  99.99 │
│ Charlie │      104 │ 350.00 │
│ Diana   │          │        │
└─────────┴──────────┴────────┘

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 │
└──────────┴─────────┴─────┴────────┘

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    │         │
│ 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 │
└──────┴───────┘

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

Common JOIN Mistakes

-- WRONG: PostgreSQL rejects this outright
SELECT * FROM orders
INNER JOIN customers;
-- ERROR:  syntax error at or near ";"

-- ALSO WRONG, and far more dangerous: this one runs.
-- The comma is the old pre-1992 join syntax, so with no WHERE
-- you silently get every order paired with every customer.
SELECT * FROM orders, customers;   -- 4 orders x 4 customers = 16 rows

-- CORRECT: Specify how to join
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- 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: 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

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 │
└─────────┴───────────────────┴──────────────┴─────────────┘

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