The Art of PostgreSQL

0001-01-01

How to Learn SQL

There’s no secret sauce — learn the two foundations (relations and declarative thinking), then practice in small steps and build from there.

0001-01-01

The OVER Clause: One Result Per Row Without GROUP BY

OVER turns any aggregate into a window function — computing a result across a set of rows while still returning every row intact.

0001-01-01

What is an SQL JOIN?

Forget the set-theory Venn diagrams. A JOIN composes two collections of objects into a new collection that has the properties of both.

0001-01-01

JOIN Output Cardinality: Why Your Row Counts Surprise You

A JOIN does not just combine tables — it multiplies rows. One-to-many joins inflate counts silently, and aggregate queries over joined data produce wrong results unless you account for it.

0001-01-01

What is an SQL Aggregate?

It’s the reduce half of map/reduce: an aggregate computes a single result per group, and GROUP BY defines the groups.

0001-01-01

What is an SQL Relation?

A table is a relation with storage. Every SQL query you write defines a new relation, assembled on the fly from relations that already exist.

0001-01-01

GROUP BY and HAVING: Thinking in Groups

GROUP BY compresses many rows into one per group. Understanding the evaluation order — WHERE, then GROUP BY, then HAVING, then SELECT — is what makes aggregation queries predictable.

0001-01-01

The FILTER Clause: Multiple Counts in One Pass

count(*) FILTER (WHERE condition) computes conditional aggregates without CASE expressions or separate subqueries — one scan, multiple results.

0001-01-01

Storing the same fact in more than one place is the root cause of three predictable failures. Understanding update, insertion, and deletion anomalies is what makes normalization feel obvious rather than arbitrary.

0001-01-01

LATERAL JOIN for Top-N Per Group

LATERAL lets the right-hand subquery reference columns from the left side — enabling top-N per group in a single scan without window functions and outer filters.