SQL

SQL — engine-agnostic query craft

This skill is the portable query-writing layer that sits above any one database engine. It owns the SELECT-side craft: joins and what each does to row count and NULLs, window functions (PARTITION/ORDER/frame), CTEs (including recursive), aggregation (GROUP BY/GROUPING SETS/HAVING), set operations (UNION/INTERSECT/EXCEPT), conditional logic (CASE/COALESCE/NULLIF), and the NULL three-valued-logic traps that quietly corrupt results across every engine. You write queries a reviewer accepts on Postgres, MySQL 8, SQLite, DuckDB, SQL Server, or BigQuery with minimal change, and you flag exactly where a construct is non-portable and what the dialect substitute is. The target standard is SQL:2023 (ISO/IEC 9075:2023), the ninth edition published June 2023; window functions have been standard since SQL:2003, so they are safe to assume everywhere.

This is about thinking in sets and frames, not about one product's planner, DDL, indexing, or ops.

When to use

• Writing a non-trivial read query: multi-table join, "top-N per group", running totals, period-over-period deltas, dedup, pivots, cohort/funnel shaping.
• Reaching for a window function and unsure about PARTITION BY vs GROUP BY, or ROWS vs RANGE vs GROUPS frames.
• Structuring a query with CTEs or recursive CTEs (hierarchies, graph walks, generated series).
• Aggregation shaping: GROUP BY, HAVING, GROUPING SETS/ROLLUP/CUBE, conditional aggregates.
• Combining result sets with UNION/INTERSECT/EXCEPT; deciding ALL vs distinct.
• Debugging a query that returns too many rows (join fan-out), too few (NULL-eating NOT IN), or wrong aggregates (counting joined duplicates).
• Translating a procedural loop ("for each row, query again") into one set-based statement.
• Reviewing SQL for portability and correctness regardless of the target engine.

When NOT to use

The defining line: sql = portable query-language craft; engine skills = one product's behavior, storage, and operations. When the engine isn't decided, or the question is "how do I express this in SQL at all" rather than "how does Postgres run it" — you are in the right place.

Non-negotiables

1. Explicit JOIN syntax, never comma-joins. FROM a, b WHERE a.id = b.a_id hides the join condition in the filter — drop the WHERE clause by accident and you get a silent cross product.
2. Alias and qualify every column in a multi-table query. SELECT id, name is ambiguous and breaks the moment two joined tables share a column name; SELECT o.id, c.name survives schema changes.
3. NOT EXISTS over NOT IN whenever the inner side is nullable. NOT IN returns zero rows if the subquery yields a single NULL (3VL UNKNOWN is never TRUE); NOT EXISTS is NULL-safe. Standard, not engine-specific.
4. Know your implicit window frame. A window function with ORDER BY but no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps tied rows together — not ROWS. This silently wrong running total is the single most common window bug, identical across engines. Write the frame explicitly.
5. Every non-aggregated SELECT column appears in GROUP BY. Engines that let you skip it (old MySQL) return an arbitrary row per group — a correctness landmine, not a convenience.
6. UNION ALL unless you genuinely need dedup. Bare UNION sorts/hashes to remove duplicates — real cost — and silently collapses rows you meant to keep. Add ALL by default; remove it deliberately.
7. Reason about NULL 3VL before writing any predicate. NULL = NULL is UNKNOWN, x <> 5 excludes NULL x, and COUNT(col) skips NULLs while COUNT(*) does not. Decide what NULL means before the WHERE.
8. One set-based statement beats a procedural loop. "For each row, run another query" is almost always a join or a window function — orders of magnitude faster and atomic. Reach for sets first.

Decision tables

JOIN chooser

A 1-to-many JOIN fans out the left row once per match. If you then SUM/COUNT, the aggregate is inflated. Use a semi-join (EXISTS) when you only want existence, not the joined columns.

GROUP BY vs window function

Rule of thumb: if the question is "per X, the total/rank/previous," and you still want the individual rows, it is a window function. If you only want the rollup, it is GROUP BY.

Frame chooser (ROWS / RANGE / GROUPS)

ROWS and RANGE plus EXCLUDE and numeric RANGE offsets work on Postgres 11+ and SQLite 3.28+. MySQL 8 supports only ROWS and RANGE — no GROUPS, no EXCLUDE. See references/window-functions.md.

Subquery vs JOIN vs CTE

Copy-paste patterns

Every fence is sql. Full depth in references/.

Top-N per group — never LIMIT inside a correlated subquery.

