Joins and relationships

Data about one thing is spread over several tables, so most questions need a join: rows of two tables combined where a condition holds, usually a foreign key matching a primary key.

Relationships

• One to many (1:N): a customer has many policies, each policy has one customer. The foreign key sits on the 'many' side: policies.customer_id.
• Many to many (N:M): a policy has many coverages, and a coverage type appears on many policies. It needs a table in between, policy_coverages, with a foreign key to each side.
• One to one: rare; usually one table is enough.

Kinds of join

SELECT b.name AS broker, p.policy_number
FROM brokers b
LEFT JOIN policies p ON p.broker_id = b.broker_id
ORDER BY b.broker_id, p.policy_id;

USING (policy_id) is a short form of ON a.policy_id = b.policy_id when the column has the same name in both tables.

Semi-joins and anti-joins

EXISTS (subquery) keeps rows that have at least one match, without repeating them (a semi-join); NOT EXISTS keeps rows with none (an anti-join).

SELECT COUNT(*) AS customers_who_claimed
FROM customers cu
WHERE EXISTS (
    SELECT 1
    FROM policies p
    JOIN claims c USING (policy_id)
    WHERE p.customer_id = cu.customer_id
);

The fan-out trap

Joining a row to many rows repeats it. Sum a policy's premium after joining it to its claims, and a policy with three claims counts its premium three times. Aggregate each table to the level you need first (in a subquery or CTE), then join the totals.

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT p.policy_id) AS policies
FROM policies p
JOIN claims c USING (policy_id);