SQL is a declarative, set-oriented language

SQL asks for a result, not a procedure. You describe the set of rows you want and the database's planner decides how to produce it — which index to use, which join algorithm to run, in what order. This is the single most important adjustment for engineers arriving from imperative languages: a loop over rows is almost always the wrong instinct, and the query that reads like a description of the answer usually outperforms the one that reads like a program.

Because the language is declarative, the order you write clauses is not the order the database evaluates them. The logical processing order is: FROM and its joins, then WHERE, then GROUP BY, then HAVING, then SELECT (including window functions and column aliases), then DISTINCT, then ORDER BY, and finally LIMIT/OFFSET. Almost every confusing error message follows from this order. An alias defined in SELECT is not visible to WHERE, because WHERE ran first. An aggregate cannot appear in WHERE, because grouping has not happened yet — that filter belongs in HAVING. The planner is free to reorder physical execution however it likes, provided the answer matches this logical model.

Joins decide which rows exist

INNER JOIN keeps only rows with a match on both sides. LEFT JOIN keeps every left row and fills the right side with NULLs when nothing matches; RIGHT JOIN is its mirror and FULL JOIN keeps unmatched rows from both. CROSS JOIN produces every combination, so a cross join of 10,000 and 5,000 rows is fifty million rows — usually a mistake, occasionally exactly what a calendar or matrix query needs.

Two distinctions cause most real bugs. The first is where you filter a left-joined table. A predicate on the right table in ON restricts what counts as a match and preserves unmatched left rows; the same predicate in WHERE runs after the join and discards the NULL-extended rows, silently converting your LEFT JOIN into an inner join. The second is join fan-out. Joining to a table with several matching rows multiplies the left row, and any SUM computed afterwards is inflated. If a total looks too large after adding a join, fan-out is the first thing to check — aggregate the child table first, or use EXISTS when you only need to test for existence rather than pull columns.

NULL is unknown, not empty

SQL uses three-valued logic: TRUE, FALSE and UNKNOWN. Any comparison with NULL yields UNKNOWN, and WHERE keeps only rows where the predicate is TRUE. So WHERE x = NULL returns nothing, ever, and so does WHERE x <> NULL — you must write IS NULL or IS NOT NULL, or use IS DISTINCT FROM when you want NULL to compare as an ordinary value.

The consequence that catches experienced engineers is NOT IN against a subquery that can return NULL. x NOT IN (1, 2, NULL) is never TRUE — it is UNKNOWN whenever x is not 1 or 2, because the database cannot rule out that the NULL is equal to x — so the query returns zero rows with no error and no warning. NOT EXISTS does not have this behaviour and is the safer anti-join. Aggregates ignore NULLs except COUNT(*): COUNT(*) counts rows, COUNT(col) counts non-NULL values of that column, and the difference between them is a quick NULL audit. SUM over zero rows returns NULL rather than 0, which is why report queries wrap it in COALESCE.

Grouping and set operations

GROUP BY collapses rows into one row per distinct grouping key. The portable rule is that every selected expression must either appear in GROUP BY or sit inside an aggregate. Some engines also allow columns they can prove are functionally dependent on the grouping key — PostgreSQL recognises this in limited cases such as grouping by a table's primary key — while permissive modes that cannot prove a dependency may return an arbitrary row. HAVING filters groups after aggregation, WHERE filters rows before it; a predicate on grouping columns that applies to the whole input belongs in WHERE, where it can reduce the rows reaching aggregation. Conditional aggregation — COUNT(*) FILTER (WHERE status = 'failed'), or a portable SUM(CASE WHEN ... THEN 1 ELSE 0 END) — computes several different counts in one pass instead of joining the table to itself repeatedly.

Set operations stack result sets vertically and require matching column counts and compatible types. UNION removes duplicates, which costs a sort or hash; UNION ALL does not and should be your default whenever duplicates are impossible or acceptable. INTERSECT and EXCEPT complete the set algebra.

Subqueries, CTEs and readability

A scalar subquery returns one row and one column and can appear anywhere a value can. A correlated subquery references the outer row and is evaluated per row conceptually, though planners frequently rewrite it into a join. EXISTS stops at the first match, which makes it the right tool for existence tests. Common table expressions (WITH) name intermediate results and turn a nested pyramid into a readable sequence of named steps; recursive CTEs walk hierarchies and graphs. Treat a CTE as an organisational tool whose performance you still verify — in some engines and versions it acts as an optimisation fence and is materialized rather than inlined.

Integrity belongs in the schema

Constraints are the only guarantees that survive every application, script and console session that touches the database. NOT NULL states that a value is required. A PRIMARY KEY is unique and not null and identifies the row. A UNIQUE constraint enforces uniqueness but, in standard SQL, permits multiple NULLs — because two unknowns are not known to be equal. FOREIGN KEY enforces referential integrity, and its ON DELETE action is a real design decision: RESTRICT/NO ACTION refuses to orphan rows, CASCADE deletes children with the parent, and SET NULL keeps the child while clearing the link. CHECK encodes row-level invariants such as a non-negative quantity. Application-level validation is a usability feature; the constraint is the guarantee.

Writing SQL that is safe and predictable

Never build SQL by concatenating user input. Parameterized statements send the query text and the values separately, so a value can never be reinterpreted as syntax — this is the actual fix for SQL injection, and unlike escaping it does not depend on remembering to apply it correctly every time. It usually improves plan reuse as a side effect.

Two habits prevent a surprising share of production incidents. Result order is not guaranteed without ORDER BY — not by insertion order, not by primary key, not by whatever the last run happened to return. A LIMIT without ORDER BY is genuinely nondeterministic, and pagination built on it will skip and repeat rows. Deep OFFSET pagination also degrades because the database must generate and discard every skipped row; keyset pagination (WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 50) stays fast at any depth. SELECT * in application code is fragile: it breaks or silently changes meaning when a column is added, renamed or reordered, moves data you do not use across the network, and usually prevents an index-only scan unless the index happens to contain every selected column. Name your columns.

Finally, choose types deliberately. Use an exact numeric/decimal for money — binary floating point cannot represent 0.1 exactly and will drift over repeated arithmetic. Store timestamps in UTC with a time-zone-aware type and convert at the edges. Prefer a native date, boolean or JSON type over a string that merely looks like one, because the type is what lets the database validate values, compare them correctly and use an index on them.

Worked example: LIMIT 50 OFFSET 50 without ORDER BY

Invoices table, 10,000 rows, page size 50.

pagination	page 2 vs page 3	invoice 4419
LIMIT 50 OFFSET 50, no ORDER BY	can skip or repeat	may appear twice
keyset WHERE (created_at, id) < last ORDER BY created_at DESC, id DESC LIMIT 50	stable	once

Result order is not insertion order. OFFSET also walks and discards every skipped row.