🔗 Joins and Relationships

Why Split Data Across Tables?

Imagine storing a customer's name, email, and phone on every single order they place. A customer with 200 orders would have their email duplicated 200 times — and changing that email would mean updating 200 rows, any of which could be missed. This duplication is exactly what relational design avoids through normalization: each fact is stored once, in the table it belongs to, and other tables refer to it by a key.

💡 Analogy — a department store: A store doesn't pile electronics, clothing, and groceries into one heap. Each department is organized for its own goods (its own table), and a directory helps you move between them (foreign keys). Joins are you walking the directory to gather items from several departments into one basket.

The Three Relationship Types

Nearly every schema is built from three patterns:

• One-to-One (1:1): each row on one side matches exactly one on the other. Example: a user and their profile. Often used to split rarely-accessed or sensitive columns into their own table.
• One-to-Many (1:N): one row relates to many rows on the other side, but each of those points back to just one. Example: a customer has many orders; each order has one customer. This is the most common relationship. The FK lives on the "many" side.
• Many-to-Many (N:M): rows on both sides can relate to many on the other. Example: students and courses. SQL can't store this directly, so you add a junction table (here enrollment) holding a FK to each side.

INNER JOIN

An INNER JOIN returns only rows that have a match in both tables. It's the join you'll reach for most.

SELECT columns
FROM   table1
INNER JOIN table2 ON table1.key = table2.key;

We'll use this tiny e-commerce dataset throughout. Note that Alice (id 4) has no orders:

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

Alice has no matching order, so INNER JOIN drops her entirely. That's the defining behavior: no match on either side means the row disappears.

LEFT JOIN (and RIGHT)

A LEFT JOIN (a.k.a. LEFT OUTER JOIN) keeps every row from the left table, matching rows from the right where possible and filling in NULL where there's no match.

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

The classic LEFT JOIN trick: find the orphans

Combine a LEFT JOIN with WHERE right_key IS NULL to find rows on the left that have no match — here, customers who have never ordered:

SELECT c.customer_id, c.name
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE  o.order_id IS NULL;      -- keeps only the unmatched (Alice)

RIGHT JOIN

A RIGHT JOIN is the mirror image: it keeps every row from the right table. In practice it's rarely used — most developers just swap the table order and write a LEFT JOIN, which reads more naturally. These two are equivalent:

-- Every order, with customer info if available
SELECT o.order_id, c.name
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- Same result, written as a LEFT JOIN
SELECT o.order_id, c.name
FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id;

FULL OUTER, CROSS & SELF

FULL OUTER JOIN

Returns all rows from both tables, matching where possible and using NULL on whichever side lacks a match. Useful for reconciliation — spotting rows that exist on one side but not the other.

SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

SELECT c.customer_id, c.name, o.order_id
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN

Produces the Cartesian product — every row of A paired with every row of B, with no matching condition. If A has M rows and B has N, you get M × N rows.

-- Generate every product/size combination (2 products × 3 sizes = 6 rows)
SELECT p.product_name, s.size_name
FROM products p
CROSS JOIN sizes s
ORDER BY p.product_name, s.size_name;

SELF JOIN

A SELF JOIN isn't a new join type — it's joining a table to itself using two aliases. It's how you traverse hierarchies stored in one table, like an employee/manager chain:

SELECT e.name AS employee, e.title,
       m.name AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Here e is "the employee" and m is "the employee acting as manager." A LEFT JOIN ensures the CEO — who has no manager — still appears, with NULL for the manager column.

Joining Multiple Tables

Real reports chain several joins. To list what each customer bought, you walk customers → orders → order_details → products:

SELECT c.name AS customer,
       o.order_id,
       p.product_name,
       od.quantity,
       od.unit_price,
       (od.quantity * od.unit_price) AS line_total
FROM   customers c
INNER JOIN orders        o  ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id    = od.order_id
INNER JOIN products      p  ON od.product_id = p.product_id
ORDER BY o.order_id, p.product_name;

Duplicate & Missing Rows

Problem 1 — unexpected duplicates

When one row on the "one" side matches many on the "many" side, its data repeats once per match. A customer with 3 orders shows up 3 times. That's correct behavior, but if you only wanted a per-customer summary it looks like duplication. The fix is to aggregate with GROUP BY:

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS order_count,
       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
ORDER BY total_spent DESC;

Using LEFT JOIN plus COALESCE means Alice still appears, with a count and total of 0 rather than being dropped.

Problem 2 — unexpectedly missing rows

If a query silently loses rows you expected, the usual culprit is an INNER JOIN where you needed to preserve unmatched rows. Switch the relevant join to LEFT JOIN.

-- Keeps all customers; only "large" orders are joined in
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
       ON c.customer_id = o.customer_id
      AND o.amount > 100;

Hands-on Exercise

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(80) NOT NULL
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    amount      DECIMAL(8,2) NOT NULL
);

INSERT INTO customers VALUES (1,'John'),(2,'Jane'),(3,'Bob'),(4,'Alice');
INSERT INTO orders VALUES
    (101,1,150.50),(102,1,89.99),(103,2,49.95),(104,3,75.00);

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

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

-- 3
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;              -- returns Alice

-- 4
SELECT c.name,
       COUNT(o.order_id) AS order_count,
       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
ORDER BY total_spent DESC;

Best Practices

Summary & Quiz

📚 Further Reading

• PostgreSQL — Joins Between Tables
• MySQL — JOIN Syntax
• Visual SQL JOIN reference

🚀 What's Next?

You can now query across a whole schema. Next you'll get hands-on with a specific engine in MySQL Setup and Administration — installing, configuring, and managing a real MySQL server.