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.
Data lives in tables: named collections of rows, where every column has a fixed name and type. That typing is the first line of defense — a column declared as a date cannot hold "tomorrow-ish", and the engine rejects the write rather than storing something it cannot reason about.
The part worth internalizing early is that a table is a set. Rows have no inherent order. An engine may return them in insertion order today and a different order tomorrow after the data is reorganized, and nothing is broken when it does.
SELECT id, name FROM users LIMIT 10; -- which ten? unspecified
SELECT id, name FROM users ORDER BY id LIMIT 10; -- deterministic
LIMIT without ORDER BY is the common form of this bug, because it looks stable in testing and drifts in production.
Declarative also means you describe the result and the engine plans the execution. Two differently-written queries can produce the same plan, and a query that reads efficiently may not run efficiently — which is why performance work starts with asking the engine what it did, not with rewriting on instinct.
Rule: if order matters, say so; otherwise assume the engine will surprise you eventually.
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.
NULL is not zero and not an empty string. It is a marker meaning "no value recorded here", and comparisons against an unknown must themselves be unknown. That gives three logical outcomes rather than two.
The consequence is immediate: WHERE status = NULL returns nothing at all, because the comparison evaluates to unknown for every row and WHERE keeps only rows that are definitely true. You need the dedicated tests.
WHERE status IS NULL -- correct
WHERE status IS NOT NULL -- correct
WHERE status = NULL -- always matches zero rows
The subtler trap is negation. WHERE status <> 'active' looks like it covers everything that is not active, and it silently drops rows where status is NULL — because unknown is not true. Any filter meant to be exhaustive has to say what happens to the unknown case, usually with OR status IS NULL.
When you genuinely want NULL to compare equal to NULL, IS DISTINCT FROM does that: it treats two NULLs as the same and a NULL against a value as different, giving a definite answer either way.
Rule: every filter on a nullable column deserves the question "and what about the rows where this is unknown?"
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.
SELECT is written first and evaluated late. The engine builds the row source, filters it, groups it, and only then computes the output expressions. So a name you invent in the select list does not exist yet when WHERE runs.
SELECT price * qty AS total FROM items WHERE total > 100; -- rejected: no such column yet
SELECT price * qty AS total FROM items WHERE price * qty > 100; -- repeat the expression
SELECT price * qty AS total FROM items ORDER BY total; -- fine: ORDER BY is later
That asymmetry — alias usable in ORDER BY, not in WHERE — is not a quirk to memorize; it falls straight out of the evaluation order. Some engines accept the first form anyway as an extension, which is worth knowing precisely because it makes the query stop working when it moves to one that does not.
Sorting and paging sit at the end. ORDER BY then LIMIT is how you page, and paging on a non-unique sort key gives unstable pages: rows tie, the tie is broken arbitrarily, and the same row can appear on two pages. Add a unique tiebreaker.
DISTINCT deduplicates the output rows after selection, which means it deduplicates on every selected column — adding a column can make duplicates reappear.
Rule: read a query in evaluation order, not reading order, and most "why doesn't this work" answers itself.
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.
Aggregation turns many rows into one per group. Because the group is the unit, any column you select that is not aggregated has to be something the group agrees on — which is why it must appear in GROUP BY.
The distinction that matters is where a filter lands.
SELECT city, count(*)
FROM orders
WHERE status = 'paid' -- discards rows BEFORE counting
GROUP BY city
HAVING count(*) > 10; -- discards groups AFTER counting
Swap those and the meaning changes: filtering paid orders in HAVING is not possible without an aggregate, and filtering the count in WHERE is not possible at all, because no count exists yet. When a condition can go in WHERE, put it there — fewer rows reach the grouping step.
A second trap is counting. count(*) counts rows; count(column) counts rows where that column is not NULL. The two differ exactly when the column is nullable, which is usually the moment someone reports the numbers disagreeing.
Set operations combine whole result sets instead: UNION removes duplicates and UNION ALL does not, which makes UNION ALL both faster and more honest when you know there are none.
Rule: filter rows in WHERE, filter groups in HAVING, and say which count you mean.
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.
Joining relates rows from two tables by a condition. An inner join returns only pairs that match. A left join returns every row from the left side, with NULLs where the right had nothing.
The first failure is placement. On an outer join, a condition in ON restricts what counts as a match, while the same condition in WHERE runs after the join — and because unmatched rows carry NULL, a WHERE test on the right-hand table discards exactly the rows the outer join was there to keep.
-- keeps every customer, even those with no recent order
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at > now() - interval '30 days'
-- silently becomes an INNER join: customers with no order are filtered out
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.created_at > now() - interval '30 days'
The second failure is multiplication. If the joined side has several rows per key, every one produces an output row — so a sum over the joined result double-counts. The tell is a total that grows when you add a join that "only adds a column". Aggregate the child side first, then join the aggregate.
Rule: on an outer join, conditions about the optional side belong in ON; and after any join, check whether your row count should have changed.
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.
WITH gives a subquery a name, so a query built from three ideas can be read as three named steps rather than nested parentheses. Recursive CTEs extend that to hierarchies — a category tree, a reporting chain — by referencing their own output.
Window functions are the underused half. An aggregate collapses rows; a window function computes over a set of related rows and leaves every row intact.
SELECT name, dept, salary,
avg(salary) OVER (PARTITION BY dept) AS dept_avg,
rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;
Doing that with grouping requires aggregating and joining back; the window version says it once. Running totals, per-group ranks, and "compare this row to its group" are all the same shape.
One caveat worth carrying: a CTE may be treated as an optimization fence, computed once and materialized rather than folded into the surrounding query. That is often what you want, and occasionally it prevents a filter from reaching the inner query, which shows up as a CTE-based rewrite being slower than the nested version it replaced.
Rule: if you are grouping only to join the result back to the detail rows, you wanted a window function.
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.
Pick the narrowest type that can hold the truth. Integers for counts, numeric for money, text for strings, boolean for flags.
Floating point is the classic mistake. float stores binary approximations, so decimal fractions are inexact and repeated arithmetic accumulates error — visible as a total that is off by a cent and refuses to reconcile. Use an exact decimal type for money and reserve floats for measurements where approximation is expected.
Time is the other one. A timestamp with time zone records an unambiguous instant; a timestamp without records a wall-clock reading whose meaning depends on where you were standing. Storing "when did this happen" in the second kind means the answer changes with the reader's assumptions, and daylight-saving transitions make some values ambiguous.
JSON columns are useful for genuinely variable structure, and are frequently reached for to avoid designing a schema. The cost is that the database can no longer validate or constrain what is inside, so every consumer must handle every shape. Prefer the binary JSON form where the engine offers one; it can be indexed, while the text form generally cannot.
Rule: exact types for money, zone-aware timestamps for events, and JSON only where the structure is genuinely unknown.
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.
Writes are the operations where a mistake is expensive, and the syntax offers no protection: omitting WHERE is legal and means "all rows".
The habit worth building is to write the WHERE first, run it as a SELECT, confirm the row set, and only then convert it to an UPDATE. Inside a transaction you get a second safety net, because a wrong result can be rolled back before anyone sees it.
BEGIN;
SELECT count(*) FROM users WHERE last_seen < '2024-01-01'; -- confirm the blast radius
DELETE FROM users WHERE last_seen < '2024-01-01';
-- ROLLBACK; if the count was wrong
COMMIT;
RETURNING closes the loop by having the write report back — the generated key from an insert, or the rows an update actually touched. That removes a whole class of read-after-write races where the follow-up SELECT sees a different state.
Generated keys come from sequences, and two things about them surprise people: values are handed out outside the transaction, so a rollback leaves gaps permanently; and a sequence knows nothing about rows inserted with an explicit key, so bulk-loading with literal identifiers leaves it behind and the next insert collides.
Rule: select it before you delete it, and prefer RETURNING over a follow-up query.
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.
Application validation protects one path. A constraint protects the data, including from the migration script, the admin console, and the colleague using a client directly.
The vocabulary is small. NOT NULL requires a value. CHECK requires an expression to hold. UNIQUE forbids duplicates. PRIMARY KEY is unique and not null together. FOREIGN KEY requires the referenced row to exist, and its referential action decides what happens when that row is deleted — reject it, cascade the delete, or null the reference.
The trap is UNIQUE and NULL. Because NULLs are not equal to each other, a unique column accepts many NULL rows — so "one active subscription per user" implemented as a nullable unique column silently permits any number of them.
Design is the same instinct one level up: store each fact once, so there is no way for two copies to disagree. Deliberate denormalisation for read performance is a legitimate later decision; arriving at it by accident is how a system ends up with two different answers to the same question.
Rule: if a rule must always hold, make the database enforce it — and check what UNIQUE does with your NULLs.
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.
An index is a maintained structure the engine may consult instead of scanning. Every write must update it, so indexes are a trade rather than a free win, and an index nothing queries is pure cost.
Two patterns silently prevent use. Applying a function to the column means the stored values no longer match what you are comparing, so WHERE lower(email) = ? cannot use a plain index on email — you need an index on the expression itself. And a composite index is ordered by its first column, so a query filtering only on the second cannot use it; column order is part of the design.
The habit that matters more than any rule is asking the engine what it did rather than guessing.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
That distinguishes an estimate from reality, and a large gap between predicted and actual row counts usually means stale statistics — which is a different fix from adding an index.
A sequential scan is not automatically a failure: for a small table, or a query returning most rows, scanning is genuinely cheaper, and the planner choosing it is the system working.
Rule: measure with the plan before adding an index, and check the plan again afterwards to confirm it is used.
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.
Wrap statements in a transaction and they either all take effect or none do. That is the easy half, and it is why any multi-statement invariant belongs inside one.
The harder half is isolation. Under the common default, each statement sees a snapshot taken when that statement began, so two reads in the same transaction can return different values. Read-modify-write logic built on that assumption has a race: you read a balance, compute a new one, and write it, while another transaction did the same in between.
Stronger levels fix it, at a price. A repeatable-read level holds one snapshot for the whole transaction. A serializable level makes concurrent transactions behave as though they ran one after another — and pays for it by aborting transactions that would violate that, which means the application must catch the failure and retry. Code that assumes commit always succeeds will lose writes under it.
The alternative is to let the database do the arithmetic (SET balance = balance - 10) or to lock the rows you intend to modify. Locks introduce their own hazard: two transactions taking the same locks in opposite orders deadlock, and the engine resolves it by killing one.
Rule: decide explicitly what your transaction must not see changing, and be ready to retry.
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.
Injection happens when data becomes part of the statement. Concatenating user input into a query means the input can end the string and add its own clauses. Escaping is a losing game — the reliable fix is parameters, where the value is sent separately and never parsed as syntax.
-- vulnerable: the input is part of the statement text
"SELECT * FROM users WHERE email = '" + input + "'"
-- safe: the value can never become syntax
SELECT * FROM users WHERE email = $1;
Note that parameters bind values, not identifiers — a table or column name chosen at runtime cannot be parameterized, and must be validated against an allowlist instead.
Privileges are the next layer: an application account rarely needs to drop tables, and limiting it caps the damage of any single mistake.
Row-level policies push the rule into the database, so a query returns only the rows a user may see regardless of what it asks for. Two things surprise people: enabling row security with no policy defined denies everything rather than allowing it, and the table's owner normally bypasses policies entirely — so testing as the owner shows a filter working when it is not being applied at all.
Rule: parameterize every value, and test row-level rules as the actual application role.
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.
A view names a query and lets the rest of the system treat it like a table. That is genuinely useful for hiding a join everyone repeats, or for exposing a narrowed subset of a table.
What it does not do is make anything faster. A plain view is substituted into the query that uses it, so a view over an expensive query is an expensive query wherever it appears — and stacking views on views produces something whose real cost nobody can see from the call site.
Views are updatable only when the engine can map a written row back to exactly one base row, which rules out aggregates, DISTINCT, grouping, and set operations. Anything more complex is read-only unless you write the write path yourself.
A materialized view is the opposite trade: the result is stored, so reads are fast and the data is as stale as the last refresh. That is a good fit for dashboards and a bad one for anything that must be current.
Server-side routines put procedural logic next to the data, which is worth it when a job would otherwise pull rows out only to write them back. The cost is that this logic lives outside your application's tests, review, and deployment — so keep it small and deliberate.
Rule: use a view for clarity, a materialized view for speed, and never assume the first one is the second.
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.