# SQL Standards

> Use when writing or reviewing the SQL language itself — .sql files, SELECT/JOIN/CTE/window function queries, GROUP BY and aggregation, NULL and three-valued logic, MERGE and UPSERT, DDL and CREATE TABLE, ALTER TABLE migrations and expand/contract, transactions, BEGIN/COMMIT, isolation levels, SELECT FOR UPDATE and deadlocks, parameterized queries and SQL injection in query construction, dynamic identifier quoting, SARGable predicates, EXPLAIN and query plans, ANSI SQL versus PostgreSQL/MySQL/MariaDB/SQL Server T-SQL/Oracle PL-SQL/SQLite dialect differences, .sqlfluff and sqlfluff, shandy-sqlfmt, or hand-written SQL versus ORM- and dbt-generated SQL.

- Skill: `serialexperimentslainnnn/sql-standards` (Agent Skill)
- Install (CLI): `npx skillmds@latest add serialexperimentslainnnn/sql-standards`
- Raw SKILL.md: https://api.skillmd.com/api/skills/serialexperimentslainnnn/sql-standards/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: serialexperimentslainnnn (https://skillmd.com/u/serialexperimentslainnnn)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/serialexperimentslainnnn/sql-standards

---


# SQL language standards

Criteria verified as of **August 2026**. Re-verify on the web before committing to anything (§8).

## 1. Scope and triggers

Applies to **written SQL**: queries, DDL, migrations, transactions, style, linting and testing
of SQL, and to reading plans **in order to decide how the query is written**.
Triggers: `.sql` files, `.sqlfluff`, `SELECT`/`JOIN`/`WITH`/`OVER()`, `GROUP BY`, `MERGE`,
`CREATE TABLE`, `ALTER TABLE`, `BEGIN`/`COMMIT`, `SET TRANSACTION ISOLATION LEVEL`, `FOR UPDATE`,
`EXPLAIN`/`EXPLAIN ANALYZE`/`SHOW PLAN`, `sqlfluff`, `sqlfmt`.
It sets **criteria**, not tutorials.

