SQL Joins in PostgreSQL, Explained Properly

Joins are where SQL stops being a lookup language and starts being a query language. Most developers know INNER JOIN and stop there, then get surprised when rows go missing, or duplicate, or a report shows fewer customers than actually exist. Almost all of those surprises come from not being precise about which join you meant.

This is part 5 of the PostgreSQL Masterclass. With the schema and constraints in place, we can start asking real questions of the data, and every interesting question eventually needs a join.

The mental model that makes joins click

A join takes two tables and produces rows by matching them on a condition. The join type decides what happens to rows that don't find a match. That's the entire concept. Everything else is a variation on "what do I do with the unmatched rows."

Here are two small tables to work with:

CREATE TABLE customers (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  total       numeric(12, 2) NOT NULL
);

Say you have 3 customers and one of them has placed no orders. How that customer appears in your results depends entirely on the join you pick.

INNER JOIN: only the matches

INNER JOIN returns rows where the condition matches on both sides. Unmatched rows on either side vanish.

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

The customer with no orders does not appear at all. That's correct when you want "customers who have ordered," and it's a bug when you meant "all customers, with their orders if any." The word INNER is optional in Postgres, so a bare JOIN is an inner join. Being explicit costs nothing and reads more clearly.

LEFT JOIN: keep everything on the left

LEFT JOIN keeps every row from the left table and fills in NULL where the right side has no match.

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Now the orderless customer shows up once, with NULL for total. This is the join you want for "list all X, with their related Y." It's also the source of a classic mistake: filtering the right table in the WHERE clause instead of the ON clause.

-- BUG: this quietly turns the LEFT JOIN back into an INNER JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

-- correct: put the condition on the join
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 100;

In the first query, the orderless customer has NULL for o.total, and NULL > 100 is not true, so the WHERE clause drops them. You asked for a left join and got an inner join's result. When you filter the right-hand table of a left join, decide whether the condition belongs in ON (keep unmatched left rows) or WHERE (a genuine post-join filter).

RIGHT and FULL: less common, still useful

RIGHT JOIN is a LEFT JOIN with the tables swapped, so it keeps every row from the right table. Most people rewrite it as a left join for readability, since reading top-to-bottom left-to-right is easier when the preserved table comes first.

FULL OUTER JOIN keeps unmatched rows from both sides, filling NULL on whichever side is missing. It's the right tool for reconciliation: finding rows in table A with no match in B and rows in B with no match in A, in one pass.

-- find customers with no orders AND orders with no customer, together
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL;

The duplicate-row trap

Here's the join surprise that bites people in production. When you join to a table that has many rows per key, each matching row multiplies the output. Join a customer to their 5 orders and that customer's name appears 5 times. Now sum something from the customer side and you've counted it 5 times.

-- WRONG: if a customer has many orders, their credit is counted once per order
SELECT sum(c.account_credit)
FROM customers c
JOIN orders o ON o.customer_id = c.id;

The fix is to aggregate before joining, so each side contributes one row per key:

SELECT c.name, c.account_credit, o.order_count
FROM customers c
JOIN (
  SELECT customer_id, count(*) AS order_count
  FROM orders
  GROUP BY customer_id
) o ON o.customer_id = c.id;

Whenever a total looks inflated after adding a join, this is almost always why. A join fanned out the rows and an aggregate double-counted them.

Joining more than two tables

Real queries join several tables, and the same rules apply pairwise. Postgres joins left to right, applying each ON condition as it goes:

SELECT c.name, o.id AS order_id, oi.product_name, oi.quantity
FROM customers c
JOIN orders o        ON o.customer_id = c.id
JOIN order_items oi  ON oi.order_id = o.id
WHERE c.name = 'Acme Inc';

One thing worth knowing: the order you write joins does not force the order Postgres executes them. The query planner reorders joins to run the cheapest plan it can find, using your indexes and table statistics. You write joins for correctness and readability; the planner handles efficiency. We'll get into reading its decisions when we cover EXPLAIN later in the series.

A short checklist

When a join result looks wrong, run through this:

• Missing rows you expected? You probably wanted a LEFT JOIN, or a right-table filter moved into WHERE and dropped your unmatched rows.
• Duplicate rows or inflated sums? A join to a one-to-many table fanned out the rows; aggregate the many side first.
• Slow join? That's an indexing and planning question, and index the join keys first.

Joins are not hard once you hold the core idea: pick the type by what should happen to unmatched rows, and watch for one-to-many relationships inflating your aggregates. Get those two right and the rest is detail.

Next, we stay with querying and look at aggregations: GROUP BY, HAVING, and the aggregate functions that turn rows into summaries.