Joins In PostgreSQL

Joins are one of those topics where everyone nods along when you explain them, then freezes up the moment they have to write one on the spot, in front of someone, without being able to run it first and check. I think that's mostly because the four main types genuinely blur together if you've only half-memorized their definitions instead of actually understanding what each one is doing to the result set. This article is my attempt at explaining them the way I eventually understood them, not the way they're usually first presented.

Why this is the part people freeze on

The syntax for a join isn't hard. SELECT ... FROM a JOIN b ON a.x = b.y — that's most of it. What actually trips people up is reasoning about what rows come back, especially when the two tables don't match up perfectly. A venue with no closures. A closure somehow pointing at a venue that doesn't exist. These edge cases are exactly what separates the four join types, and if you've only memorized "LEFT JOIN keeps everything on the left," without really internalizing why, you'll get it backwards the first time you're asked to write one under any pressure.

Working through all four, using the same two tables

Assume venues and closures, where closures.venue_id references venues.id.

-- INNER JOIN: only rows that match in both tables
SELECT v.name, c.closed_on
FROM venues v
INNER JOIN closures c ON v.id = c.venue_id;

This gives you venues that actually have at least one closure recorded. A venue with zero closures just doesn't show up at all — not as a row with nulls, it's simply absent from the result.

-- LEFT JOIN: all venues, even ones with no closures
SELECT v.name, c.closed_on
FROM venues v
LEFT JOIN closures c ON v.id = c.venue_id;

Every venue shows up here, whether or not it has closures. For venues with no matching closure row, c.closed_on just comes back as null. This is probably the join you'll reach for most often in practice, since "show me all of X, along with whatever related Y exists" is an extremely common shape of question.

-- RIGHT JOIN: all closures, even orphaned ones
SELECT v.name, c.closed_on
FROM venues v
RIGHT JOIN closures c ON v.id = c.venue_id;

The mirror image of LEFT JOIN — every closure shows up, even if somehow its venue_id doesn't match any real venue. In practice this is rare to actually use, since you can just swap the table order and use LEFT JOIN instead, which is why most people default to always writing LEFT JOIN and almost never reach for RIGHT JOIN even though it exists.

-- FULL OUTER JOIN: everything from both sides
SELECT v.name, c.closed_on
FROM venues v
FULL OUTER JOIN closures c ON v.id = c.venue_id;

Everything from both tables, matched where possible, with nulls filling in on whichever side doesn't have a match. Less common day to day, but genuinely useful when you need to audit both directions at once — venues with no closures, and closures pointing at venues that no longer exist.

A simpler way I ended up explaining these to myself: INNER only wants pairs that actually match. LEFT wants everything from the left table no matter what, filling in gaps with null. RIGHT is the same idea flipped. FULL OUTER wants everything from both, period.

Self joins, and the thing that goes wrong without an ON clause

-- venues in the same city, paired against each other
SELECT a.name, b.name
FROM venues a
JOIN venues b ON a.city = b.city AND a.id < b.id;

This is a self join — the same table joined against itself, using two aliases (a and b) so Postgres can tell which "copy" of the table a given column reference belongs to. The classic real-world case is an employee-manager table, where each row references another row in the same table. Here it's a more contrived example — pairing up venues that share a city — but the mechanics are identical. The a.id < b.id part matters too: without it, you'd get every pair twice, once in each order, plus every venue paired with itself.

The mistake that catches almost everyone at least once: forgetting the ON condition entirely, or getting it wrong in a way that matches everything. That gives you a CROSS JOIN by accident — every row from one table matched with every row from the other, a full Cartesian product. If a join query comes back with a suspiciously huge row count, way more than you'd expect, this is almost always why. It's usually pretty obvious once you see it, but it's an easy thing to miss for a minute if you're staring at a query that looks syntactically fine.

A real CROSS JOIN does have legitimate uses — generating every combination of two sets, like all dates crossed with all venues for a reporting calendar that needs a row for every date-venue pair regardless of whether anything happened. It's rare, but it's worth knowing it's an intentional tool sometimes, not just a mistake that happens to have a name.

Working through a real query

Say you want a report: every venue, and how many times it's been closed, including venues that have never been closed at all, showing zero rather than being left out.

SELECT v.name, COUNT(c.id) AS closure_count
FROM venues v
LEFT JOIN closures c ON v.id = c.venue_id
GROUP BY v.name
ORDER BY closure_count DESC;

The LEFT JOIN is doing the actual work here — it's what makes sure venues with zero closures still show up, instead of silently vanishing the way they would with an INNER JOIN. COUNT(c.id) matters more than it looks like it should: for a venue with no closures, the joined c.id value is null, and COUNT() doesn't count nulls, so it correctly comes back as zero. If you'd written COUNT(*) instead, every row would count as one regardless of whether c.id was null, and a venue with no closures would incorrectly show a count of one instead of zero, because the LEFT JOIN still produces exactly one row for it, just with null values in it.

That's a small detail, but it's exactly the kind of thing that produces a report with numbers that are subtly wrong in a way that's easy to miss during a quick review.

Where this leaves you

The four join types aren't really four different things to memorize — they're one idea (match rows between two tables based on a condition) with different rules about what happens when a match doesn't exist on one side or the other. If you can explain, out loud, what happens to an unmatched row in each of the four cases, you understand joins. If you're just recalling which keyword goes with which definition, that's worth practicing out loud a few times before it comes up somewhere that actually matters.

Next up: aggregations — GROUP BY, HAVING, and the subquery patterns that come up constantly once you start asking more complex questions of your data.