SQL Basics

Why this exists

SQL looks like English, which is exactly why it misleads people. You write what you want and the database decides how to get it — so two queries that read almost identically can differ by a factor of ten thousand in cost, and nothing in the syntax hints at which is which. The other trap is quieter: the clauses do not execute in the order you wrote them, and `NULL` is not a value. Most of the confusing results people get from SQL come from those two facts alone.

The mental model

Ordering at a restaurant rather than cooking. You describe the dish you want; the kitchen decides the pans, the order of operations and whether to prep something in advance. You get enormous leverage from not specifying the how — and you also lose the ability to blame the recipe when it comes out slowly. An index is you having quietly told the kitchen where the ingredients live.

How it works

The mechanism

The evaluation order explains errors that otherwise look arbitrary: ```sql -- FAILS: total does not exist when WHERE runs SELECT price * qty AS total FROM order_items WHERE total > 100; -- WORKS: ORDER BY runs after SELECT SELECT price * qty AS total FROM order_items ORDER BY total DESC; -- WHERE filters rows, HAVING filters groups SELECT customer_id, SUM(price * qty) AS spend FROM order_items WHERE created_at >= '2026-01-01' -- per row, before grouping GROUP BY customer_id HAVING SUM(price * qty) > 500 -- per group, after ORDER BY spend DESC LIMIT 10; ``` Putting the date filter in `HAVING` would give the same answer and read every row of the year before discarding most of them. Same result, far more work — and `EXPLAIN` is where you would see that.

flowchart TD
    F[FROM<br/>pick the tables] --> W[WHERE<br/>filter individual rows]
    W --> G[GROUP BY<br/>collapse into groups]
    G --> H[HAVING<br/>filter the groups]
    H --> S[SELECT<br/>choose and alias columns]
    S --> O[ORDER BY<br/>sort the result]
    O --> L[LIMIT<br/>take a slice]
    W -.->|aliases do not exist yet| S
    S -.->|aliases usable here| O

What people get wrong

WHERE column = NULL finds rows where the column is null.

It matches nothing. Comparison with NULL yields UNKNOWN, and only TRUE passes a WHERE clause. NULL means unknown, so asking whether an unknown equals an unknown cannot return true. Use IS NULL and IS NOT NULL, which are separate operators for exactly this reason.

Rows come back in the order they were inserted.

There is no guaranteed order without ORDER BY. The planner returns rows in whatever order the chosen plan produced them. It often looks like insertion order on small tables, right up until an index is added or the table grows, at which point behaviour changes with no code change.

COUNT(*) and COUNT(column) are the same.

COUNT(*) counts rows; COUNT(column) counts rows where that column is not null. Aggregates skip nulls. On a table with optional emails, COUNT(*) and COUNT(email) answer two different questions, and picking the wrong one produces a report that is quietly wrong rather than obviously broken.

SELECT * is fine, I'll just use the fields I need.

It transfers every column, defeats covering indexes, and breaks silently when someone adds a column. An index containing all the columns a query needs can answer it without touching the table at all. SELECT * guarantees that never happens. It also means adding a BLOB column to a table changes the cost of every existing query against it.

NOT IN and NOT EXISTS behave the same way.

NOT IN returns zero rows if the subquery yields even one NULL. NOT EXISTS does not. NOT IN expands to a chain of not-equals comparisons, and comparing anything to NULL is UNKNOWN, so the whole condition can never be true. This is a silent, valid-looking, empty-result bug — prefer NOT EXISTS.

When not to use it

Deeply nested or schema-less documents where the shape varies per record.

A document store, or a JSON column with an appropriate index. Modelling optional nested structures as dozens of nullable columns fights the relational model.

Full-text search — relevance ranking, stemming, fuzzy matching.

A dedicated search engine, or the database's own full-text index. LIKE with a leading wildcard cannot use a normal index and degrades to a full scan.

Heavy analytical scans over billions of rows for reporting.

A columnar warehouse. Row-oriented storage is optimised for fetching whole rows, which is the wrong shape when you want one column across everything.

Terms

Declarative

— You state the result you want, not the steps to produce it. The planner chooses the steps.

Query planner

— The component that turns your SQL into an execution strategy, using table statistics to estimate costs.

Predicate

— A condition that evaluates to true, false or unknown for a row — the contents of a WHERE clause.

Three-valued logic

— SQL's TRUE / FALSE / UNKNOWN system, which exists because NULL means unknown rather than empty.

Aggregate

— A function collapsing many rows into one value: COUNT, SUM, AVG, MIN, MAX.

Cardinality

— The number of distinct values in a column. Low cardinality makes an index much less useful.

EXPLAIN

— The command that shows the plan the database intends to use. The only reliable way to know a query's cost.

In an interview

Connected ideas

• Joins & Relationships — Inner, left, and the one that silently duplicates all your rows.
• Indexes & Query Plans — Why one query is 2ms and the identical-looking one is 8 seconds — read the plan.

Sources

• PostgreSQL — SELECT
• PostgreSQL — Using EXPLAIN