Chapter 13: Joining Tables

Relational data lives across multiple tables; joins let you combine rows that share related keys. This chapter shows the core join types, how to read and write them, and how to avoid common mistakes when matching data across tables.

INNER, LEFT, RIGHT, FULL, CROSS joins

A join combines rows from two tables based on a match condition (except for a true Cartesian product). The typical pattern is:

SELECT {column_list …}
FROM table1
[INNER] JOIN table2
  ON table1.key = table2.key;

INNER JOIN: matches in both tables

Returns only rows where the join condition is true in both tables.

SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.customer_id;

If a customer has no orders, or an order references a missing customer, that pair does not appear in the result.

LEFT JOIN and RIGHT JOIN: preserving one side

A LEFT JOIN returns all rows from the left table and fills unmatched columns from the right with NULL. A RIGHT JOIN is symmetric for the right table.

-- All customers; show matching orders if any
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

-- All products; show suppliers if any
SELECT p.product_id, p.product_name, s.supplier_name
FROM products AS p
RIGHT JOIN suppliers AS s
  ON p.supplier_id = s.supplier_id;  -- Example; prefer LEFT with swapped sides

FULL OUTER JOIN: union of LEFT and RIGHT

Returns every row from both tables; rows that do not match on either side appear with NULLs for the other side.

SELECT a.id, a.name, b.name AS b_name
FROM table_a AS a
FULL OUTER JOIN table_b AS b
  ON a.id = b.id;

SELECT a.id, a.name, b.name AS b_name
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.id
UNION
SELECT a.id, a.name, b.name AS b_name
FROM table_a AS a
RIGHT JOIN table_b AS b ON a.id = b.id;

CROSS JOIN (Cartesian product)

Produces every combination of rows from both tables. Use carefully; it scales as rows(table1) × rows(table2).

-- Equivalent forms in many engines
SELECT c.color, s.size
FROM colors AS c
CROSS JOIN sizes AS s;

SELECT c.color, s.size
FROM colors AS c, sizes AS s;  -- Avoid this legacy comma form in new code

ON vs WHERE with outer joins

With outer joins, filters in WHERE can turn an outer join into an inner join by removing the NULL-extended rows. Put row-matching logic in ON, and post-join filtering in WHERE only when you intend to filter both matched and unmatched rows.

-- Keep customers even when they have no orders
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
  AND o.status = 'OPEN'   -- filter applies to right side only
WHERE c.country = 'UK';   -- filter applies to preserved left rows as well

Self-joins and aliasing

A self-join relates a table to itself (for example hierarchical data, predecessor links, or de-duplicating near matches). Aliases keep the references readable.

Aliasing with AS (or implicit aliases)

SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

-- Most engines allow omitting AS (use sparingly for clarity):
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Classic self-join: employees and managers

-- Each employee row has manager_id pointing to another employee's employee_id
SELECT e.employee_id,
       e.employee_name,
       m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id;

The alias e stands for the employee row; m stands for the manager row in the same table.

Self-join for de-duplication and matching

-- Find pairs of customers with the same email (case-insensitive), different ids
SELECT c1.customer_id AS id1,
       c2.customer_id AS id2,
       LOWER(c1.email) AS email_normalized
FROM customers AS c1
JOIN customers AS c2
  ON LOWER(c1.email) = LOWER(c2.email)
 AND c1.customer_id < c2.customer_id;  -- prevent symmetric duplicates

Hierarchy traversal (adjacency list)

For a simple parent → child relationship stored as (id, parent_id), a self-join returns parent attributes alongside children.

SELECT child.category_id,
       child.category_name,
       parent.category_name AS parent_name
FROM categories AS child
LEFT JOIN categories AS parent
  ON parent.category_id = child.parent_id;

Practical join examples

This section uses a small sales schema:

Joining three or more tables

SELECT o.order_id,
       c.customer_name,
       p.product_name,
       oi.quantity,
       oi.unit_price
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
JOIN products AS p
  ON p.product_id = oi.product_id;

Add joins one step at a time; verify each join multiplies rows as expected.

Aggregations across joins

-- Revenue per customer
SELECT c.customer_id,
       c.customer_name,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
JOIN order_items AS oi
  ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.customer_name
ORDER BY revenue DESC;

Finding unmatched rows (LEFT JOIN with NULL check)

-- Customers with no orders
SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

This pattern (outer join plus IS NULL on the non-preserved side) is the standard anti-join approach.

Many-to-many bridges

Use a bridge (junction) table to connect two entities. Join through the bridge to reach the far table.

-- Authors <- book_authors -> Books
SELECT a.author_name, b.title
FROM authors AS a
JOIN book_authors AS ba
  ON ba.author_id = a.author_id
JOIN books AS b
  ON b.book_id = ba.book_id;

Filtering on the joined table safely

-- Customers with at least one OPEN order; preserve the "at least one" intent
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'OPEN';

If you need to keep customers with zero open orders as well, move the status predicate into ON and use LEFT JOIN.

USING and NATURAL JOIN

-- USING collapses duplicate column names in the output
SELECT o.order_id, customer_id
FROM orders AS o
JOIN customers AS c
USING (customer_id);

-- NATURAL JOIN matches columns with the same name automatically (avoid in production)
SELECT *
FROM orders NATURAL JOIN customers;

Join performance checklist

• Index foreign keys (orders.customer_id, order_items.order_id, order_items.product_id).
• Project only needed columns; wide selects slow I/O.
• Filter early where possible (push selective predicates into ON for outer joins or WHERE for inner joins).
• Watch cardinality multiplication; understand one-to-many and many-to-many expansion.

Dialect notes

With these patterns, you can confidently combine tables for analysis and application queries.