Postgres – Joins

Joins are the reason to use a relational database, and the counts below are real — every number in this post comes from running the query against a booking database with 400,000 bookings and 374,518 payments in it. Seeing the counts change is how the difference between the join types stops being abstract.

The shape: every booking may have a payment, but a booking still waiting to be paid has none. That gap is what the join types disagree about.

INNER JOIN

SELECT count(*)
FROM   bookings b
JOIN   payments p ON p.booking_id = b.id;
-- 374,518

Rows that match on both sides. The 25,482 bookings with no payment are gone. JOIN with no qualifier means INNER JOIN, and this is the one you want most of the time.

LEFT JOIN

SELECT count(*)
FROM   bookings b
LEFT   JOIN payments p ON p.booking_id = b.id;
-- 400,000

Every row from the left table, with NULLs where the right side has nothing. The count is the count of bookings, which is the useful property: a left join cannot lose rows from the table you started with.

That makes it the tool for "find the ones with nothing":

SELECT count(*)
FROM   bookings b
LEFT   JOIN payments p ON p.booking_id = b.id
WHERE  p.id IS NULL;
-- 25,482

The mistake everyone makes once

Put a condition on the right-hand table in the WHERE clause and the left join quietly becomes an inner join:

-- looks like a LEFT JOIN, behaves like an INNER JOIN: 374,518 rows.
-- Every unpaid booking has p.status = NULL, and NULL <> 'REFUNDED' is not true.
SELECT count(*)
FROM   bookings b
LEFT   JOIN payments p ON p.booking_id = b.id
WHERE  p.status <> 'REFUNDED';

-- keep the condition in the JOIN, and the unmatched rows survive: 400,000 rows.
SELECT count(*)
FROM   bookings b
LEFT   JOIN payments p ON p.booking_id = b.id AND p.status <> 'REFUNDED';

The rule is worth memorising because nothing warns you: a condition in ON decides what counts as a match; a condition in WHERE filters the rows that came out. For the left table it makes no difference. For the right table it is the difference between a left join and an inner one.

RIGHT, FULL and CROSS

-- one row per property per month, whether or not anything was booked
SELECT p.id, m.month, count(b.id) AS bookings
FROM   properties p
CROSS  JOIN generate_series(DATE '2024-01-01', DATE '2024-06-01', INTERVAL '1 month') AS m(month)
LEFT   JOIN bookings b ON b.property_id = p.id
                      AND b.check_in >= m.month
                      AND b.check_in <  m.month + INTERVAL '1 month'
WHERE  p.id <= 3
GROUP  BY p.id, m.month
ORDER  BY p.id, m.month;

The cross join generates the rows that should exist and the left join attaches the data that does. Without it, a month with no bookings is simply missing from the result, and a chart drawn from it has no gap where the gap should be.

Anti-joins: NOT EXISTS

The LEFT JOIN ... WHERE IS NULL above works. NOT EXISTS says the same thing more directly, and it is the one to reach for:

SELECT count(*)
FROM   bookings b
WHERE  NOT EXISTS (SELECT 1 FROM payments p WHERE p.booking_id = b.id);
-- 25,482

Postgres plans both as an anti-join, so they perform the same. NOT EXISTS wins on two counts: it says what it means, and it is safe. NOT IN (SELECT …) is the version to avoid — a single NULL in the subquery's results makes the whole thing return nothing, silently.

Self-joins

A table joined to itself, with two aliases. Overlapping stays at the same property, which is the condition the exclusion constraint on this table forbids:

SELECT a.id, b.id, a.check_in, b.check_in
FROM   bookings a
JOIN   bookings b ON b.property_id = a.property_id
                 AND b.id > a.id
                 AND a.check_in < b.check_out
                 AND b.check_in < a.check_out
LIMIT  5;

b.id > a.id is doing real work: without it every pair appears twice, once in each direction, and every row also matches itself.

LATERAL

An ordinary subquery in FROM cannot see the columns of the tables beside it. LATERAL lets it, which turns "for each row, run this query" into one statement:

-- the three most recent bookings for each of five properties
SELECT p.id, p.city, recent.check_in, recent.total
FROM   properties p
CROSS  JOIN LATERAL (
    SELECT b.check_in, b.total
    FROM   bookings b
    WHERE  b.property_id = p.id          -- only legal because of LATERAL
    ORDER  BY b.check_in DESC
    LIMIT  3
) AS recent
WHERE  p.id <= 5
ORDER  BY p.id, recent.check_in DESC;

This is top-N-per-group, and it is the version that stays fast when N is small and the inner query has an index to seek with — Postgres runs the inner query once per outer row and stops after three. The window-function version reads more elegantly and has to sort every booking of every property first. Both are in the next posts; which is faster depends on how many rows you are skipping.

CROSS JOIN LATERAL drops an outer row when the inner query returns nothing. Use LEFT JOIN LATERAL (…) ON true when you want to keep it.

Joining more than two tables

Joins are evaluated left to right, and each one narrows what the next sees. The order you write them in does not change the result — the planner reorders freely — but it changes how readable the query is, and putting the most restrictive filter first is a habit worth keeping:

SELECT p.city,
       count(*)                    AS stays,
       round(avg(b.total), 2)      AS avg_total,
       round(avg(r.rating), 2)     AS avg_rating
FROM   bookings b
JOIN   properties p ON p.id = b.property_id
JOIN   payments   pay ON pay.booking_id = b.id AND pay.status = 'SUCCEEDED'
LEFT   JOIN reviews r ON r.booking_id = b.id
WHERE  b.check_in >= DATE '2024-06-01'
  AND  b.check_in <  DATE '2024-07-01'
GROUP  BY p.city
ORDER  BY stays DESC
LIMIT  5;

Three things in there are the whole post. pay.status = 'SUCCEEDED' sits in the ON of an inner join, where it means the same as it would in WHERE — inner joins do not care. The reviews join is LEFT, because an unreviewed stay should still be counted. And avg(r.rating) ignores the NULLs that left join produces, which is what you want and is worth knowing rather than assuming.

USING and NATURAL

-- the usual case: the columns have different names, so ON is the only option
SELECT count(*) FROM bookings b JOIN properties p ON p.id = b.property_id;

-- USING needs the SAME name on both sides
SELECT count(*) FROM property_images JOIN property_amenities USING (property_id);

USING is shorthand for when both columns have the same name, and it collapses them into one output column. Notice how rarely it applies here: a foreign key called property_id points at a column called id, so most joins in a normally named schema cannot use it at all. NATURAL JOIN takes that further and joins on every same-named column automatically — which means adding a created_at to both tables silently changes what the query returns. Never use it.