sql-skills-plugin

Guides the core write statements — INSERT, UPDATE, DELETE — as set-based operations the database serializes, not row-by-row loops. Teaches the INSERT forms (single-row VALUES, multi-row VALUES in ONE statement, INSERT ... SELECT for bulk copy, DEFAULT VALUES) and bans the nightly job that fires 100k single-row INSERTs instead of one. Centers the

Guides correct, portable text matching in SQL — LIKE with its `%` (any sequence) and `_` (any single character) wildcards, the ESCAPE clause for literal `%`/`_`, and the central trap that LIKE's case sensitivity is decided by the active COLLATION and therefore silently differs across engines (case-SENSITIVE in standard SQL and PostgreSQL, case-INSENSITIVE under the common MySQL/SQL Server default collations, case-insensitive for ASCII only in SQLite) so the identical query returns different rows on different databases. Teaches the explicit fix — COLLATE per-operation or LOWER() on both sides — plus SIMILAR TO as the only SQL-standard regular-expression operator versus the non-standard vendor regex (`~`/`~*`, REGEXP/RLIKE), ILIKE as a non-standard PostgreSQL extension, and COLLATE for deterministic ordering and accent sensitivity. Auto-invokes when writing or editing a LIKE/ILIKE/SIMILAR TO/REGEXP/`~`/GLOB predicate, a COLLATE clause, case-insensitive or accent-insensitive search, or on "case-insensitive searc

The portability index for SQL — maps each standard feature to whether the readable engines (PostgreSQL, SQLite, MySQL/MariaDB, plus notes on SQL Server / Oracle / DuckDB) support it and how they spell it. Covers `OFFSET … FETCH` vs `LIMIT`, `GENERATED AS IDENTITY` vs `SERIAL`/`AUTO_INCREMENT`/`IDENTITY(1,1)`, `MERGE` vs `ON CONFLICT` vs `ON DUPLICATE KEY UPDATE`, `||` vs `+` vs `CONCAT()`, `IS DISTINCT FROM` vs `<=>`, `LISTAGG`/`STRING_AGG`/`GROUP_CONCAT`, `INFORMATION_SCHEMA` vs `sqlite_master`/`PRAGMA`/`SHOW`, standard JSON functions vs `->`/`->>`, `LATERAL` vs `CROSS/OUTER APPLY`, `EXCEPT` vs `MINUS`, identifier quoting and `ANSI_QUOTES`, `BOOLEAN` vs `BIT` vs `TINYINT(1)`, `CURRENT_TIMESTAMP` vs `NOW()`/`GETDATE()`/`SYSDATE`, default isolation levels, `CAST` vs `::`, and which advanced features (temporal tables, `MATCH_RECOGNIZE`, SQL/PGQ) live where. Auto-invokes when porting SQL between engines, targeting a specific database, choosing between a standard and a vendor spelling, or on "does Postgres/MySQL/

Guides `MATCH_RECOGNIZE` (SQL:2016 row pattern recognition) — regex-style pattern matching across ordered rows for time-series problems (V-shapes/dip-and-recovery, trend reversals, threshold breaches, complex sessionization) — instead of convoluted self-joins or nested LAG/CASE window gymnastics. Covers the fixed clause order (PARTITION BY / ORDER BY / MEASURES / ONE ROW|ALL ROWS PER MATCH / AFTER MATCH SKIP / PATTERN / DEFINE), PATTERN regex quantifiers and greedy-vs-reluctant matching, pattern-variable conditions, MEASURES with RUNNING vs FINAL semantics, and the AFTER MATCH SKIP modes that control overlapping matches. LOW PORTABILITY — established only on Oracle, Trino, Snowflake, Flink, Vertica, and DB2; absent from deployed PostgreSQL (landed only in 18), MySQL 8, MariaDB (<12.3), and SQLite (<3.53). Auto-invokes when writing or editing time-series pattern detection, row-sequence matching, trend-reversal/dip detection, complex multi-state sessionization, or "find this shape/sequence in rows" requests. CA

