Joins: SQL Analysis Chapter 2

Why this chapter matters

Key metrics usually require data spread across entities such as users, orders, plans, and events.

What you will learn

Choose INNER and LEFT joins based on whether unmatched rows must remain visible.
Write explicit ON clauses that protect row cardinality.
Diagnose duplicate rows caused by one-to-many joins.

Understand the core ideas

Joins combine facts that live in different tables, but the most important question is still row grain. Before writing SQL, state the intended output like one row per user or one row per invoice. In PostgreSQL, an INNER JOIN keeps only rows with matches on both sides, while a LEFT JOIN keeps every row from the left table and fills missing right side values with null. That behavior should be chosen based on business meaning, not habit. If you are building a subscriber list and must include users without invoices yet, start from users and LEFT JOIN invoices. If you need only paid activity, an inner join may be right. Place join conditions in ON and business filters in WHERE so each decision remains readable. This separation also makes debugging easier when totals look wrong.

Duplicate inflation usually comes from one to many relationships. A user can have many invoices, so joining users to invoices creates multiple rows per user unless you pre-aggregate invoices first. In PostgreSQL, a safe pattern is to build a CTE with one row per user for invoice metrics, then join that CTE back to users. You can verify grain by counting before and after each join: if output rows jump unexpectedly, the join created fan out. Another good check is to compare COUNT(DISTINCT user_id) with COUNT(*) at each stage. When those diverge unexpectedly, inspect key uniqueness assumptions. Precise aliases such as u, s, and i_latest reduce cognitive load, especially in multi-join queries. Reliable join work is less about memorizing syntax and more about proving each join preserves the intended analytical unit.

Key terms

join cardinality: The relationship pattern between two tables, such as one-to-one or one-to-many.
fan out: Row multiplication caused by joining to multiple matching rows on the other table.
join key: The column pair used in the ON clause to match rows across tables.
left preservation: The property of LEFT JOIN that keeps all rows from the left input.

Active subscribers with latest paid invoice date

Use users(user_id, email), subscriptions(user_id, status), and invoices(user_id, paid_at, state) where invoices are one-to-many per user. Goal grain is one row per active subscriber.

Create an invoice staging query grouped by user_id that computes MAX(paid_at) for rows where state = 'paid', producing one row per user before any join.
Filter subscriptions to active users in a second staging step so business status logic is isolated from invoice logic.
Join active subscriptions to users on user_id with an inner join, because every subscription row must map to a known user.
Left join the one-row-per-user invoice staging table so active subscribers without paid invoices still appear with null latest_paid_at.

Result:

WITH paid_invoice_latest AS (SELECT user_id, MAX(paid_at) AS latest_paid_at FROM invoices WHERE state = 'paid' GROUP BY user_id), active_subscribers AS (SELECT user_id FROM subscriptions WHERE status = 'active') SELECT u.user_id, u.email, p.latest_paid_at FROM active_subscribers a JOIN users u ON u.user_id = a.user_id LEFT JOIN paid_invoice_latest p ON p.user_id = u.user_id ORDER BY u.user_id;

returns one row per active subscriber with correct latest payment context.

A common misconception

Claim: If counts rise after a join, PostgreSQL duplicated rows by mistake.

Correction: PostgreSQL is following join rules correctly. Increased row count usually means the data relationship is one-to-many and your query did not pre-aggregate or otherwise constrain matches to the intended grain.

Lessons in this chapter

Join types and result meaningInterpret how PostgreSQL join types include or exclude unmatched rows. Read the full guide →
Join keys and grainMatch tables on stable keys and check the grain of each dataset before joining.
Preventing accidental fan-outUse pre-aggregation or unique keys to avoid inflated counts after joins.
Readable multi-table SQLAlias tables clearly and keep join predicates separate from business filters.

Study task

Join users, subscriptions, and invoices in PostgreSQL to list active subscribers and their latest paid invoice date.

Chapter checkpoint

Which join keeps all rows from the left table even when there is no match on the right in PostgreSQL?

LEFT JOIN keeps all rows from the left table and fills unmatched right-side columns with NULL.

Joins