Four statements do the day-to-day work of any database: SELECT reads, INSERT adds, UPDATE changes, and DELETE removes. They look simple, and the basics are, but PostgreSQL gives each one capabilities — filtering and ordering, returning modified rows, upserts, and set-based thinking — that separate fumbling with data from working with it fluently. This lesson builds that fluency on a solid mental model.
The single most important shift is to think in sets, not loops. SQL operates on whole sets of rows at once: one UPDATE can modify thousands of rows by describing which rows and what change, with no iteration. Fighting this — looping in application code, one row per round-trip — is both slower and more error-prone than letting the database do set-based work it is built for.
We will walk through each statement with the features that matter most in practice: precise WHERE filtering, ORDER BY and LIMIT, the powerful RETURNING clause, and INSERT ... ON CONFLICT for upserts. These are the verbs you will use constantly, so understanding them well underpins everything from simple scripts to complex analytics.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.