Joining Tables with JOIN

One of the most powerful SQL features is the ability to combine data from multiple tables. JOINs are how relational databases truly shine.

Why Join Tables?

Rather than storing everything in one giant table, relational databases split data across multiple tables to avoid repetition. For example:

• users table: user info (name, email)
• orders table: order info (user_id, product_id, quantity)
• products table: product info (name, price)

To answer "who ordered what?", we need to join these tables.

INNER JOIN (the most common)

Returns only rows that have matching values in both tables:

SELECT
  users.name,
  users.email,
  orders.quantity,
  orders.order_date
FROM orders
INNER JOIN users ON orders.user_id = users.id;

Result:

name           | email               | quantity | order_date
---------------+---------------------+----------+------------
Alice Johnson  | alice@example.com   | 1        | 2024-01-20
Alice Johnson  | alice@example.com   | 2        | 2024-01-20
Bob Smith      | bob@example.com     | 1        | 2024-01-21
...

Using Aliases for Readability

Table names get long. Use aliases:

SELECT
  u.name,
  u.email,
  o.quantity
FROM orders o
JOIN users u ON o.user_id = u.id;

JOIN without a type is the same as INNER JOIN.

Three-Table JOIN

SELECT
  u.name AS customer,
  p.name AS product,
  p.price,
  o.quantity,
  (p.price * o.quantity) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
ORDER BY u.name;

Result:

customer       | product              | price   | quantity | total
---------------+----------------------+---------+----------+--------
Alice Johnson  | Laptop Pro 15"       | 1299.99 | 1        | 1299.99
Alice Johnson  | Mechanical Keyboard  | 89.99   | 2        | 179.98
Alice Johnson  | Notebook (paper)     | 4.99    | 5        | 24.95
Bob Smith      | USB-C Hub            | 39.99   | 1        | 39.99
...

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right table. Unmatched rows from the right table get NULL:

-- All users, even those with no orders
SELECT
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

If a user has no orders, COUNT(o.id) returns 0 (not null, because COUNT ignores NULLs).

RIGHT JOIN

Returns all rows from the right table. Less common — usually rewritten as a LEFT JOIN by swapping table order.

FULL OUTER JOIN