PostgreSQL / Basic: relational foundations / Lesson 4

Joins and relational cardinality

Combine customers, bookings and payments without losing optional relationships or multiplying business measures. Predict each intermediate row count before aggregating.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain inner and outer joins from matching rules.
Avoid fan-out errors when combining several child relations.

Name the grain of every relation

A customer row represents one customer, while a booking row represents one booking. Joining one customer to three bookings creates three joined rows, repeating customer attributes. This is correct for booking detail and dangerous for a customer-level balance that is then summed. Start with the report’s intended grain and identify every one-to-many relationship. A declared foreign key verifies parent existence, not that a join will return only one child or that monetary measures are safe to aggregate.

Preserve a customer without bookings

The complete VALUES fixture has two customers and one booking for customer 1. LEFT JOIN preserves customer 2 by supplying NULL booking columns. The expected result is Lina with booking 11 and Omar with NULL. INNER JOIN would remove Omar. A left join does not automatically mean every later filter preserves that customer: a WHERE test on a child column can reject the NULL-extended row. The placement of optional conditions is therefore part of the business question.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT c.customer_id, c.name, b.booking_id
FROM (VALUES (1, 'Lina'), (2, 'Omar')) AS c(customer_id, name)
LEFT JOIN (VALUES (11, 1)) AS b(booking_id, customer_id)
 ON b.customer_id = c.customer_id
ORDER BY c.customer_id;

Expected output

customer_id | name | booking_id
1           | Lina | 11
2           | Omar | NULL

NULL is the absence of a matching booking, not a booking identifier of zero.

Place optional-child conditions in ON

To report all customers with only paid bookings attached, put the paid-state condition beside the key equality in ON. Putting it in WHERE instead removes customers whose joined child is NULL or not paid. Mandatory customer-scope predicates can remain in WHERE because they intentionally choose the report population. Use explicit column qualification when both sides have names such as id or state. USING can simplify identical join keys, but understand its merged output columns before relying on SELECT *.

Use EXISTS when only presence matters

A report that asks which customers have any paid booking needs presence, not repeated booking detail. EXISTS directly states that requirement and avoids manufacturing duplicates just to remove them with DISTINCT. The synthetic fixture below returns customer 1 once although that customer has two bookings. The correlated predicate must connect the right keys; omitting it asks whether any booking exists anywhere. This distinction is especially important when tenant or organization scope is also part of the relationship.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT c.customer_id
FROM (VALUES (1), (2)) AS c(customer_id)
WHERE EXISTS (
 SELECT 1 FROM (VALUES (11, 1), (12, 1)) AS b(booking_id, customer_id)
 WHERE b.customer_id = c.customer_id
) ORDER BY c.customer_id;

Expected output

customer_id
1

EXISTS returns a truth condition, so the parent is not repeated for every matching child.

Preaggregate independently before a report join

Suppose one booking has two line items and three payments. Joining both child sets directly gives six combinations, repeating each line three times and each payment twice. Aggregate lines and payments separately to one row per booking before joining their summaries. DISTINCT on amount cannot repair this because equal amounts may be legitimate different events. Verify the report with an empty child set and repeated monetary values. PostgreSQL’s optimizer can choose access paths, but it cannot infer an unstated accounting grain or correct an ambiguous business definition.

Common errors and fixes

column reference is ambiguous (42702)

Why it happens: A shared column name was used without a table alias.
How to fix it: Qualify the intended side, for example c.customer_id.
Prevent it next time: Use consistent aliases and explicit projections in joins.

Optional customers disappear from the report

Why it happens: WHERE rejected NULL-extended child rows.
How to fix it: Move the optional child filter into ON when that matches the question.
Prevent it next time: Include an unmatched parent in outer-join fixtures.

Totals grow after another child table is joined

Why it happens: Two many-side relations multiplied each other.
How to fix it: Preaggregate both children to the common parent grain.
Prevent it next time: Document grain and predict small row counts before summing.

Check your understanding

1. A booking has line amounts 10 and 15 and payments 5, 8 and 12. Predict the correct totals and the incorrect direct-join totals.

Show worked solution

Correct totals are 25 for lines and 25 for payments. The six-row direct join repeats every line three times, producing 75, and every payment twice, producing 50. Group each child relation by booking_id first. Join those one-row summaries and define whether absent child totals should display as zero or remain unknown.

2. You need customers with no bookings. Describe a correct read-only query and its NULL assumption.