-- Bad: correlated subquery runs once per customer; non-portable LIMIT placement
SELECT * FROM orders o
WHERE o.id IN (
  SELECT id FROM orders i WHERE i.customer_id = o.customer_id
  ORDER BY i.amount DESC LIMIT 3
);

-- Good: one pass, ranked, then filtered
SELECT customer_id, id, amount
FROM (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

Running total — make the frame explicit so ties don't lump.

-- Bad: no frame -> implicit RANGE, tied dates collapse into one running value
SELECT day, SUM(amount) OVER (ORDER BY day) AS running FROM sales;

-- Good: explicit ROWS frame counts physical rows
SELECT day,
       SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales;

Period-over-period with LAG.

-- Good: previous row's value per partition; NULL on the first row is expected
SELECT month, revenue,
       revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month) AS delta,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY product_id ORDER BY month))
             / NULLIF(LAG(revenue) OVER (PARTITION BY product_id ORDER BY month), 0), 2) AS pct_change
FROM monthly_revenue;

NULLIF(prev, 0) guards against divide-by-zero; the first row's LAG is NULL by design.

Dedup keeping latest — QUALIFY is convenient but narrow.

-- Portable: rank then filter in an outer query
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM users
) d WHERE rn = 1;

-- DuckDB / BigQuery / Snowflake only: QUALIFY skips the wrapper. NOT in Postgres/MySQL/SQLite.
SELECT * FROM users
QUALIFY ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1;

Recursive CTE with a depth guard — always bound the recursion.

-- Good: org chart walk; depth column stops runaway / cyclic graphs
WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1
  FROM employees e JOIN tree t ON e.manager_id = t.id
  WHERE t.depth < 50            -- hard ceiling; for true cycles track a path array
)
SELECT * FROM tree;

Conditional aggregation / pivot — FILTER reads cleaner than CASE.

-- Portable everywhere: CASE inside the aggregate
SELECT region,
       SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'open' THEN amount ELSE 0 END) AS open
FROM invoices GROUP BY region;

-- Postgres/SQLite/DuckDB: FILTER is the standard, more readable form. NOT in MySQL/SQL Server.
SELECT region,
       SUM(amount) FILTER (WHERE status = 'paid') AS paid,
       SUM(amount) FILTER (WHERE status = 'open') AS open
FROM invoices GROUP BY region;

GROUPING SETS / ROLLUP — one scan, multiple aggregation levels.

-- Good: subtotals per (region, product), per region, and grand total in one query
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product);   -- = GROUPING SETS ((region,product),(region),())

Anti-join via NOT EXISTS — the NULL-safe "rows with no match."

-- Good: customers who never ordered; correct even if orders.customer_id has NULLs
SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The NOT IN-NULL footgun.

-- Bad: if ANY returned customer_id is NULL, this yields ZERO rows, silently
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Good: NOT EXISTS, or NOT IN with an explicit IS NOT NULL filter on the inner column
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Portability quick map

Full six-engine matrix in references/portability.md.

Anti-patterns / rationalizations -> STOP

Verify

Run scripts/verify.sh from your project root. It is read-only, never connects to a database, and runs on stock macOS bash 3.2. It heuristically scans discovered .sql files and warns on the footguns above (NOT IN (SELECT …), comma-joins with WHERE-join predicates, SELECT * alongside GROUP BY, window OVER (… ORDER BY …) with no explicit frame) and, if sqlfluff is installed, lints with --dialect ansi. It exits non-zero only on a real sqlfluff lint error or unbalanced parens/quotes (dollar-quote aware); every heuristic is advisory [warn], and an empty target passes clean.

See Also

• references/window-functions.md — ranking/offset/aggregate-over catalog, every frame unit worked, EXCLUDE, named windows, implicit-frame trap, per-engine matrix.
• references/joins-and-sets.md — every join type with row-count reasoning, semi/anti/lateral joins, set ops + ALL/dedup/MINUS, the fan-out-inflates-aggregates bug.
• references/ctes-and-recursion.md — CTE structuring, recursive template (hierarchy/graph/series) with cycle + depth guards, the optimization-fence portability note.
• references/portability.md — full dialect matrix across Postgres / MySQL 8 / SQLite / DuckDB / SQL Server / BigQuery.
• Siblings: ../postgresdb/SKILL.md, ../mysql/SKILL.md, ../duckdb/SKILL.md, ../clickhouse-analytics/SKILL.md, ../drizzle-orm/SKILL.md, ../prisma-orm/SKILL.md, ../db-migrations/SKILL.md. ORM/engine internals are out of scope here — this skill owns the SQL those tools ultimately emit.