The Grammar of SQL
Game: the terminal of every chapter
A handful of rules that every correct query follows: what a grouped query may show, how text and names are quoted, how values of different types are compared, what makes a condition.
The game's terminal follows PostgreSQL's rules, and on types it adds one rule of its own: a query that breaks one does not run, and the terminal answers with an error line in PostgreSQL's words and a sentence on how to fix it. The query still counts, but it is not kept in the History list. This page gives the order in which a query is worked out, from which most of the rules follow, then lists every rule with a link to the page that explains it, with examples on the bakery database.
Order of execution
A query is written starting with SELECT, but it is not worked out in that order. Logically, the database takes the clauses in this order, and each step sees only what the step before it produced:
FROMandJOINgathers the rows of the tables and pairs them upWHEREkeeps the rows whose condition is trueGROUP BYgathers the remaining rows into groupsHAVINGkeeps the groups whose condition is trueSELECTworks out the columns of the result, window functions included, and names themDISTINCTremoves the rows of the result that repeatORDER BYsorts the resultLIMITandOFFSETkeeps a slice of the sorted rows
Most of the rules on this page follow from that order. An alias is a name given at step 5, so WHERE and HAVING, at steps 2 and 4, run before it exists and cannot use it (see Aliases in WHERE and HAVING), while ORDER BY, at step 7, can (PostgreSQL also lets GROUP BY name an alias, an exception to this order). A window function is computed at step 5 too, which is why it cannot be filtered in WHERE: compute it in a CTE and filter outside. After step 3 each row is a group, so steps 4 to 7 may only use grouped columns and aggregates, plus, in ORDER BY, the result's own column names. And LIMIT comes last, so it keeps the first rows of the sorted result, never a sample taken before sorting. With UNION, INTERSECT or EXCEPT, each half runs steps 1 to 6 on its own, the two results are combined, and the one ORDER BY and LIMIT apply to the combination. The database may take shortcuts when it actually runs a query, but the result is always the one this order gives.
The rules, and where each is explained
Each rule sits on the page of the construct it belongs to, with a query that breaks it, the terminal's answer, and the correct version. The terminal's own error messages link to the same sections.
SELECT and WHERE
- Double quotes
'text'is a value,"name"a name, and a quoted name keeps its case. - One equals signSQL compares with
=, never==. - TypesA number against a number, text against text,
||on text. - Aliases in WHERE and HAVINGA
SELECTname does not exist there yet. - Conditions
WHERE,HAVING,ONandWHENneed something true or false.
ORDER BY and LIMIT
- DISTINCT and ORDER BYSort by what was selected.
- LIMIT and OFFSET
LIMIT 3 OFFSET 2, notLIMIT 2, 3.
LIKE and text patterns
- LIKE and ILIKEThe only pattern matches, case-sensitive or not.
Dates and times
- Dates, substr and CASTComparisons,
substrandCAST, notstrftimeordate(d, '+1 day').
GROUP BY, aggregates and HAVING
- GROUP BYEvery column grouped or aggregated.
- HAVING without GROUP BYAggregates only.
- ORDER BY after GROUP BYGrouped columns, aggregates or aliases.
- string_agg and SUMNot
GROUP_CONCATorTOTAL.
NULL and missing values
- COALESCE and ISNot
IFNULL;ISonly withNULL,TRUEorFALSE.
CASE WHEN
- CASE, not IIFNo
IIF, and noMAXorMINof two values.
On this page
- Other shorthands
printf,randomblob,likely: what to write instead.
Other shorthands
A few more functions are not standard SQL either. Write these instead:
| Instead of | Write |
|---|---|
printf(...) | format(...) |
randomblob(n) | random() |
likely(c), unlikely(c) | the condition c alone |
In the game: every chapter's terminal follows these rules, in both the investigation and the timed sessions.