3.4 SQL for data work

What this is and why it exists

SQL is how data actually leaves a database, and the difference between an analyst who writes their own queries and one who waits for an extract is roughly a factor of two in how fast they work. The language is small: five clauses cover most questions, joins cover most of the rest, and window functions cover the ones that used to require exporting to a spreadsheet. It also has one rule that quietly produces wrong answers, and it is about nothing.

The vocabulary

• SELECT — which columns come back; FROM — from where.
• WHERE — which rows qualify, applied before grouping.
• GROUP BY — collapse rows into groups; HAVING — filter those groups after aggregating.
• Join — combine rows from two tables on a condition.
• Anti-join — rows in one table with no match in the other.
• Window function — a calculation across related rows that, unlike an aggregate, leaves the rows separate.
• CTE (common table expression) — a named intermediate result, written with WITH.
• NULL — unknown; not zero, not empty, and not equal to anything.

The mental model

A query is evaluated in a fixed logical order, and holding it explains most confusions: rows come from FROM, are filtered by WHERE, grouped by GROUP BY, groups filtered by HAVING, columns chosen by SELECT, then ORDER BY, then LIMIT. Which is why WHERE cannot use an aggregate — the grouping has not happened yet — and why HAVING can. Two clauses, two moments.

Joins are the second idea and the mental picture is worth getting right: for each row on the left, find matching rows on the right, and the join type decides what happens when there are none. Inner drops unmatched rows on both sides. Left keeps every left row, filling the right with nulls where nothing matched — which makes the anti-join pattern possible: a left join, then a WHERE requiring the right side's key to be null, which gives you exactly the rows with no match. "Students who never sat an exam" is that query, and it is one of the most useful shapes in practice.

Window functions are the third, and they are the tool most self-taught analysts are missing. The documentation distinguishes them from aggregates precisely: a window function "performs a calculation across a set of table rows that are somehow related to the current row… However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities." So you get each row and its group's statistic, in one pass — a rank within a subject, a running total, the previous month's value beside this month's. The clause that controls it is OVER, which "determines exactly how the rows of the query are split up", and PARTITION BY inside it "divides the rows into groups, or partitions, that share the same values".

Two facts about them save real time. They can be used only in the SELECT list and the ORDER BY clause, because they "logically execute after the processing of those clauses" — so filtering on a rank means computing it in a CTE and filtering outside. And they see only rows that survived the WHERE: "a row removed because it does not meet the WHERE condition is not seen by any window function."

Then the trap, which is responsible for a startling share of all wrong query results. NULL is unknown, and unknown compares to nothing. The documentation is exact: "ordinary comparison operators yield null (signifying 'unknown'), not true or false, when either input is null. For example, 7 = NULL yields null." So a WHERE clause comparing a nullable column silently drops every row where the value is missing — including a filter written specifically to keep them. The remedy is the predicates that exist for it: "do not write expression = NULL because NULL is not 'equal to' NULL", use IS NULL and IS NOT NULL; and where you want nulls treated as an ordinary value, IS DISTINCT FROM "effectively act[s] as though null were a normal data value, rather than 'unknown'".

CTEs are the readability tool and worth using early. A query with three nested subqueries is read from the inside out; the same query as three named WITH steps is read top to bottom, each step named for what it produces. There is no performance argument to have here at the sizes you will meet — write the readable one.

In code

Checked against the PostgreSQL documentation.

-- Aggregate: rows collapse. WHERE filters rows, HAVING filters groups.
SELECT subject, COUNT(*) AS sat, AVG(score) AS mean_score
FROM exam_results
WHERE taken_on >= DATE '2026-01-01'
GROUP BY subject
HAVING COUNT(*) >= 30
ORDER BY mean_score DESC;

-- Anti-join: students with no result at all.
SELECT s.student_id, s.name
FROM students s
LEFT JOIN exam_results r ON r.student_id = s.student_id
WHERE r.student_id IS NULL;

-- Window functions: each row keeps its identity and gains its group's context.
WITH ranked AS (
    SELECT
        student_id,
        subject,
        score,
        ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rank_in_subject,
        LAG(score) OVER (PARTITION BY student_id ORDER BY taken_on) AS previous_score,
        SUM(score) OVER (PARTITION BY student_id ORDER BY taken_on) AS running_total
    FROM exam_results
)
SELECT *
FROM ranked
WHERE rank_in_subject <= 3;

The last query shows both window-function facts at work. The rank is computed in a named step, because a window function cannot appear in a WHERE; and the filter on it happens outside, in the query that selects from that step. Written as one query with the rank in the WHERE, it would be rejected — and knowing why saves you the twenty minutes of rearranging.

What you should now be able to explain or do

Recite the logical order of clauses and use it to explain why an aggregate cannot appear in a WHERE. Write an anti-join and say what it answers. Explain what a window function gives you that an aggregate does not, in the documentation's terms. Say where window functions are permitted and how to filter on one anyway. State what a comparison with NULL yields and name the two predicates that handle it. Rewrite a nested query as named steps.

Check yourself