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).

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

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 JOINs, 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.