Joins and relationships_

Relational databases keep each entity in its own table and connect them through keys. A join combines rows from two tables by matching values, usually a foreign key on one side against a primary key on the other. This page covers how to model the two relationship shapes you'll meet constantly, and the join types you'll use in practice.

Setup

The examples below use two tables, customers and orders, where every order records which customer placed it. Create and seed them first:

CREATE TABLE customers (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    total       numeric(10, 2) NOT NULL
);

INSERT INTO customers (name) VALUES ('Ada'), ('Grace'), ('Linus');
INSERT INTO orders (customer_id, total) VALUES
    (1, 120.00), (1, 80.00), (2, 45.50);

Ada has two orders, Grace has one, and Linus has none.

What a join actually does

A join takes two tables and produces a new, temporary table built from them in three steps:

1. Pair rows from one side with rows from the other.
2. Evaluate the join condition on each pair, here o.customer_id = c.id.
3. Keep the pairs where it's true; each one becomes a result row carrying the columns of both sides.

customers               orders                        result of the join
id | name               id | customer_id | total     name  | total
 1 | Ada        ←──┬──   1 |           1 | 120.00    Ada   | 120.00
                   └──   2 |           1 |  80.00    Ada   |  80.00
 2 | Grace      ←─────   3 |           2 |  45.50    Grace |  45.50
 3 | Linus      (no order matches)

Two things fall out of this model:

• A row that matches several rows on the other side appears several times in the result: Ada shows up twice because two orders point at her.
• A row that matches nothing, like Linus, produces no pair at all, and what happens to such unmatched rows is exactly what distinguishes the join types below.

The database doesn't literally build every combination; indexes let it jump straight to matching rows, but the result is always as if it had.

One-to-many relationships

One customer has many orders, so orders carries a customer_id column referencing customers. The REFERENCES clause creates a foreign key: the database rejects any customer_id that doesn't exist in customers:

INSERT INTO orders (customer_id, total) VALUES (99, 10.00);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".

A foreign key also decides what happens when the parent row is deleted. Declare it with the behavior you want:

• ON DELETE NO ACTION (the default) blocks deleting a customer who still has orders.
• ON DELETE CASCADE deletes the customer's orders along with the customer.
• ON DELETE SET NULL keeps the orders but clears their customer_id (the column must be nullable).

Inner joins

An inner join returns only rows that match on both sides:

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.total;

 name  | total
-------+--------
 Ada   |  80.00
 Ada   | 120.00
 Grace |  45.50

Linus disappears from the result because he has no matching order. JOIN and INNER JOIN are the same thing.

Left joins

A left join keeps every row from the left table and fills the right side with NULL where nothing matches:

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

 name  | total
-------+--------
 Ada   |  80.00
 Ada   | 120.00
 Grace |  45.50
 Linus |

This is the join for "all X, with their Y if any". Combined with aggregation it answers questions like order counts per customer, including zero:

SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY orders DESC;

Note count(o.id), not count(*): counting a column from the right table skips the NULL produced for Linus, yielding 0 instead of 1.

RIGHT JOIN mirrors LEFT JOIN in the other direction. In practice, most people reorder the tables and use LEFT JOIN for consistency.

Full joins

A full join keeps unmatched rows from both sides. It's useful for reconciliation, such as comparing two datasets that should agree:

CREATE TABLE imported_customers (id bigint, name text);
INSERT INTO imported_customers VALUES (1, 'Ada'), (4, 'Margaret');

SELECT c.name AS in_database, i.name AS in_import
FROM customers c
FULL JOIN imported_customers i ON i.id = c.id
ORDER BY c.name;

 in_database | in_import
-------------+-----------
 Ada         | Ada
 Grace       |
 Linus       |
             | Margaret

Cross joins

A cross join produces every combination of rows from both tables, with no matching condition. Use it to generate combinations, such as every product in every size:

SELECT c.name, s.size
FROM customers c
CROSS JOIN (VALUES ('S'), ('M'), ('L')) AS s (size)
ORDER BY c.name, s.size;

Three customers times three sizes returns nine rows. Be careful with large tables: the result size is the product of both row counts.

Anti-joins: rows without a match

"Customers with no orders" is an anti-join. Two equivalent spellings:

SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Both return only Linus. Prefer NOT EXISTS over NOT IN (SELECT ...): if the subquery ever returns a NULL, NOT IN returns no rows at all, and nothing reports an error.

Many-to-many relationships

When both sides can have many of the other, such as orders and products, neither table can hold the foreign key. A junction table holds one row per connection:

CREATE TABLE products (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id),
    product_id bigint NOT NULL REFERENCES products (id),
    quantity   integer NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO products (name) VALUES ('Keyboard'), ('Mouse');
INSERT INTO order_items (order_id, product_id, quantity) VALUES
    (1, 1, 1), (1, 2, 2), (2, 2, 1);

The composite primary key (order_id, product_id) prevents duplicate connections, and the junction table is the natural home for relationship attributes like quantity. Traversing the relationship is two joins:

SELECT o.id AS order_id, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.name;

 order_id |   name   | quantity
----------+----------+----------
        1 | Keyboard |        1
        1 | Mouse    |        2
        2 | Mouse    |        1

Each result row is one line item: the first join finds the connections belonging to each order, and the second swaps each product_id for the product's actual name. The Mouse appears under both orders because two junction rows point at it, one connection per row.

Join or separate queries?

Fetching a list and then querying once per row (the N+1 pattern) multiplies round trips and usually loses badly to a single join, even a large one. Let the database combine the data; that is what it is optimized for. If a join's result set explodes because one row matches thousands, paginate or aggregate on the many side instead of splitting into per-row queries.