PostgreSQL Basics #3 Joins and Aggregation: Working Instincts for Read Queries

Infrastructure PostgreSQL Database
4 min read

We made tables (chapter 2); now it’s time to read them. The bulk of real-world read queries ends up being some combination of joins and aggregation, and the accidents in that combination are formulaic too. Instead of a syntax tour, this chapter centers on those formulaic accidents, building working instincts with the two tables users and orders.

Joins: INNER is the default, LEFT is “must appear even when absent” #

INNER and LEFT JOIN 
-- Only users who have orders: INNER JOIN
SELECT u.name, o.amount_usd
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Users without orders too: LEFT JOIN (missing side becomes NULL)
SELECT u.name, o.amount_usd
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

The selection criterion is the shape of the question. “Of the users who ordered, …” is INNER; “for all users, … (none if no orders)” is LEFT. And here comes the most common accident: write a LEFT JOIN, then put a condition on the right-hand table in WHERE, and the NULL rows get filtered out — you’re back to an INNER JOIN.

Right-side filter in ON 
-- Intent: all users + December orders. Reality: only users WITH December orders (LEFT neutralized)
... LEFT JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-12-01';

-- Correct form: right-hand table conditions go in the ON clause
... LEFT JOIN orders o
       ON o.user_id = u.id
      AND o.created_at >= '2026-12-01';

One rule eliminates this whole family of accidents: in a LEFT JOIN, filters on the right table go in ON; filters on the left table go in WHERE.

Aggregation: the roles of GROUP BY and HAVING #

WHERE and HAVING 
SELECT o.user_id, count(*) AS order_count, sum(o.amount_usd) AS total_usd
FROM orders o
WHERE o.status = 'paid'          -- row-level filter: before aggregation
GROUP BY o.user_id
HAVING sum(o.amount_usd) >= 1000;  -- group-level filter: after aggregation

WHERE filters rows before aggregation; HAVING filters groups after it. “Count only paid orders, but only for users whose total is $1,000 or more” splits naturally into exactly those two filters. Note that count(*) counts rows while count(o.column) counts non-NULLs — when counting LEFT JOIN results, that difference decides whether zero orders shows up as 0.

The join + aggregation trap: double counting #

When one user has 3 orders and 2 reviews, joining users to both orders and reviews multiplies the rows to 3 × 2 = 6 — and sum(o.amount_usd) on top of that counts every amount twice. Aggregating after a many-to-many join always carries this risk. The proper form is to finish each aggregation first in a subquery (or a CTE, next chapter’s tool) and join the results one-to-one.

Separate aggregation with subqueries 
SELECT u.name, coalesce(os.total_usd, 0) AS total_usd, coalesce(rs.review_count, 0) AS review_count
FROM users u
LEFT JOIN (SELECT user_id, sum(amount_usd) AS total_usd FROM orders GROUP BY user_id) os
       ON os.user_id = u.id
LEFT JOIN (SELECT user_id, count(*) AS review_count FROM reviews GROUP BY user_id) rs
       ON rs.user_id = u.id;

A large share of “the totals look too big” bug reports comes down to this one pattern.

Latest row per group: DISTINCT ON #

“Each user’s most recent order” is a workhorse requirement, and standard SQL makes it surprisingly clumsy. PostgreSQL has a dedicated tool.

Latest row per group 
SELECT DISTINCT ON (user_id) user_id, id, amount_usd, created_at
FROM orders
ORDER BY user_id, created_at DESC;

DISTINCT ON (user_id) keeps only the first row per user_id, and ORDER BY defines what “first” means (per user, descending created_at — that is, the latest). The standard-SQL equivalent, the window function ROW_NUMBER(), is covered in chapter 8; when you need the top N (say, the latest 3), that’s the answer.

Whether to use a subquery or a join is mostly a readability question, and PostgreSQL’s planner often transforms them into equivalent forms anyway. “Which is faster” is answered not by guessing but by the execution plan — and that tool, EXPLAIN, is chapter 5’s topic.

Summary #

  • INNER means “only what’s on both sides”; LEFT means “everything on the left.” The sentence shape of the question is the selection criterion.
  • Right-hand table conditions in a LEFT JOIN go in ON. Putting them in WHERE neutralizes the LEFT — this chapter’s biggest trap.
  • WHERE is the pre-aggregation row filter, HAVING the post-aggregation group filter. Remember the NULL difference between count(*) and count(column).
  • Aggregating after joining multiple child tables is a double-counting minefield. Finish aggregation in subqueries first, then join one-to-one.
  • Latest-per-group is DISTINCT ON, the PostgreSQL-native answer. Top-N-per-group continues into chapter 8’s window functions.
X