Guides SQL:2023 SQL/PGQ (ISO/IEC 9075-16, Part 16) — define a property graph as a metadata overlay over existing relational tables with `CREATE PROPERTY GRAPH ... VERTEX TABLES (...) EDGE TABLES (...)`, then query it with `GRAPH_TABLE (graph MATCH (a)-[e]->(b) WHERE ... COLUMNS (...))` using ASCII-art vertex/edge patterns in the FROM clause — and explains its relationship to the standalone GQL language (ISO/IEC 39075:2024), with which it shares the graph-pattern sub-language (GPML). A forward-looking reference for "graph queries without a graph database." BLEEDING-EDGE, VERY LOW PORTABILITY — as of 2026 only Oracle Database 23ai ships it; PostgreSQL has out-of-tree patches only; SQLite and MySQL have nothing, so the portable way to traverse graph-shaped relational data is a recursive CTE. Auto-invokes when writing or editing graph-pattern queries over relational data, `GRAPH_TABLE`/`CREATE PROPERTY GRAPH`, friend-of-friend / reachability / recommendation traversals, or on "can I do graph / Cypher-style querie

Guides SQL:2011 temporal tables — system-versioned tables (`PERIOD FOR SYSTEM_TIME` + `WITH SYSTEM VERSIONING`) that make the engine auto-record a full history so you query past states with `FOR SYSTEM_TIME AS OF / FROM..TO / BETWEEN / ALL` instead of hand-rolling trigger-based history tables, application-time period tables (`PERIOD FOR <name>`, `UPDATE/DELETE ... FOR PORTION OF`, `WITHOUT OVERLAPS`) for valid-time, and the system-time vs valid-time vs bitemporal distinction. LOW PORTABILITY — supported in MariaDB 10.3+, SQL Server 2016+, IBM DB2, SAP HANA, and (via Flashback) Oracle, but NOT in PostgreSQL (needs an extension), MySQL, or SQLite (need manual modeling); even among supporters the DDL diverges, so confirm engine support before recommending. Auto-invokes when writing or editing audit-history / "as-of" / point-in-time queries, system- or valid-time period tables, slowly-changing-dimension history, temporal primary keys, or "track every change" / "what did this row look like last March" requests. Ow

Guides the two performance habits that matter most — think in sets, not rows, and measure the plan instead of guessing. Replaces the N+1 / RBAR ("row-by-agonizing-row") antipattern — application code looping to issue one query per row, or a correlated per-row subquery — with a single set-based query (`JOIN`/`IN`/`VALUES`/`LATERAL`). Teaches reading a query plan as a concept — the plan tree, sequential/full scan vs index access (seek), estimated cost and estimated rows — and using `EXPLAIN ANALYZE` (Postgres) / `EXPLAIN QUERY PLAN` (SQLite) to get actual rows and time, where a large gap between estimated and actual rows is the tell of a bad estimate (usually stale statistics) that drives a bad join order. Warns that a plan fine on 100 dev rows can die at 10M, so you must test at realistic scale, and that set-based does not mean one giant unreadable "Spaghetti Query." Auto-invokes when writing or editing per-row query loops in application code, a correlated per-row subquery, `EXPLAIN`/`EXPLAIN QUERY PLAN` outpu

Guides portable index design and the sargability rule — an index is a sorted B-tree, so the database can use it only when the predicate leaves the indexed column bare. Bans the recurring index-killers — wrapping an indexed column in a function or expression (`WHERE LOWER(email) = …`, `WHERE DATE(ts) = …`, `WHERE col + 0 = …`) and leading-wildcard `LIKE '%term'`, both of which force a full table scan. Teaches composite-index column order (equality columns first, then the one range/sort column), the leftmost-prefix rule, covering indexes / index-only scans, how one index can serve `WHERE` + `ORDER BY` + `GROUP BY`, and the "index shotgun" antipattern weighed against the per-write maintenance cost of every index. Auto-invokes when writing or editing `CREATE INDEX`, a slow `WHERE`/`ORDER BY`/`JOIN`/`GROUP BY`, a function or arithmetic applied to a filtered column, a `LIKE` pattern, or on "why is this query slow" / "what index do I need" / "this query does a full table scan" requests. The highest-leverage performa