**Arbitration rule** (this skill's line, and it is written here without ambiguity):
> **If the question changes how the query or the DDL is written, it belongs to `sql-standards`.
> If it changes which engine is chosen, how it is sized, backed up, replicated or restored, it belongs to the engine's skill.
> If it changes the shape of the data model, it belongs to the modelling skill.**

Practical corollary: *"why does this query not use the index?"* belongs here (you rewrite the predicate);
*"which index do I create and what does it cost to maintain?"* and *"why did the engine choose that plan with these
statistics?"* belong to the engine's skill. **This skill cedes modelling, operation and engine tuning; it
keeps the language.**

**Not applicable**: see
- `data-platform-standards` (**parent skill**: PostgreSQL as the relational default, modelling, **indexes
  as objects** —which to create, write cost, partitioning—, replicas, PITR, encryption at rest,
  data retention and classification. Here only **how the query is written so that an existing
  index is usable**).
- `mysql-mariadb-dba-standards`, `oracle-dba-standards`, `sqlserver-dba-standards` (**operation of each
  engine**: parameters, licensing, HA/replicas, RMAN/Data Guard, DBCC/Always On/Query Store,
  binlog, AWR/ASH, instance *tuning*). Here **the dialect differences that change the code**
  you write against them, and nothing else. **Forbidden to duplicate operational criteria here.**
- `data-warehouse-modeling-standards` (**the shape of the analytical model is theirs**: grain, facts,
  dimensions, SCD, layers, canonical metric definition). If the question is *"what does a
  row represent?"*, it is theirs; if it is *"how do I express that SCD2 in SQL without a correlated subquery?"*, it belongs here.
- `data-engineering-standards` (ingestion, orchestration, job idempotency, *backfill*, Parquet,
  freshness; **dbt as a transformation tool and its project are theirs** — here only the criteria
  about the **SQL** that model contains, see §7),
  `lakehouse-standards` (Iceberg/Delta/Hudi table format, catalogue, snapshots, compaction,
  `MERGE` as a table operation and its copy-on-write / merge-on-read cost),
  `analytics-bi-standards` (the dashboard and who decides with it),
  `data-governance-quality-standards` (data ownership, contracts, quality assertions).
- `nosql-standards`, `graph-db-standards` (**including SQL/PGQ and `GRAPH_TABLE`: variable-depth
  traversal is theirs**), `timeseries-db-standards`, `vector-db-standards`,
  `search-engines-standards` (other data models and their languages; here only relational SQL),
  `streaming-cdc-standards` (streaming SQL —Flink SQL, ksqlDB— and event semantics).
- `appsec-standards` (**the AppSec process is theirs**: threat modelling, triage of an SQLi finding,
  choice of SAST/DAST, ASVS. **Here only the code criteria**: how a query is built so
  that injection is impossible — §5).
- `api-design-standards` (the outward contract: **a table is not an API**), the language
  skills —`python-standards`, `typescript-standards`, `go-standards`, `jvm-spring-standards`,
  `dotnet-standards`, `php-standards`, `ruby-standards` (Active Record and `strong_migrations`),
  `elixir-erlang-standards` (Ecto, `changesets` and `Ecto.Multi`), `scala-standards` (Doobie, Slick,
  Quill), `clojure-standards` (`next.jdbc`, HoneySQL), `r-standards` (`dbplyr`), `julia-standards` (`DBInterface`, `LibPQ`)— (the driver, the
  ORM/generator and the specific migration tool; **here the SQL they produce or that you write by
  hand**. That the SQL is generated by a library **does not exempt it** from these criteria: if the generator emits
  a correlated subquery where a window function belonged, the problem is SQL's).

## 2. Default decisions

> Verify the latest version on the web before pinning it in a real project (§8).

| Area | Default | Justifiable alternative / note |
|---|---|---|
| Baseline | **Standard SQL (ISO/IEC 9075)** as the starting line; the dialect is used **consciously** | The current standard is **SQL:2023**, adopted in **June 2023** (ninth edition). No engine implements it in full: writing "standard SQL" and believing it is portable is an error |
| Portability | **Do not pursue it by default**: pick an engine and use its dialect well | Portability is paid for on every query and almost never collected. It is justified only if the product is sold on top of several engines, and then it is declared and tested in CI against all of them |
| Linter | **SQLFluff 4.2.x** (MIT, actively maintained: ~2-3 week cadence in 2026) with a versioned `.sqlfluff` and an explicit `dialect` | The only serious multi-dialect linter that is aware of Jinja/dbt templates (`sqlfluff-templater-dbt`, versioned in parallel). 4.0 introduced an **opt-in** Rust parser/lexer (`sqlfluff[rs]`); the maintainers intend to make it the default in 5.0 |
| Formatter | **sqlfmt** — PyPI package **`shandy-sqlfmt`** (Apache-2.0), a single non-configurable style | Plain `sqlfmt` on PyPI is **a different package, unrelated to the author**: installing the wrong name is a supply error. sqlfmt **is not a linter** (it builds no AST); it coexists with SQLFluff, which also formats (`sqlfluff fix`). Pick **one** of the two as the formatting authority and disable the other's layout rules |
| Flavour of `MERGE` | Only where it exists (see §3) | `MERGE` has been in the standard since SQL:2003 and **does not exist in MySQL, MariaDB or SQLite** as of 2026-08 |
| Migrations | A tool with **versioning and immutable migrations** (Flyway, Liquibase, Alembic, EF Core, golang-migrate) | The specific choice belongs to the language's skill; **the expand/contract criteria of §6 belong here and are not negotiable** |
| Generation | **ORM for CRUD, hand-written SQL for the analytical and the hot paths** | See §7 |

**Dialect differences that really do change the code** (verify per version before using, §8):

| Point | PostgreSQL | MySQL / MariaDB | SQL Server | Oracle | SQLite |
|---|---|---|---|---|---|
| `MERGE` | Since **15**; `RETURNING` since **17** | **Does not exist** → `INSERT ... ON DUPLICATE KEY UPDATE` / `REPLACE` | Yes (long-standing) | Yes (with its own restrictions: cannot update `ON` columns, non-standard `then update … delete` syntax) | **Does not exist** → `INSERT ... ON CONFLICT` (UPSERT, since 3.24, syntax taken from PostgreSQL) |
| `JSON_TABLE` / SQL/JSON | Constructors and `JSON_TABLE` since **17** | Yes in MySQL 8.0+ | Native `JSON` type, JSON indexes and `JSON_CONTAINS` in **SQL Server 2025** (`JSON_TABLE`: **not verified**, §8) | Yes | No (`json_each`/`json_tree`) |
| `GROUP BY ALL` | **Not in 18**; *committed* for **19** | No | No | No | No |
| Identifier quoting | `"x"` (folded to lowercase if unquoted) | `` `x` `` (or `"x"` with `ANSI_QUOTES`) | `[x]` or `"x"` | `"x"` (folded to **uppercase**) | `"x"`, `` `x` ``, `[x]` |
| Strings and concatenation | `\|\|` | `\|\|` is **logical OR** unless `PIPES_AS_CONCAT`; use `CONCAT()` | `+` | `\|\|` | `\|\|` |
| `''` vs `NULL` | Different | Different | Different | **Oracle treats `''` as `NULL`** — a classic trap when porting | Different |
| Row limiting | `LIMIT`/`OFFSET` and `FETCH FIRST` | `LIMIT` | `OFFSET … FETCH` / `TOP` | `FETCH FIRST` (12c+) | `LIMIT` |
| Default isolation | Read Committed | **Repeatable Read** (InnoDB) | Read Committed with **locks** (or RCSI if enabled) | Read Committed over a *snapshot* | Serializable in practice |
| Case sensitivity in data | Sensitive (use `citext`/`ILIKE`/collation) | Depends on the **collation** (`_ci` by default historically) | Depends on the collation | Sensitive | `NOCASE` per column |
| Boolean types | Native `boolean` | `TINYINT(1)` in disguise | `BIT` | Until 23c there was no `BOOLEAN` in a table | Integers 0/1 |

None of this is exhaustive: **before using a recent clause, check the minimum engine version of the
project**, not the product's latest (§8).

## 3. Style and conventions

- **Keywords in UPPERCASE**, identifiers in lowercase `snake_case`. It is the only convention that
  survives Postgres's and Oracle's case folding without forcing you to quote identifiers.
- **Never quote identifiers** unless obliged: a `"MyTable"` forces you to quote it forever and everywhere.
  Names with no spaces, no accents and no reserved words.
- **Names**: tables in **plural or singular — pick one and pin it in `.sqlfluff`**, never both; columns
  without a redundant prefix (`users.id`, not `users.user_id` except in FKs: `orders.user_id` yes); primary
  keys `id`, foreign keys `<singular_table>_id`; booleans `is_`/`has_`; timestamps in `_at`
  (`created_at`) and **always with a time zone** (`timestamptz`), in UTC. No cryptic abbreviations.
- **Formatting**: one clause per line (`SELECT`, `FROM`, `JOIN`, `WHERE`, `GROUP BY`, `ORDER BY`);
  one column per line in long lists; comma **leading** or trailing, chosen and pinned by the
  formatter, not by person. The style decision is taken by the tool (§2), not by review.
- **`SELECT *` FORBIDDEN** outside interactive exploration: it breaks when columns are added, it transfers data
  nobody uses, it prevents *index-only scans* and it makes the diff unreadable. In views and in `CREATE TABLE AS`, it is a
  latent bug.
- **Aliases**: short but meaningful table aliases (`orders o`, not `a`, `b`, `c`); explicit `AS` in
  column aliases. Every column in a query with more than one table is **qualified** (`o.id`).
- **Explicit and mandatory `JOIN`**. **Implicit comma joins are FORBIDDEN** (`FROM a, b WHERE a.id = b.a_id`):
  they mix the join condition with the filter, and a forgotten `WHERE` produces a silent Cartesian product.
  `CROSS JOIN` is written explicitly when it is genuinely wanted. `NATURAL JOIN` and `USING` with many
  columns: vetoed as fragile against schema changes (`NATURAL JOIN` breaks on its own when you add a
  column with a matching name).
- **CTEs (`WITH`) to name the steps**, instead of unreadable nested subqueries. Careful:
  in old PostgreSQL versions the CTE was an **optimisation barrier** (always materialised);
  since PG12 it can be inlined and `MATERIALIZED`/`NOT MATERIALIZED` exist — **verify the
  behaviour in the specific engine and version before assuming it**. A CTE is not free by definition.
- **Comments that explain the why**, not the what: a `-- tenant 0 is excluded because it is the internal
  template` is worth ten lines describing syntax.
- **Declarative and explicit DDL**: `NOT NULL` by default (nullability is justified, not the other way round),
  explicit `DEFAULT`, **named constraints** (`CONSTRAINT ck_orders_total_positive CHECK (...)`) —
  an autogenerated name makes it impossible to write the reverse migration. Foreign keys **declared** with
  their `ON DELETE` thought through; referential integrity lives in the database, not "in the application".
- Types: the **most restrictive that works**. `text`/`varchar` with a considered limit, `numeric`/`decimal` for
  money (**never `float`**), `timestamptz` for instants, `date` for calendar dates, an enum or a catalogue
  table instead of free strings, UUID only if it helps (see the PK criteria in `data-platform-standards`).

## 4. SQL quality and testing

- **CI gates, in increasing order of cost** (all break the build):
  1. `sqlfluff lint` with the repo's `dialect` and rules (and `sqlfluff fix`/`sqlfmt` in pre-commit).
  2. Validation that **every migration applies cleanly and in order** on an empty database.
  3. **Migration applied to a copy with representative data**, measuring time and locks.
  4. Behaviour tests of the queries against the **same engine and version as production**
     (ephemeral container, `testcontainers` or a CI service). **FORBIDDEN to test against SQLite if
     production is PostgreSQL**: the dialects differ in exactly what breaks.
  5. Plan checks on the critical queries (see §6).
- **What is tested**: not the syntax (the engine does that) but the **behaviour**:
  - Happy path **and edges**: empty set, a single element, duplicates, `NULL` in every
    nullable column, boundary values, collation and accents, time zone at the day boundary.
  - **Model invariants as executable assertions**: uniqueness, referential integrity, grain
    (one row per X), valid ranges. Formulation and ownership in `data-governance-quality-standards`;
    here, that they exist and run.
  - **Expected errors**: constraint violation, concurrency conflict, deadlock, timeout.
  - Every query fixed for a bug leaves a **regression test with the data that reproduced it**.
- Test data **built in the test**, not a production dump (personal data + non-determinism).
  Zero dependence on row order: without `ORDER BY`, order **does not exist** — asserting it is a flaky test.
- Review: an `ALTER TABLE` and a `DELETE`/`UPDATE` without a bounded `WHERE` are reviewed as production
  code, with the plan and the number of affected rows in the PR.

## 5. Security: SQL injection is the axis

**Parameterised queries, always, no exceptions.** A *placeholder* (`$1`, `?`, `:name`) is not a
text template: the value travels **outside** the statement and the engine never interprets it as code.
Everything else is a variant of concatenation.

- **FORBIDDEN** to build SQL with concatenation, interpolation (`f"..."`, `${}`, `+`), the language's or the
  engine's `printf`/`format` with input data. PostgreSQL's `format()` and misused `sp_executesql` are
  **the real injection route in 2026**, not the tutorials' `' OR 1=1 --`: the team believes it "uses the
  ORM" and hides a `raw()`/`.query()` with a concatenated part.
- **Escaping by hand is not a defence**: it depends on the encoding, the collation, the engine's
  mode (`NO_BACKSLASH_ESCAPES`) and on nobody forgetting a path. The only defence is that the data is not part
  of the statement.
- **Identifiers cannot be parameterised**, and that is where the real hole is. When you genuinely need
  a dynamic table, column or ordering name:
  1. **First, avoid it**: a **whitelist** mapping the user's input to a literal identifier
     written in the code (`{"date": "created_at", "amount": "total"}`) solves 95 % of the
     cases. If the value is not in the map, it is an error, not an identifier.
  2. If there is no alternative, **identifier quoting via the driver or the engine**, never by hand:
     `quote_ident()`/`format('%I')` in PostgreSQL, `QUOTENAME()` in SQL Server,
     `DBMS_ASSERT.ENQUOTE_NAME` in Oracle, or the driver's identifier helper
     (`psycopg.sql.Identifier`, `sqlalchemy.sql.quoted_name`, `Sequelize.escapeIdentifier`…).
  3. Dynamic `ORDER BY`: **whitelist only**; the direction (`ASC`/`DESC`) too, never straight from the
     parameter. `ORDER BY <number>` with user input is injection under another name.
- **Dynamic SQL inside the engine** (`EXECUTE`, `sp_executesql`, `EXECUTE IMMEDIATE`): same criteria —
  bound parameters (`USING`, `sp_executesql`'s `@params`), never a built-up string. Vetoed:
  `EXEC(@sql)` with `@sql` built by concatenation.
- **Least privilege as a second layer**: the application connects with a role **with no DDL, no `SUPERUSER`,
  no access to other schemas**, and with per-table/per-column permissions. A read-only role for read
  queries. **RLS** (PostgreSQL, SQL Server) when tenant isolation is a security
  requirement and not a `WHERE tenant_id = ...` convention that somebody will eventually forget. An
  application user that can `DROP TABLE` turns an SQLi into a catastrophe instead of a leak.
- **Avoidable blindness**: engine errors **never** to the client (they reveal the schema and enable error-based
  injection); mandatory `LIMIT` on every exposed query; `statement_timeout`/command timeout
  always configured (time-based blind SQLi needs long queries).
- **Sensitive data in the query**: no secrets and no personal data in literals that end up in the
  slow statement log, in `pg_stat_statements` or in a saved plan. Parameters help here too.
- **Auditing and traceability**: `application_name`/context comment on the session to attribute a
  query to a service; the finding management process, in `appsec-standards`.

## 6. SQL correctness and performance

**`NULL` and three-valued logic — the source of silent bugs**:
- `NULL = NULL` is `UNKNOWN`, not `TRUE`. Compare with `IS NULL` / `IS NOT NULL`, or
  `IS [NOT] DISTINCT FROM` to compare treating `NULL` as a value.
- **`NOT IN (subquery)` with a single `NULL` returns zero rows**. Use `NOT EXISTS`, which also usually
  optimises better. It is the most expensive SQL bug there is.
- Aggregates **ignore `NULL`** (`COUNT(col)` ≠ `COUNT(*)`; `AVG` divides by the non-nulls).
- `WHERE` filters by `TRUE`, not by "not false": a row with `UNKNOWN` disappears; in a `CHECK`, by contrast,
  `UNKNOWN` **passes**. The asymmetry is real and must be kept in mind when writing constraints.
- `LEFT JOIN` + a condition on the right-hand table in the `WHERE` **turns it into an `INNER JOIN`**: the
  condition goes in the `ON`.
- Design: **`NOT NULL` by default**; a `NULL` must mean something declared, not "we did not know".

**Sets, not loops**:
- **Think in sets**: a query that solves the whole problem almost always beats N queries
  from the client. **N+1 is vetoed** in all its forms (ORM loop, a `for` that queries
  per row, a cursor doing one `UPDATE` per iteration).
- **CTEs and window functions instead of correlated subqueries and client-side logic**:
  `ROW_NUMBER()`/`RANK()` with `PARTITION BY` for "top N per group"; `LAG`/`LEAD` to compare with the
  previous row; `SUM() OVER (ORDER BY ... ROWS BETWEEN ...)` for running totals; `FILTER (WHERE ...)` or
  `CASE` inside the aggregate to pivot. Pulling the data to the client to sort, group or compare
  is the default antipattern and is usually two orders of magnitude more expensive.
- **Recursive CTE** (`WITH RECURSIVE`) for hierarchies and bill-of-materials explosion, **with an explicit
  depth cut-off**. If the traversal is of variable depth with no reasonable bound, the question is no
  longer SQL: see `graph-db-standards`.
- `GROUP BY`: group by the **real** columns, not by ordinal position (`GROUP BY 1, 2` is convenient in
  exploration and fragile in production). MySQL with `ONLY_FULL_GROUP_BY` disabled allows selecting
  non-aggregated columns and returns an **arbitrary** value: enable strict mode and treat any
  query that depends on that behaviour as a bug. `HAVING` filters on aggregates; filtering
  individual rows in `HAVING` instead of `WHERE` processes more than necessary.
- `UNION` deduplicates (and sorts to do so): use **`UNION ALL`** unless deduplication is the objective.
- `DISTINCT` as a patch for a `JOIN` that multiplies rows is a sign of a badly built query:
  fix the `JOIN` or use `EXISTS`.

**SARGability — how the query is written so the index is usable**:
- **A function over the indexed column kills the index**: `WHERE UPPER(email) = $1`,
  `WHERE date(created_at) = $1`, `WHERE col + 0 = $1`. Rewrite the predicate over the bare column
  (`created_at >= $1 AND created_at < $2`) or create an expression index — which index, in the engine's skill.
- `LIKE '%something'` (leading wildcard) does not use a B-tree index. It is a search problem, not an SQL one: see
  `search-engines-standards`.
- **Type mismatch** (comparing `varchar` with a number, `int` with `bigint` in some engines) causes
  an implicit conversion and discards the index. Type the parameter correctly in the driver.
- `OR` across different columns usually prevents a good plan: a `UNION ALL` of two indexed branches wins.
- **Selective** predicates first conceptually (even though the optimiser reorders them): filter in the engine,
  do not fetch and discard.
- **Keyset pagination** (`WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT n`),
  not a large `OFFSET`: `OFFSET 100000` reads and discards 100,000 rows.

**Transactions and concurrency**:
- **Short transactions with an explicit scope**: `BEGIN` … `COMMIT` around the atomic unit, and
  **zero external I/O inside** (HTTP calls, sending mail, waits). A transaction left open while
  waiting on a third party is a lock waiting to happen.
- **Isolation levels and which anomaly each one allows** (ANSI, with the caveat that every engine
  implements them in its own way — verify per engine and version, §8):

  | Level | Dirty read | Non-repeatable read | Phantom | Write skew |
  |---|---|---|---|---|
  | Read Uncommitted | possible | possible | possible | possible |
  | Read Committed | no | possible | possible | possible |
  | Repeatable Read | no | no | possible depending on the engine (**InnoDB and PostgreSQL's snapshot avoid them**) | **possible** |
  | Serializable | no | no | no | no |

  Consequences you do decide: the default is not the same in every engine (table in §2); in
  PostgreSQL, `REPEATABLE READ` and `SERIALIZABLE` **abort the transaction with a serialization error** and
  **the application must retry** — code that does not handle that retry is broken by design. `SERIALIZABLE`
  is not "slower" in the abstract: it is correct and costs retries; raise it when the invariant
  crosses rows (write skew), and lowering it requires justifying why.
- **`SELECT ... FOR UPDATE`** to lock rows you are going to modify (and `FOR NO KEY UPDATE`/`FOR SHARE`
  as appropriate); `SKIP LOCKED` for queues and distributed work, `NOWAIT` when you prefer to fail fast rather than
  wait. **It does not replace an appropriate isolation level**: it locks what you read, not what does not exist yet.
- **Deadlocks**: they are prevented by accessing resources **always in the same order** and keeping
  transactions short; they are **handled** with retry-with-backoff in the client, because the engine kills
  one of the two victims and that is normal, not exceptional. A recurring deadlock between the same two
  statements is an access-order bug, not a capacity problem.
- Idempotency: use uniqueness constraints + `ON CONFLICT`/`MERGE` instead of "check and then
  insert" — the gap between the `SELECT` and the `INSERT` is a race condition, always.

**Backwards-compatible DDL and migrations (expand/contract)** — mandatory:
1. **Expand**: add the new thing in a compatible way (nullable column or one with a default, new table, new
   index). Version N-1 of the code keeps working.
2. **Migrate**: fill the data **in bounded batches and short transactions**, not in a mass `UPDATE` that
   locks the table and bloats the WAL/undo.
3. **Deploy** the code that uses the new thing and writes to both places if necessary.
4. **Contract**: in a **later release**, remove the old one.

- **Never a migration that breaks version N-1** during a rolling deployment: renaming a column,
  changing its type or dropping it in the same release as the code is a guaranteed outage.
- **What locks, locks**: an `ALTER TABLE` that rewrites the table, adding an FK or a `CHECK` that validates
  everything, creating an index without the concurrent variant. Use the engine's online variants
  (`CREATE INDEX CONCURRENTLY`, `NOT VALID` + `VALIDATE CONSTRAINT`, `ALGORITHM=INPLACE`/`LOCK=NONE`,
  `ONLINE=ON`, online change tools) and **set a short `lock_timeout`**: a migration waiting on
  a lock queues everything behind it and takes the service down before touching a single byte.
- Migrations **immutable once applied** (a new migration to correct, never editing the applied one) and
  **with a thought-through way back** —or explicitly declared as irreversible—. Every destructive migration
  is reviewed with a name attached.

**Execution plans**:
- Read the **real** plan, not the estimated one: `EXPLAIN (ANALYZE, BUFFERS)` in PostgreSQL, `EXPLAIN ANALYZE` in
  MySQL 8+, the actual plan in SSMS or `SET STATISTICS IO/TIME` in SQL Server, `DBMS_XPLAN.DISPLAY_CURSOR` in Oracle.
- What to look at, in this order: **divergence between estimated and actual rows** (a symptom of statistics or of a
  non-estimable predicate), the node that dominates the time, sequential scans over large tables,
  *nested loops* with many iterations, sorts and hashes that spill to disk.
- **Measure with representative data**: a plan over 100 rows says nothing about the plan over 100 million.
- When the plan is bad despite a well-written query, **the problem stops belonging to this skill**:
  statistics, memory parameters, *plan cache*, *parameter sniffing* and index suggestions belong to the
  engine's skill. **Optimiser *hints* are a last resort and come with an expiry date**: they freeze a
  decision the engine would revisit on its own.

## 7. Long-term sustainability

- **Hand-written vs. generated SQL**:
  - **ORM**: correct and preferable for CRUD by primary key, unit of work and aggregate mapping.
    It stops being so as soon as the query has more than two `JOIN`s, aggregation or windows: there you write SQL
    by hand (or with a typed builder like sqlc/jOOQ/explicit `sqlalchemy.select`). **Generated SQL
    is reviewed and measured just like written SQL**: "the ORM generates it" is not a plan justification.
  - **dbt/SQLMesh**: the model is SQL and **is subject to this skill** (style, `NULL`, joins, windows,
    SARGability). The framework's orchestration, materialisation, incrementality and tests belong to
    `data-engineering-standards`. Jinja that builds SQL by interpolating values is **the same
    injection as §5** when the input is not a literal from the repo.
  - Stored procedures: business logic in the engine **only with a declared reason** (transactional
    integrity impossible outside, prohibitive network cost). Accepted cost: worse versioning, testing and
    deployment, and coupling to the engine.
- **Views**: useful for encapsulating a canonical query; dangerous when stacked (a view on a view on a
  view produces plans impossible to reason about). One level at most unless justified. Materialised views
  with a **declared** refresh policy, not an improvised one.
- SQL lives in the repo, in `.sql` files or in the model, versioned and reviewed. SQL saved only in the
  engine, in a BI tool or in somebody's history **does not exist**.
- Cadence: on a major engine version upgrade, re-read the dialect's *breaking changes* and re-measure
  the critical queries — the optimiser changes and some plan gets worse, always.

**Prohibition list (veto):**
- ❌ Any SQL built by concatenation or interpolation with input data. No exceptions.
- ❌ A dynamic identifier without a whitelist or driver/engine quoting.
- ❌ `SELECT *` in production code, in views or in `CREATE TABLE AS`.
- ❌ **Implicit comma joins** (`FROM a, b WHERE ...`) and `NATURAL JOIN`.
- ❌ `NOT IN (subquery)` over a nullable column (use `NOT EXISTS`).
- ❌ `UPDATE`/`DELETE` without a bounded `WHERE`, or executed without having first seen the equivalent `SELECT`.
- ❌ `DISTINCT` to paper over rows duplicated by a badly built `JOIN`.
- ❌ `float`/`double` for money. Dates as text. Implicit time zones.
- ❌ A client-side loop executing one query per row (N+1) or a cursor with an `UPDATE` per iteration.
- ❌ A large `OFFSET` as pagination.
- ❌ A migration that breaks version N-1 of the code, or a locking `ALTER TABLE` without `lock_timeout` and without
  an online variant. Editing an already-applied migration.
- ❌ Constraints and foreign keys "managed by the application" instead of declared.
- ❌ Depending on row order without `ORDER BY`, or on non-aggregated columns with `ONLY_FULL_GROUP_BY`
  disabled.
- ❌ A transaction left open around a network call. A long transaction "because it is simpler".
- ❌ Connecting the application with a role holding DDL or superuser privileges.
- ❌ Optimiser *hints* as a permanent solution with no review date.
- ❌ Testing against a different engine from production's (SQLite in CI, PostgreSQL in prod).

## 8. Mandatory web verification

Before pinning clauses, versions or tools, **verify online** (WebSearch/WebFetch), with the
**engine's release notes** as the primary source and third-party compatibility tables only as a
hint — their columns usually indicate *last tested version*, not *version since which it exists*, and reading them
the wrong way round produces false claims (it happened to this document during its drafting with `MERGE`):
1. **Standard**: SQL:2023 (ISO/IEC 9075) is the current edition; Part 16 (SQL/PGQ) is already listed as
   *"to be revised"*, so there is a revision under way. **There is no confirmed "SQL:2026" edition as of 2026-08**:
   check the activity of ISO/IEC JTC 1/SC 32 WG3 before citing one.
2. **The project's minimum engine version** (not the product's latest) for every clause you use:
   `MERGE` (PostgreSQL 15+, `RETURNING` since 17; **non-existent in MySQL, MariaDB and SQLite** as of 2026-08),
   `JSON_TABLE` and SQL/JSON constructors (PostgreSQL 17+), `GROUP BY ALL` (**not in PostgreSQL 18**,
   *committed* for 19; available in DuckDB, Snowflake, Databricks and BigQuery), `FILTER`, `MATCH_RECOGNIZE`,
   `GRAPH_TABLE`/SQL/PGQ, windows with `GROUPS`/`EXCLUDE`.
3. **Declared gaps, not verified as of Aug 2026**: whether `JSON_TABLE` exists in **SQL Server 2025** (the
   native `JSON` type, JSON indexes, `JSON_CONTAINS` and the `REGEXP_*` functions are verified);
   the status and known limitations of `MERGE` in SQL Server; `MERGE` support in MariaDB
   independently of MySQL; the isolation level defaults in the **specific version** of each engine
   (the §2 table reflects the classic behaviour and must be confirmed per version).
4. **Tools**: SQLFluff (4.2.2 as of 2026-06, **MIT**, actively maintained) — check whether 5.0 has already made
   the Rust engine the default and which rules change severity; **sqlfmt**, installed as **`shandy-sqlfmt`**
   (Apache-2.0, 0.31.0 as of 2026-08) — confirm the package name before installing it. Also verify
   that neither has changed licence or entered maintenance mode: the catalogue already has
   precedents (Trivy changed licence; gitleaks declared itself *feature complete* and its action requires a commercial
   licence for organisations from v2).
5. **CVEs and advisories** for the engine and the driver before pinning a version (osv.dev / GitHub Advisories).
6. Before a major engine upgrade, the **dialect's breaking changes** in the official notes — not from
   memory or from an unverified third-party blog.

If the web contradicts this document, **the web wins** — flag the discrepancy.