The Working Schema
All examples in this chapter use the following five-table e-commerce schema. Understanding its structure before writing any SQL pays dividends throughout.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
country TEXT,
joined_at DATE
);
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
category_id INT REFERENCES categories(category_id)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
placed_at TIMESTAMPTZ NOT NULL,
status TEXT -- 'pending', 'shipped', 'cancelled'
);
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT NOT NULL,
unit_price NUMERIC(10,2) NOT NULL
);The foreign-key graph is: order<em>items → orders → customers, and order</em>items → products → categories. This is a classic star-like normalized schema.
What a Join Actually Does
A join combines rows from two tables by evaluating a join predicate — a Boolean condition on the columns of both tables. The relational algebra operation is called a theta join; the overwhelmingly common special case where the predicate tests equality of a column from each table is the equi-join.
Conceptually, the database engine first forms the Cartesian product of both tables (every row paired with every row), then filters by the predicate. In practice, query optimizers avoid the full Cartesian product by using hash joins, merge joins, or nested-loop index seeks, but the logical result is identical.
The cardinality of a join result depends on how many rows from the left table match rows from the right table:
- A 1-to-1 join (primary key ↔ primary key) returns at most as many rows as the smaller table.
- A many-to-1 join (foreign key ↔ primary key) preserves the row count of the many side.
- A many-to-many join (no unique constraint on either join key) can multiply row counts and is a common source of unintended row duplication.
INNER JOIN — the Default
An INNER JOIN returns only rows where the predicate is satisfied on both sides. Rows with no match are silently discarded.
-- Every order paired with its customer's name
SELECT o.order_id,
c.name AS customer_name,
o.placed_at,
o.status
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.placed_at DESC
LIMIT 10;Because orders.customer<em>id is a NOT NULL foreign key to customers, every order has exactly one matching customer row, so no orders are dropped. If the foreign key allowed NULLs, orders with customer</em>id IS NULL would vanish from the result — a subtle bug that INNER JOIN silently introduces.
Alias convention. Table aliases (o, c) are essential in multi-table queries. Use short, memorable aliases that reflect the table name. Always qualify ambiguous column names.
LEFT / RIGHT / FULL OUTER JOIN — Preserving Unmatched Rows
Outer joins extend the inner join result by preserving rows from one or both tables even when no match exists, padding the missing side with NULLs.
-- All customers, with their order count (0 for customers who never ordered)
SELECT c.customer_id,
c.name,
COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;Here the LEFT JOIN ensures every customer appears in the result. Customers with no orders will have o.order<em>id = NULL, so COUNT(o.order</em>id) returns 0 (COUNT ignores NULLs). Using COUNT(<em>) instead would incorrectly return 1 for those customers.
A RIGHT JOIN is the mirror image — all rows from the right table are preserved. In practice, most practitioners convert RIGHT JOINs to LEFT JOINs by swapping the table order, improving readability.
A FULL OUTER JOIN preserves unmatched rows from both* sides. It is useful for reconciliation queries — finding rows present in one source but missing from another:
-- Reconcile two inventory snapshots: find products in either but not both
SELECT COALESCE(a.product_id, b.product_id) AS product_id,
a.quantity AS qty_snapshot_a,
b.quantity AS qty_snapshot_b
FROM inventory_snapshot_a a
FULL OUTER JOIN inventory_snapshot_b b
ON a.product_id = b.product_id
WHERE a.product_id IS NULL OR b.product_id IS NULL;CROSS JOIN — Generating All Combinations
A CROSS JOIN produces the full Cartesian product with no join predicate. Its output has $|A| \times |B|$ rows. Practical uses include generating test data, date-dimension spine tables, or all (customer, product) pairs for recommendation scoring:
-- Every customer paired with every product (for a recommendation scaffold)
SELECT c.customer_id, p.product_id
FROM customers c
CROSS JOIN products p;Warn yourself before running CROSS JOINs on large tables — 10 000 customers × 50 000 products yields 500 million rows.
Self Join — Relating a Table to Itself
A self join joins a table to itself using two aliases. The canonical example is an employee-manager hierarchy, but in our schema we can find customers in the same country:
-- Pairs of customers who share the same country (excluding self-pairs)
SELECT a.name AS customer_a,
b.name AS customer_b,
a.country
FROM customers a
JOIN customers b
ON a.country = b.country
AND a.customer_id < b.customer_id; -- avoid (A,B) and (B,A) duplicatesThe a.customer<em>id < b.customer</em>id trick is idiomatic for generating unordered pairs without duplicates.