Guides the standard SQL access-control model — `GRANT` adds privileges, `REVOKE` removes them, roles are the grant targets (a role is a user or a group), and access is default-deny so every privilege must be explicitly granted. Drives toward least privilege — the application connects as a dedicated login role holding ONLY the privileges it needs (`SELECT/INSERT/UPDATE/DELETE` on specific tables), never as a superuser (which bypasses all permission checks) and never with `GRANT ALL` broadly. Covers roles and role hierarchies (`GRANT role TO role`), `WITH GRANT OPTION` and its cascade risk, why ownership is not the same as grants (an owner's right to modify or destroy an object is inherent and cannot be revoked), and the `PUBLIC` pseudo-role pitfalls. Flags that SQLite has NO privilege model at all — access is the OS file permissions on the database file. Auto-invokes when writing or editing `GRANT`/`REVOKE`/`CREATE ROLE`/`CREATE USER`, designing database user/role setup, deciding which credentials an applicati

Guides the SQL-statement surface of transactions — wrap any multi-statement invariant or check-then-act sequence in `START TRANSACTION`/`COMMIT`/`ROLLBACK` so it is atomic (all-or-nothing), use `SAVEPOINT`/`ROLLBACK TO` for partial rollback, set the isolation level with `SET TRANSACTION ISOLATION LEVEL`, and know the four standard level names (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), autocommit, READ ONLY/DEFERRABLE, and that DDL is not transactional everywhere (MySQL implicitly commits on DDL; PostgreSQL does not). Names the standard anomaly catalog (dirty read, non-repeatable read, phantom read, serialization anomaly) in one paragraph and routes ALL isolation theory — which level permits which anomaly, snapshot isolation, serializability, write skew — to the MVCC plugin. Auto-invokes when writing or editing `BEGIN`/`START TRANSACTION`/`COMMIT`/`ROLLBACK`/`SAVEPOINT`, `SET TRANSACTION`, a multi-statement write sequence, a debit/credit or check-then-act flow, or on "should this be in

Guides the canonical "gaps and islands" pattern family — detecting unbroken runs of consecutive values (islands — active streaks, sessions, contiguous date/number ranges) and the missing stretches between them (gaps) — with the portable set-based solution instead of brittle self-joins or procedural row-by-row loops. Centerpiece is the ROW_NUMBER-difference trick — `grp = value - ROW_NUMBER() OVER (ORDER BY value)` is constant within a consecutive run, so GROUP BY grp collapses each run to its MIN/MAX boundaries (DENSE_RANK variant for runs within categories). Covers gaps via `LEAD(value) <> value + 1`, merging adjacent/overlapping ranges with a running MAX(end), and sessionization with a time-gap threshold via a running SUM of a new-session flag. Points to MATCH_RECOGNIZE where window logic gets unwieldy. Auto-invokes when writing or editing queries for consecutive sequences, streaks/runs, longest-active-streak, sessionization, collapsing or merging adjacent date/number ranges, finding missing IDs/dates, or "

Guides portable relational schema design — normalize to remove the insertion, update, and deletion anomalies (the 1NF→BCNF ladder), choose natural vs surrogate keys deliberately, model many-to-many with a junction table, and denormalize only with a stated reason. Bans the structural antipatterns that no constraint can later rescue — comma-separated value lists ("Jaywalking", a First Normal Form violation), Entity-Attribute-Value (EAV) key-value rows instead of columns, adjacency-only "Naive Trees" that cannot query a subtree, and polymorphic/promiscuous foreign keys that carry no referential integrity. Auto-invokes when designing tables or schemas, writing CREATE TABLE for a new model, modeling a hierarchy/tree or a many-to-many relationship, storing a list or "flexible attributes" in a column, choosing a primary key, or on "is this schema/data model good", "review my schema", or "how should I store X" requests. Pure design theory — engine-independent and fully portable.

Guides readable, reviewable SQL and the one genuine correctness trap in formatting — `'single quotes'` are string literals and `"double quotes"` are delimited identifiers per the SQL standard, so `WHERE name = "John"` references a column named John (error on PostgreSQL, silent misbehavior on MySQL until ANSI_QUOTES flips it). Covers keyword casing (UPPER keywords, lower names), snake_case identifier naming so columns never need permanent double-quoting, why a CamelCase name forces forever-quoting via case-folding, reserved-word collisions, leading-vs-trailing comma style, and multi-line layout instead of single-line mega-queries. Auto-invokes when writing or editing SQL with string-vs-identifier quoting, choosing identifier names, formatting/laying out a query, or on "clean up / format this SQL" requests. A foundation-level style policy.

Guides the two standard SQL ways to let the database derive a column value for you — `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` for surrogate keys instead of vendor `SERIAL`/`AUTO_INCREMENT`/`IDENTITY(1,1)`, and `GENERATED ALWAYS AS (expr) STORED|VIRTUAL` computed columns instead of recomputing a derived value in every query or maintaining it by hand in application code. Teaches the load-bearing distinctions — `ALWAYS` (rejects explicit inserts unless `OVERRIDING SYSTEM VALUE`) vs `BY DEFAULT` (serial-like, lets a supplied value win); `STORED` (disk, computed on write) vs `VIRTUAL` (compute on read); that a generated column can be read but never written directly; and that a generation expression must be deterministic, same-row, and cannot reference another generated column. Auto-invokes when writing or editing auto-increment / surrogate-key columns, `SERIAL`/`AUTO_INCREMENT`/`IDENTITY(1,1)`, computed/derived columns, `GENERATED` clauses in `CREATE TABLE`, or on "auto-incrementing id" / "computed column"

Guides standard SQL/JSON (SQL:2016 functions, SQL:2023 type) instead of reflexive vendor operators — use the constructors (JSON_OBJECT/JSON_ARRAY/JSON_OBJECTAGG/JSON_ARRAYAGG), and the query functions JSON_VALUE (for a scalar) vs JSON_QUERY (for an object/array) vs JSON_EXISTS (for a path test) rather than `->`/`->>`/`#>>`/JSON_EXTRACT; shred documents into joinable rows with JSON_TABLE; control path mismatches with the lax-vs-strict path language and ON ERROR/ON EMPTY; validate with IS JSON; and store JSON in the SQL:2023 native JSON type where available. The centerpiece is the wrong-function trap — JSON_QUERY on a scalar returns a quoted JSON string (`"abc"`) where JSON_VALUE returns the bare scalar (`abc`). Warns against storing JSON where a normalized schema belongs (the modern EAV antipattern) and routes that judgment to schema design. Auto-invokes when writing or editing JSON columns/queries, `->`/`->>`/`#>>`/JSON_EXTRACT, JSON_VALUE/JSON_QUERY/JSON_TABLE, JSON path expressions, JSON aggregation, or on

Guides SQL views and portable schema introspection — a view is a stored query (`CREATE VIEW`), not a table, re-run on every reference; only simple single-table views are automatically updatable while joins/aggregates/DISTINCT/GROUP BY/set-operation views are read-only by default; `WITH [LOCAL|CASCADED] CHECK OPTION` rejects an INSERT/UPDATE through a filtered view whose new row would fall outside the view (so it can't silently vanish or escape a security filter); and schema discovery should query the SQL-standard `INFORMATION_SCHEMA` (portable across PostgreSQL/MySQL/MariaDB/SQL Server) rather than vendor catalogs (`pg_catalog`, `sqlite_master`, `SHOW TABLES`, `PRAGMA`). Notes that SQLite has no `INFORMATION_SCHEMA` at all. Auto-invokes when writing or editing `CREATE VIEW`, updatable/security views, `WITH CHECK OPTION`, querying catalog/metadata, `INFORMATION_SCHEMA`/`SHOW`/`PRAGMA`/`pg_catalog`/`sqlite_master`, or generating schema-discovery or migration tooling queries.

Guides the one non-negotiable rule for combining SQL with data — pass every value through a bind parameter / prepared statement, and never build SQL by string interpolation, concatenation, f-strings, or `format`. Explains why this beats hand-escaping (OWASP ranks escaping last and "STRONGLY DISCOURAGED," and "CANNOT guarantee" it works), why identifiers (table/column names, ASC/DESC) cannot be parameterized and must be allow-listed against a fixed set, and that placeholder syntax (`?`, `$1`, `:name`, `%s`) is a driver convention, not a SQL dialect feature. Covers dynamic WHERE/ORDER BY/LIMIT done safely and the standard `PREPARE`/`EXECUTE` surface. Treats "just for this example" interpolation of user input as a defect, not a shortcut. Auto-invokes when writing or editing any query that embeds a variable or user input, string-building of SQL, dynamic `WHERE`/`ORDER BY`/table or column names, ORM raw-query escape hatches, or on "build this query from input" / "is this safe from injection" / "escape this value"

Guides atomic upsert — never a race-prone "SELECT then INSERT-or-UPDATE" in application code. Teaches the standard `MERGE` statement (`USING ... ON ...` with `WHEN MATCHED THEN UPDATE/DELETE` and `WHEN NOT MATCHED THEN INSERT`) and maps the real-world alternatives — `INSERT ... ON CONFLICT (col) DO UPDATE/DO NOTHING` in PostgreSQL/SQLite (with `excluded.col`) and `INSERT ... ON DUPLICATE KEY UPDATE` in MySQL/MariaDB (with the `new.col` row alias) — since `MERGE` is unevenly adopted (PostgreSQL only since v15; absent in MySQL and SQLite). Establishes that every upsert REQUIRES a UNIQUE or PRIMARY KEY constraint to define the "conflict," and flags the `MERGE` concurrency footgun — it can still raise a unique-violation under concurrent inserts and is not a substitute for a unique constraint plus retry. Auto-invokes when writing or editing `MERGE`, `INSERT ... ON CONFLICT` / `ON DUPLICATE KEY UPDATE`, any upsert / "insert-or-update" / "create-or-update" logic, or check-then-act read-modify-write code that selects

Guides correct, scalable pagination and the row-value machinery behind it. Prefer keyset/seek pagination — `WHERE (sort_key, id) > (:last_key, :last_id) ORDER BY sort_key, id FETCH FIRST n ROWS ONLY` — over `LIMIT n OFFSET m`, because `OFFSET` makes the server fetch-and-discard the first m rows (O(offset), so deep pages time out) and is unstable — an insert or delete between page loads shifts the window so the reader skips a row or sees one twice. Teaches the standard `OFFSET ... FETCH FIRST n ROWS ONLY [WITH TIES]` surface versus non-standard `LIMIT`, the row value constructor comparison `(a, b) > (x, y)` (lexicographic, left-to-right, with its NULL trap) as the clean replacement for the error-prone `(a > x) OR (a = x AND b > y)`, multi-row `IN ((..),(..))`, and `VALUES` row-lists for set-based bulk operations. Every correct page needs a total, stable `ORDER BY` (tie-broken on a unique column). Auto-invokes when writing or editing pagination, `LIMIT`/`OFFSET`/`FETCH` queries, infinite-scroll or cursor/keyset

Guides standard temporal handling in SQL — the temporal types (DATE, TIME, TIMESTAMP, and crucially TIMESTAMP WITH TIME ZONE), typed literals (DATE '...', TIMESTAMP '...', INTERVAL '...'), EXTRACT, the standard CURRENT_DATE/CURRENT_TIMESTAMP keywords, and INTERVAL arithmetic — instead of jamming dates into strings or reaching for vendor functions (NOW()/GETDATE()/SYSDATE, DATEADD/DATEDIFF, STRFTIME). The centerpiece is the TIMESTAMP vs TIMESTAMP WITH TIME ZONE distinction — store an event as a UTC instant, never as a naive local timestamp that breaks across DST and zones — plus the month-length ambiguity of INTERVAL math and the high implementation divergence (SQLite has no native date type; MySQL's TIMESTAMP and DATETIME mean opposite things). Auto-invokes when writing or editing date/time columns or literals, EXTRACT, INTERVAL arithmetic, time-zone-sensitive comparisons, date truncation/bucketing, or age/duration calculations, and on "store a timestamp", "add a day/month", "why is my time off by an hour", o

Guides database-enforced data integrity — push the rules into the schema as declarative constraints rather than re-checking them in every application that writes. Covers PRIMARY KEY (= UNIQUE + NOT NULL, one per table) vs UNIQUE (many per table, and by default permits multiple NULLs — the SQL:2023 NULLS NOT DISTINCT lever fixes the duplicate-"unique"-emails trap), CHECK constraints and the pitfall that a CHECK passes when it evaluates to UNKNOWN/NULL (so pair it with NOT NULL), NOT NULL/DEFAULT, and FOREIGN KEY referential actions — choose ON DELETE/ON UPDATE deliberately (CASCADE | RESTRICT | NO ACTION | SET NULL | SET DEFAULT; the default is NO ACTION, which errors rather than cascades). Flags the two big SQLite footguns — foreign keys are NOT enforced unless you run PRAGMA foreign_keys = ON per connection, and PRIMARY KEY does not imply NOT NULL — and DEFERRABLE INITIALLY DEFERRED for circular references. Auto-invokes when writing or editing CREATE TABLE/ALTER TABLE constraints, FOREIGN KEY/REFERENCES, CHE

Guides standard-SQL type selection so the storage matches the data's meaning — exact `NUMERIC`/`DECIMAL(p,s)` for money and counts (never `FLOAT`/`REAL`/`DOUBLE`, whose binary floating point cannot store 0.1 exactly and whose rounding error compounds when summed), `SMALLINT`/`INTEGER`/`BIGINT` chosen by the value range (the `INTEGER` ceiling is +2,147,483,647), `CHARACTER VARYING` sized to a real domain limit instead of a cargo-culted `VARCHAR(255)`, fixed-width `CHARACTER(n)` only for genuinely fixed codes (it blank-pads), a true `BOOLEAN`, and `BLOB`/`CLOB` for large objects. Covers precision/scale and overflow semantics, integer overflow, and SQLite's dynamic-typing deviation where a declared type is only advisory and any column accepts any value. Auto-invokes when writing or editing column type declarations, `CREATE TABLE`/`ALTER TABLE` type choices, `CAST` targets, money/currency/price fields, high-precision or large-range numbers, identifier/key column widths, or string-length and boolean columns. Route

Guides common table expressions for readable query decomposition (`WITH`) and `WITH RECURSIVE` for hierarchies, transitive closure, and series generation — always with a termination or cycle guard so recursion cannot run away. Teaches the canonical anchor + recursive-member structure (only the recursive term may reference the CTE), the working-table/queue evaluation model (recursion is iteration, not a stack), the UNION-vs-UNION-ALL distinction in recursion, the standard `SEARCH` and `CYCLE` clauses (SQL:2023) versus the portable manual path-array guard, recursive series generation as the portable `generate_series` substitute, and the PostgreSQL CTE materialization fence (PG <=11 always materializes; >=12 inlines a single-reference CTE unless `MATERIALIZED`). Auto-invokes when writing or editing `WITH`/`WITH RECURSIVE`, hierarchy/tree/graph traversal, transitive-closure or generate-series logic, deeply nested subqueries that want flattening, or on "infinite recursion" / "query runs forever" / "tree query" / "

Guides portable scalar expressions in SQL — use standard `CASE`, `COALESCE`, `NULLIF`, `||` concatenation, and the keyword string functions (`SUBSTRING(... FROM ... FOR ...)`, `TRIM([LEADING|TRAILING|BOTH] c FROM s)`, `POSITION(sub IN s)`, `OVERLAY`, `CHAR_LENGTH`, `UPPER`/`LOWER`), plus `CAST(x AS type)` for conversion, instead of vendor spellings (`IFNULL`/`ISNULL`/`NVL`/`IIF`, `SUBSTR`/`INSTR`/`LEFT`/`RIGHT`/`LENGTH`, `+` for concat, `::`/`CONVERT`). Warns that `||` and arithmetic return NULL if any operand is NULL (the silent-blank concat trap; Oracle deviates by treating NULL as `''`), that a `CASE` with no `ELSE` defaults to NULL and short-circuits with no fall-through, that `NULLIF(a,b)` yields NULL when equal (the `x / NULLIF(y,0)` divide-by-zero guard), and that `GREATEST`/`LEAST` (standardized in SQL:2023) disagree across engines on NULL. Auto-invokes when writing or editing `CASE`, `COALESCE`/`NULLIF`/`GREATEST`/`LEAST`, string functions, string concatenation, or type conversions/`CAST`.

Guides LATERAL — a derived table in FROM that may reference columns of preceding FROM items, which a plain subquery cannot. It is the clean, standard answer to top-N-per-group ("latest 3 orders per customer"), per-row set-returning-function expansion, and pulling several correlated values in one pass instead of N correlated scalar subqueries in the SELECT list. Covers the LEFT JOIN LATERAL (...) ON true recipe with ORDER BY + FETCH FIRST n ROWS, implicit LATERAL for table functions, the left-to-right visibility rule, and the SQL Server/Oracle CROSS APPLY / OUTER APPLY spelling of (CROSS/LEFT) JOIN LATERAL. Teaches when a window function top-N is the better tool instead. Auto-invokes when writing or editing LATERAL / CROSS APPLY / OUTER APPLY, top-N-per-group or "newest N per group" queries, per-row table-function expansion (unnest/json_table/string functions in FROM), or a query carrying one correlated scalar subquery per output column. Builds on the foundation sql-relational-and-null-discipline.

Guides aggregation correctness — every column in the SELECT list of a grouped query must be either named in GROUP BY or wrapped in an aggregate (the functional-dependency rule), because a non-grouped, non-aggregated column has no single value per group. Bans the "ambiguous groups" antipattern that errors on standard-conformant engines but silently returns an arbitrary row's value on MySQL with ONLY_FULL_GROUP_BY disabled. Teaches WHERE (filters rows before grouping, no aggregates) vs HAVING (filters groups after, aggregates allowed), the standard SQL:2003 `FILTER (WHERE ...)` clause for conditional aggregation instead of fragile CASE-inside-aggregate, COUNT(*)/COUNT(col)/COUNT(DISTINCT) and aggregate NULL handling, GROUPING SETS/ROLLUP/CUBE plus GROUPING() instead of hand-UNIONed subtotal levels, and ordered-set aggregates (LISTAGG/ARRAY_AGG/percentile, WITHIN GROUP). Auto-invokes when writing or editing GROUP BY/HAVING, aggregate functions (SUM/COUNT/AVG/MIN/MAX), FILTER, LISTAGG/STRING_AGG/GROUP_CONCAT/ARRA

Guides SQL set operations and the default-to-UNION cost trap — UNION sorts and de-duplicates its whole combined result (expensive) while UNION ALL just appends, so use UNION ALL whenever duplicates are impossible or wanted. Covers INTERSECT/EXCEPT (native set intersection and difference, so they aren't reinvented with joins or NOT EXISTS) and their [ALL] multiset forms, union-compatibility (columns align by position not name, equal count, compatible types), where ORDER BY/FETCH are legal (only on the final compound query — parentheses isolate a branch), the precedence rule (INTERSECT binds tighter than UNION/EXCEPT in standard SQL), that set ops treat NULLs as NOT distinct (two NULLs collapse — the opposite of `=`), and VALUES as an inline table constructor. Auto-invokes when writing or editing UNION/UNION ALL/INTERSECT/EXCEPT, compound SELECTs, VALUES row-set constructors, or on "combine two queries" / "rows in A but not B" / "why are my duplicates gone" / "MINUS" requests.

Guides window functions — the highest-leverage modern-SQL technique and the frame-clause traps LLMs reliably botch. A window function computes across related rows without collapsing them (PARTITION BY is not GROUP BY). The centerpiece — the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which makes LAST_VALUE return the current row, not the partition's last, and makes running totals jump on tied sort keys; fix with an explicit ROWS frame (or MAX() OVER). Covers ROW_NUMBER/RANK/DENSE_RANK/NTILE tie behavior, LAG/LEAD offsets, FIRST_VALUE/LAST_VALUE/NTH_VALUE, ROWS vs RANGE vs GROUPS, named WINDOW reuse, EXCLUDE, and that a window result cannot be filtered in WHERE/HAVING (wrap in a CTE/subquery — the top-N-per-group pattern). Auto-invokes when writing or editing OVER (...), PARTITION BY, frame clauses (ROWS/RANGE/GROUPS), ranking/offset/value functions, running or moving totals, "top-N per group", or "filter on row_number" / "WHERE row_number() = 1" requests.

Guides correct join composition and the single most damaging join bug — putting a filter on the null-able side of an outer join in WHERE instead of ON, which silently demotes a LEFT JOIN to an INNER JOIN and drops the very rows you were looking for. Covers INNER/LEFT/RIGHT/FULL/CROSS semantics with wrong/right SQL, ON vs USING vs NATURAL (and why NATURAL JOIN is a schema-change landmine that joins on every same-named column), self-joins, detecting accidental cross products from a missing or typo'd predicate, one-to-many fan-out that silently inflates SUM/COUNT, and join-key nullability under three-valued logic. Auto-invokes when writing or editing JOIN clauses, ON/USING/NATURAL conditions, queries filtering an outer-joined table, multi-table FROM lists, or on "my LEFT JOIN is dropping rows" / "duplicate rows after join" / "my totals doubled" / "why did I get a cross product" symptoms. Builds on sql-relational-and-null-discipline.

Guides the logical clause-evaluation order of a SELECT — FROM → WHERE → GROUP BY/aggregates → HAVING → SELECT-list/window → DISTINCT → UNION → ORDER BY → OFFSET/FETCH — and why that order, not the written order, decides what each clause can reference. A SELECT-list alias is computed late, so it is illegal in WHERE/HAVING (write out the expression) but legal in ORDER BY; aggregate conditions belong in HAVING, not WHERE; DISTINCT dedups the WHOLE row, not one column; and SELECT * is the "Implicit Columns" antipattern that breaks on schema change. Auto-invokes when writing or editing SELECT statements, WHERE/GROUP BY/HAVING/DISTINCT/ORDER BY clauses, column aliases referenced in another clause, ORDER BY ordinals or NULLS FIRST/LAST, or on "column alias does not exist" / "must appear in the GROUP BY" / "why is this column not allowed here" errors. Builds on the sql-relational-and-null-discipline foundation.

Guides correct subquery use and owns the deep dive the foundation defers — the `NOT IN` + NULL trap, where a single NULL anywhere in the list or subquery collapses `NOT IN` to zero rows because `NOT IN` expands to a chain of `<>` comparisons AND'd together and the NULL conjunct is forever UNKNOWN, so `NOT EXISTS` is the safe portable anti-join (EXISTS never returns UNKNOWN). Covers scalar subqueries (the >1-row runtime error and the 0-rows→NULL propagation), `IN`/`EXISTS` semi-joins, correlated vs uncorrelated execution, `ANY`/`ALL` and why `= ANY` ≡ IN and `<> ALL` ≡ NOT IN (so `<> ALL` inherits the same NULL trap), and choosing a subquery vs a join for clarity and plan quality. Auto-invokes when writing or editing subqueries, `IN (SELECT ...)`, `NOT IN`, `EXISTS`/`NOT EXISTS`, correlated subqueries, `ANY`/`SOME`/`ALL`, scalar subqueries in SELECT/WHERE, or on "NOT IN returns nothing" / "my orphan/anti-join query is empty" / "subquery returned more than one row" symptoms.

Guides the core correctness floor of SQL — query results are unordered sets with no defined order unless you write ORDER BY, NULL means "unknown" and propagates UNKNOWN through every comparison and arithmetic expression, and WHERE/HAVING/ON keep only rows that evaluate to TRUE while CHECK rejects only rows that evaluate to FALSE (so UNKNOWN passes). Bans the recurring NULL traps — `x = NULL` (always UNKNOWN, use IS NULL), `col <> 'a'` silently dropping NULL rows, `NOT IN (subquery)` collapsing to zero rows on a single NULL, COUNT(col) silently undercounting versus COUNT(*), and SUM/AVG silently skipping NULL. Teaches null-safe equality IS [NOT] DISTINCT FROM and UNIQUE NULLS [NOT] DISTINCT (SQL:2023). Auto-invokes when writing or editing any WHERE/HAVING/ON/CHECK predicate, comparisons against possibly-null columns, NOT IN/`<>`/`!=`, COUNT/SUM/AVG over nullable columns, GROUP BY/ORDER BY/UNIQUE on nullable columns, or on "why does my query return no rows" / "why is my average wrong" / "handle NULL" requests.