SQL — Structured Query Language — asks for a result and lets the engine decide how to get it. That one fact explains most of what confuses people: your clauses are not steps executed top to bottom, an index is a hint the planner may ignore, and two queries that look different can run identically. The other half of the confusion is NULL, which is not a value but a statement that the value is unknown, and which quietly changes the meaning of comparisons everywhere. Read this for the model — the syntax is easy once you know what the engine is actually being asked.
Each chapter opens with the short version. Tap one to read the detail.
What a query actually asks
~2 min
A table is an unordered set of typed rows, and a query describes the result you want rather than the steps to produce it. Nothing has an order until you ask for one, which is why a query without ORDER BY can change its output at any time.
NULL is unknown, not empty
~2 min
NULL means the value is unknown, so comparing to it yields unknown rather than true or false. WHERE keeps only rows that are definitely true, so unknown rows silently disappear — which is why = NULL never matches anything.
Clause order is logical, not textual
~2 min
A query is evaluated FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — not in the order you typed it. That single fact explains why an alias defined in SELECT cannot be used in WHERE.
Aggregation, and WHERE versus HAVING
~2 min
GROUP BY collapses rows into groups and every non-aggregated column in the select list must be grouped. WHERE filters rows before grouping; HAVING filters groups after — putting a condition in the wrong one changes the answer, not just the speed.
Joins, and the two ways they go wrong
~2 min
An inner join keeps rows that match; an outer join keeps unmatched rows and fills the other side with NULL. The two classic failures are a condition in WHERE silently turning an outer join inner, and a duplicate key silently multiplying rows.
CTEs and window functions
~2 min
A common table expression (CTE) names a query so a complex one reads in steps. A window function computes across related rows *without* collapsing them — which is the tool people reach for GROUP BY and self-joins to fake.
Choosing types, including time and JSON
~2 min
Types are the cheapest constraint you have. The two that cause real damage when chosen carelessly are floating point for money and a naive timestamp for anything a user sees.
Changing data safely
~2 min
UPDATE and DELETE without WHERE change every row in the table, and the statement is valid so nothing warns you. RETURNING lets a write tell you what it did.
Constraints, and letting the database say no
~2 min
A constraint is a rule the database enforces for every writer, forever — stronger than any check in application code, because nothing can bypass it. UNIQUE treats NULLs as distinct, which is the surprise worth knowing.
Indexes and reading the plan
~2 min
An index speeds reads and slows writes, and the planner decides whether to use it. Two habits stop it being usable at all: wrapping the indexed column in a function, and querying a composite index without its leading column.
Transactions, isolation and concurrency
~2 min
A transaction makes several statements one atomic unit. Isolation level decides what it sees of concurrent work, and the default is weaker than most people assume — a value you read can change before you commit.
Injection, privileges and row-level rules
~2 min
String-concatenated queries are the vulnerability; parameters are the fix, and escaping is not. Beyond that, privileges limit what an account can reach, and row-level policies limit which rows it sees.
Views and server-side logic
~2 min
A view is a saved query that runs each time it is referenced, so it costs what its query costs. A materialized view stores the result instead, trading freshness for speed.
Written by Keentune. We are not affiliated with or endorsed by the organizations whose documentation informs this guide, and any linked sources belong to their respective owners.
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.