SQL Design & Query Reference

I have professionally worked with four different SQL database types PostgreSQL

, MySQL

& MariaDB

(InnoDB), MSSQL

SQL Server 2008+ and Oracle 19c

+. Over the course of that time I have been asked:

And being an autodidact software engineer with an electrical engineering background in computer engineering, I would reference The Library to complete task A or task B or task C. Many junior engineers are in this boat as well. They may have the formal education that I lacked not being in computer science as a major in college, but it doesn't mean that I can't use resources at my disposal to learn and meet each of these requirements and where I've done it.

This document is Claude's response to my request for it to act as Staff SQL Architect and write a reference document for these four database types as a knowledge transfer doc for junior engineers who need a quick reference of these core concepts.

In all fairness, it's not wrong to have this handy. Instead of it being fragmented on Stack Overflow in the form of dozens of Questions and Answers - its been gathered through the LLM's that have crossed the line with acceptable community behavior. So, before those models are taken offline 😅🤫🤣 I wanted to utilize this resource to help give back. It's a format and structure applicable to four distinct types of SQL databases that any developer should be comfortable with. My experience with SQL databases was extensive between 2003 and 2020. After, I became heavily involved with MongoDB

and NoSQL databases including Redis and PostgreSQL. Since 2020, I've been on the administration side of the database and less on the SQL query writing sided of the database. The Apario Database was custom written from the ground up for an OPEX problem.

If you find this document useful, please give it a 💜 before you bookmark it.

How to read this doc: Sections marked [SAME] behave identically on all four engines — learn the concept once and move on. Sections marked [DIFFERS] are where people get burned porting code or scaling up. Version numbers matter a lot here; when a feature is version-gated I say so, and you should still verify against the exact version in your environment before you rely on it.

Before you write anything, answer three questions:

EXISTS

). Enriching → join or window function.| Problem | Right pattern | Wrong pattern people reach for | |---|---|---| | "Customers who have ≥1 order" | EXISTS | JOIN + DISTINCT | | "Customers with no orders" | NOT EXISTS | NOT IN | | "Each order plus its customer name" | JOIN | correlated scalar subquery | | "Total per customer" | GROUP BY | correlated aggregate in SELECT | | "Each order plus its customer's total" | window function | correlated aggregate in SELECT | | "Latest order per customer (full row)" | ROW_NUMBER() or lateral | MAX(date) + self-join | | "Top N per group" | ROW_NUMBER /RANK /DENSE_RANK | correlated COUNT subquery | | "Rows in A not in B, both large" | anti-join (NOT EXISTS ) | EXCEPT /MINUS if you need dupes preserved |

DISTINCT

smell SELECT DISTINCT

in a query that joins to a child table almost always means you used a join where you wanted a semi-join. The join fans out, then DISTINCT

collapses it back — you paid for a sort/hash of the fanned-out set for nothing.

-- BAD: fan out 4M rows, then dedupe back to 200k
SELECT DISTINCT c.customer_id, c.name
FROM customer c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'SHIPPED';

-- GOOD: optimizer stops at first match per customer
SELECT c.customer_id, c.name
FROM customer c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.status = 'SHIPPED');

There are legitimate DISTINCT

s (deduping a genuinely duplicated source). But treat every one as a thing you must justify in code review.

A junior writes a CTE thinking "this runs once and gets reused." That is engine-dependent and version-dependent.

Engine	CTE behaviour
PostgreSQL ≤ 11
Always materialized. Hard optimization fence — predicates do not push in.
PostgreSQL 12+
Inlined if referenced once and not recursive/volatile; otherwise materialized. Force with WITH x AS MATERIALIZED (...) / AS NOT MATERIALIZED .
SQL Server
Effectively always inlined — a CTE referenced three times is executed three times. No hint to materialize. Use a #temp table when you need one-shot evaluation.
Oracle
Cost-based; may materialize into a temp segment. Hints: /*+ MATERIALIZE */ , /*+ INLINE */ .
MySQL 8.0+
Materializes derived tables/CTEs in many cases; merge is possible. Historically weaker at pushing predicates into derived tables than the others.

The scale gotcha: on SQL Server, a "clean" query with a CTE that scans a 500M-row fact table and is referenced in three branches of a UNION ALL

scans it three times. It looks elegant and it is a disaster. On PostgreSQL ≤ 11 the opposite failure: your WHERE tenant_id = 42

outside the CTE never reaches inside it, so you materialize the whole table first.

Rule for the team: if a CTE is referenced more than once and the underlying scan is expensive, on SQL Server/MySQL stage it into a temp table explicitly. Don't rely on the optimizer being clever.

Recursive syntax differs cosmetically: PostgreSQL and MySQL require WITH RECURSIVE

; SQL Server and Oracle use plain WITH

(Oracle additionally has the older CONNECT BY

, which is still often faster there for pure hierarchy walks and supports LEVEL

, SYS_CONNECT_BY_PATH

, CONNECT_BY_ISLEAF

).

EXISTS

/ NOT EXISTS

— and Why NOT IN

Keeps Hurting You

SELECT * FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);

If one row in blacklist.customer_id

is NULL, this returns zero rows. Always. Silently.

Why: x NOT IN (a, b, NULL)

expands to x <> a AND x <> b AND x <> NULL

. That last term is UNKNOWN

, never TRUE

. So the whole conjunction can never be TRUE

.

NOT EXISTS

does not have this problem, because it tests row existence, not value comparison. Default to NOT EXISTS. If you must use

NOT IN

, the subquery column must be NOT NULL

| Engine | NOT EXISTS | NOT IN | |---|---|---| Oracle | Anti-join | Handles well — has a null-aware anti-join (ANTI NA ) operator (11g+). Roughly parity. | SQL Server | Anti semi-join | Usually anti-join too, but nullable columns add an extra probe/filter; plans get uglier. | PostgreSQL | Clean anti-join | Historically cannot convert to an anti-join when the column is nullable — degrades to a filtered subplan. Materially worse on large sets. | MySQL 8.0.17+ | Anti-join transformation available | Also transformed in 8.0.17+. Pre-8.0 this was a per-row dependent subquery — catastrophic. |

So NOT EXISTS

is correct everywhere and the best-performing choice on the two engines where it matters most. There is no reason to write NOT IN

against a subquery. Against a short literal list (status NOT IN ('A','B')

) it's fine.

LEFT JOIN ... WHERE right.pk IS NULL

This is the third anti-join spelling. It is semantically safe (no NULL trap) but:

WHERE

instead of ON

, which silently converts your outer join to an inner join.Prefer NOT EXISTS

for readability; recognize the LEFT JOIN / IS NULL

form when you meet it.

EXISTS

vs IN

for the positive case All four modernly transform IN (subquery)

into a semi-join, so they're usually equivalent. Two notes:

IN

with a subquery returning NULLs is NOT IN

) — NULLs just never match.IN (subquery)

as a dependent subquery in many cases. If you maintain anything on 5.6/5.7, rewrite IN

to EXISTS

or a join. On 8.0+ the semijoin strategies (FirstMatch

, LooseScan

, MaterializeLookup

, DuplicateWeedout

) handle it.EXISTS

micro-details SELECT 1

vs SELECT *

vs SELECT NULL

inside EXISTS

makes zero difference on any of the four — the select list isn't evaluated. Don't argue about it in review. Do make sure the correlation predicate is indexed; that's what actually matters.

This is the single most common performance defect in junior-written SQL, and it's identical on all four engines. [SAME]

SELECT o.order_id,
       o.customer_id,
       (SELECT COUNT(*) FROM orders o2
        WHERE o2.customer_id = o.customer_id)      AS cust_order_count,
       (SELECT SUM(amount) FROM orders o3
        WHERE o3.customer_id = o.customer_id)      AS cust_total,
       (SELECT MAX(order_date) FROM orders o4
        WHERE o4.customer_id = o.customer_id)      AS cust_last_order
FROM orders o
WHERE o.order_date >= DATE '2026-01-01';

Three correlated subqueries = up to three extra index range scans per output row. At 2M output rows that's 6M extra scans. On a good day the optimizer decorrelates one of them. Don't rely on it.

SELECT order_id, customer_id,
       COUNT(*)        OVER (PARTITION BY customer_id) AS cust_order_count,
       SUM(amount)     OVER (PARTITION BY customer_id) AS cust_total,
       MAX(order_date) OVER (PARTITION BY customer_id) AS cust_last_order
FROM orders
WHERE order_date >= DATE '2026-01-01';

One scan, one sort/hash by customer_id

, all three aggregates computed together. Note the semantic change: the window is now over the filtered set. If you need totals over all history, you can't use this form — see Fix 2.

WITH cust_agg AS (
  SELECT customer_id,
         COUNT(*)        AS cust_order_count,
         SUM(amount)     AS cust_total,
         MAX(order_date) AS cust_last_order
  FROM orders
  GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, a.cust_order_count, a.cust_total, a.cust_last_order
FROM orders o
JOIN cust_agg a ON a.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01';

One aggregate pass over the child, one hash join. This is the workhorse. Critical rule: aggregate before you join, not after. Joining first fans out rows and then you're aggregating an inflated set — which is also how you get silently wrong

SUM

s.

-- WRONG
SELECT c.customer_id, SUM(o.amount), SUM(p.amount)
FROM customer c
JOIN orders o   ON o.customer_id = c.customer_id
JOIN payments p ON p.customer_id = c.customer_id
GROUP BY c.customer_id;

If a customer has 3 orders and 4 payments, you get 12 rows; SUM(o.amount)

is 4× too large and SUM(p.amount)

is 3× too large. This ships to production regularly because the numbers look plausible. Fix: aggregate each branch separately in its own subquery/CTE, then join the two aggregates. Or use COUNT(DISTINCT ...)

as a diagnostic to prove the fan-out to yourself.

"Give me each customer plus their most recent order's full details."

Engine	Syntax
PostgreSQL	LEFT JOIN LATERAL (...) t ON true
Oracle 12c+
OUTER APPLY (...) or LEFT JOIN LATERAL (...) ON 1=1
SQL Server	OUTER APPLY (...)
MySQL 8.0.14+	LEFT JOIN LATERAL (...) ON true

-- PostgreSQL / MySQL 8.0.14+
SELECT c.customer_id, c.name, lo.order_id, lo.order_date, lo.amount
FROM customer c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC, o.order_id DESC
    LIMIT 1
) lo ON true;

(SQL Server/Oracle: TOP 1

/ FETCH FIRST 1 ROWS ONLY

and OUTER APPLY

.)

When lateral beats ROW_NUMBER(): when the outer set is small and the inner table is huge and well-indexed. Lateral does N cheap index seeks.

ROW_NUMBER()

sorts the ROW_NUMBER()

wins — one sorted pass beats 5M seeks.That trade-off — N seeks vs. one big sort — is the mental model. Know your N.

SELECT customer_id,
       MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_order_id,
       MAX(amount)   KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_amount,
       SUM(amount) AS total
FROM orders GROUP BY customer_id;

KEEP (DENSE_RANK FIRST/LAST)

gets you "the value of column X from the row with the max/min of Y" inside a single GROUP BY

— no window pass, no self-join. There is no direct equivalent on the other three; PostgreSQL's closest is DISTINCT ON

or ordered-set aggregates.

ON

vs WHERE

on outer joins

-- These are NOT the same query
FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'X'   -- filters b before joining
FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.status = 'X' -- silently becomes an INNER JOIN

Any predicate on the null-supplying side placed in WHERE

eliminates the NULL-extended rows. If you genuinely want "no matching b," write WHERE b.a_id IS NULL

.

Wrapping an indexed column in a function kills the index. Universal.

WHERE UPPER(email) = 'A@B.COM'          -- no index use on email
WHERE order_date + 1 > SYSDATE          -- no index use
WHERE YEAR(order_date) = 2026           -- no index use

Remedies by engine:

Engine	Expression/functional index	Case-insensitive strategy
PostgreSQL
CREATE INDEX ON t (upper(email)) — long supported
citext type, or expression index
Oracle	Function-based indexes (8i+)	FBI on UPPER() , or NLS_SORT /NLS_COMP linguistic index
SQL Server	No true expression index — use a persisted computed column + index
Column collation is usually CI already (_CI_AS ) so it's often a non-issue
MySQL 8.0.13+	Functional indexes; before that, generated column + index	Default collations are CI (utf8mb4_0900_ai_ci ) — usually a non-issue

Two engine-specific date traps:

DATE

always carries a time component. WHERE order_date = DATE '2026-07-27'

misses everything with a nonzero time. Use a half-open range >= DATE '2026-07-27' AND < DATE '2026-07-28'

, not TRUNC()

.datetime

(legacy) has ~3ms precision and rounds .997

→.997

, .999

→next second. BETWEEN '2026-07-01' AND '2026-07-31 23:59:59.999'

is a bug. Always use half-open ranges. This applies everywhere but bites hardest here.NVARCHAR

parameters against a VARCHAR

column forces CONVERT_IMPLICIT

on the NVARCHAR

wins. This is probably the #1 cause of "it's fast in SSMS but slow from the app." Match your parameter types to your column types.utf8mb3_general_ci

legacy table to utf8mb4_0900_ai_ci

new table) prevents index use on the join. Also, comparing a string column to a numeric literal converts the VARCHAR2

vs NVARCHAR2

, and comparing a NUMBER

column to a string literal. Oracle converts the character side to number, which is safe, but comparing a VARCHAR2

column to a number converts the column → scan.bigint

column vs int

parameter is fine while text

vs varchar

collation mismatches can still block index use in less common cases.GROUP BY

strictness | Engine | Rule | |---|---| | SQL Server, Oracle | Strict ANSI. Every non-aggregated select item must be in GROUP BY . | | PostgreSQL | Strict, except it recognizes functional dependency on a grouped primary key — GROUP BY c.customer_id lets you select c.name . | | MySQL | ONLY_FULL_GROUP_BY is on by default since 5.7.5. Similar functional-dependency detection to PG. Legacy code with it disabled returns arbitrary values from arbitrary rows — a correctness bug, not a style issue. Never turn it off. |

HAVING

vs WHERE

WHERE

filters rows before grouping; HAVING

filters groups after. Putting a non-aggregate predicate in HAVING

is not wrong (all four will typically push it down) but it's misleading. Put row filters in WHERE

.

-- Works everywhere
SELECT customer_id,
       SUM(CASE WHEN status = 'SHIPPED'   THEN amount ELSE 0 END) AS shipped_amt,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END)           AS cancelled_cnt
FROM orders GROUP BY customer_id;

PostgreSQL additionally has the cleaner ANSI FILTER

clause: COUNT(*) FILTER (WHERE status = 'CANCELLED')

. It's PG-only among these four. Use it in PG-only code; use CASE

in anything portable.

Note COUNT(CASE WHEN ... THEN 1 END)

— no ELSE

, so non-matches are NULL and COUNT

skips them. COUNT(CASE WHEN ... THEN 1 ELSE 0 END)

counts everything and is a classic bug.

Operation	PG	MySQL	SQL Server	Oracle
Union, dedupe	UNION
UNION
UNION
UNION
Union, keep dupes	UNION ALL
UNION ALL
UNION ALL
UNION ALL
Difference	EXCEPT
EXCEPT (8.0.31+)
EXCEPT
MINUS (EXCEPT added in 21c+)
Intersection	INTERSECT
INTERSECT (8.0.31+)
INTERSECT
INTERSECT

Always ask whether you need UNION or UNION ALL.

UNION

performs a full dedupe (sort or hash) over the combined result. If the branches are provably disjoint — different date ranges, different status values — UNION ALL

is free and UNION

costs you a sort of the entire set. On a 200M-row report this is the difference between 40 seconds and 8 minutes.Also: EXCEPT

/MINUS

deduplicate the left side. NOT EXISTS

does not. They are not interchangeable when duplicates are meaningful.

Algorithm	PG	MySQL 8.0	SQL Server	Oracle
Nested loop	✅	✅	✅	✅
Hash join	✅	✅ (8.0.18+)	✅	✅
Merge join	✅	❌ never
✅	✅ (sort-merge)

MySQL's gap is the important one. Before 8.0.18 MySQL had only nested-loop variants (BNL

). A three-table analytical join over millions of rows that any other engine resolves with hash joins in seconds could run for hours on MySQL 5.7. If you're on MySQL and a large join is slow, check the version first — this is frequently the whole answer. And MySQL still has no merge join, so pre-sorted large-to-large joins don't get that option.

Practical consequence: MySQL is a weaker analytical engine than the other three. Design around it — pre-aggregate into summary tables rather than expecting the optimizer to rescue a 6-table star join.

| Engine | Basic windows | Full frames (ROWS /RANGE ) | GROUPS / EXCLUDE | |---|---|---|---| | Oracle | 8i (ancient, very mature) | ✅ | GROUPS in 21c+ | | SQL Server | 2005 (ranking only) | 2012+ | ❌ not supported | | PostgreSQL | 8.4 | ✅ | ✅ (11+) | | MySQL | 8.0 only | ✅ | ❌ |

ROW_NUMBER

, RANK

, DENSE_RANK

, NTILE

, LAG

, LEAD

, FIRST_VALUE

, LAST_VALUE

, SUM/AVG/COUNT/MIN/MAX OVER

are present and identical in semantics on all four modern versions. [SAME]

For values 100, 100, 90, 80

:

Function	Result	Use when
ROW_NUMBER()
1, 2, 3, 4	You need exactly one row per group — deduplication, "latest record"
RANK()
1, 1, 3, 4	Competition ranking; gaps after ties
DENSE_RANK()
1, 1, 2, 3	"Top 3 distinct salary levels"; no gaps

The classic requirement mismatch: "give me the top 3 earners per department." If two people tie for 3rd, do you want 3 rows or 4? ROW_NUMBER

gives 3 (arbitrarily dropping one — nondeterministic and a real bug). RANK

/DENSE_RANK

give 4. Ask the business. Then make the tiebreaker explicit either way.

Always add a deterministic tiebreaker to ORDER BY inside the window.

ORDER BY order_date DESC

on a table with same-day orders produces different results run to run. Write ORDER BY order_date DESC, order_id DESC

. This is the source of most "the report changed but the data didn't" tickets.

WITH ranked AS (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY natural_key
                            ORDER BY loaded_at DESC, id DESC) AS rn
  FROM staging t
)
SELECT * FROM ranked WHERE rn = 1;

The CTE wrapper is required — you cannot filter on a window function in WHERE

on any of these four, because windows are evaluated after WHERE

. None of PostgreSQL, MySQL, SQL Server, or Oracle support QUALIFY (that's Teradata/Snowflake/DuckDB). Expect to write the CTE.

PostgreSQL has a shorthand for the rn = 1

case:

SELECT DISTINCT ON (natural_key) *
FROM staging ORDER BY natural_key, loaded_at DESC, id DESC;

PG-only. Often faster than ROW_NUMBER

there.

When you write OVER (PARTITION BY x ORDER BY y)

with no frame clause, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

.

RANGE

means peer rows (ties in y) are all included at once. So a "running total" over a day-grain column gives every row on the same date the

-- What you almost always meant:
SUM(amount) OVER (PARTITION BY customer_id
                  ORDER BY order_date, order_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SQL Server–specific performance angle: in SQL Server the default RANGE

frame uses an on-disk worktable spool, while ROWS

uses an in-memory spool. Specifying ROWS

explicitly is frequently a 5–10× improvement on large partitions — even when the results would be identical. Make ROWS ...

mandatory in SQL Server code review.

LAST_VALUE

trap [SAME]:LAST_VALUE(x) OVER (ORDER BY y)

returns the current row's value, because the default frame ends at the current row. You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

, or better, use FIRST_VALUE

with the order reversed.

PostgreSQL, MySQL 8.0, and Oracle support WINDOW w AS (PARTITION BY ... ORDER BY ...)

then OVER w

. SQL Server does not — you repeat the full OVER

clause. Minor, but it's a portability paper cut and a source of copy-paste inconsistency in SQL Server code.

(partition_cols, order_cols)

lets PostgreSQL, Oracle, and SQL Server skip the sort entirely. This is often the single highest-leverage index on a reporting table.ORDER BY

s = three sorts. Consolidate window definitions where you can.If you only need aggregates at the group grain, GROUP BY

is cheaper — it collapses rows; windows preserve them. Don't write SELECT DISTINCT customer_id, SUM(x) OVER (PARTITION BY customer_id)

; write GROUP BY

.

JSON columns are for genuinely schemaless, sparse, or third-party-shaped payloads: webhook bodies, per-tenant custom fields, audit snapshots, API response caching. They are not a substitute for columns.

The test: if you filter, join, sort, or aggregate on it regularly, it should be a column. Every engine's JSON indexing story is worse than a plain B-tree on a scalar column. You also lose type checking, NOT NULL, foreign keys, and check constraints.

Second rule: do not use JSON as an EAV escape hatch. "Just put the extra attributes in a JSON blob" is how you get a 400GB table where nobody knows what's in it and every query is a full scan.

PostgreSQL	MySQL 8.0	SQL Server	Oracle
Native type
json (text) / (binary)jsonb
JSON (binary)
NVARCHAR(MAX) + ISJSON check (native json type in SQL Server 2025)
JSON type in 21c+; before that CLOB /VARCHAR2 + IS JSON
Extract scalar
->> , #>> , jsonb_path_query
->> , JSON_UNQUOTE(JSON_EXTRACT()) , JSON_VALUE (8.0.21+)
JSON_VALUE
JSON_VALUE , dot notation
Extract object/array
-> , #>
->
JSON_QUERY
JSON_QUERY
Containment test
@> (indexed!)
JSON_CONTAINS
JSON_PATH_EXISTS (2022+)
JSON_EXISTS
Shred to rows
jsonb_array_elements , JSON_TABLE (17+)
JSON_TABLE (8.0.4+)
OPENJSON
JSON_TABLE
Modify in place
jsonb_set , `
, -`
JSON_SET/INSERT/REPLACE/REMOVE
General index
GIN on — indexes all keys/values at oncejsonb
❌ none	❌ (until 2025's JSON indexes)
JSON search index (CREATE SEARCH INDEX ... FOR JSON )
Targeted index	B-tree on expression	Generated column + index; multi-valued index for arrays (8.0.17+)
Persisted computed column + index	Function-based index on JSON_VALUE

jsonb

, essentially always json

stores the raw text (preserves whitespace, key order, duplicate keys) and re-parses on every access. jsonb

is parsed binary — faster access, supports indexing and containment operators. Cost: slightly slower insert, key order not preserved, duplicate keys collapsed.

CREATE INDEX idx_doc_gin ON events USING GIN (payload jsonb_path_ops);
-- supports: WHERE payload @> '{"type":"purchase"}'

-- Narrower + smaller when you know the key:
CREATE INDEX idx_doc_type ON events ((payload->>'type'));

jsonb_path_ops

GIN indexes are substantially smaller and faster than the default jsonb_ops

but only support @>

/ @?

/ @@

, not key-existence (?

). Prefer it unless you need key-existence checks.

PG gotchas at scale: large jsonb

values get TOASTed (out-of-line, compressed), so payload->>'x'

on a 2KB document means detoast + decompress per row. Extracting one small field from a big document across millions of rows is far slower than people expect. Also, jsonb_set

rewrites the entire value — updating one key in a 100KB document writes 100KB and generates that much WAL.

You cannot CREATE INDEX ON t (json_col->>'$.status')

directly in the way you'd hope. Two supported routes:

-- Route 1: generated column (works 5.7+)
ALTER TABLE events
  ADD COLUMN evt_type VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.type'))) STORED,
  ADD INDEX idx_evt_type (evt_type);

-- Route 2: functional index (8.0.13+) — same thing, less clutter
ALTER TABLE events ADD INDEX idx_evt_type ((CAST(payload->>'$.type' AS CHAR(50))));

-- Route 3: multi-valued index for arrays (8.0.17+)
ALTER TABLE events ADD INDEX idx_tags ((CAST(payload->'$.tags' AS CHAR(40) ARRAY)));
-- used by: WHERE JSON_CONTAINS(payload->'$.tags', '"urgent"')

Multi-valued indexes are MySQL's one genuinely nice JSON feature — one index entry per array element, queryable via MEMBER OF

, JSON_CONTAINS

, JSON_OVERLAPS

.

MySQL gotchas: JSON columns can't be used in a primary key; generated columns must be STORED

(not VIRTUAL

) for some index types; partial in-place updates only happen under narrow conditions (JSON_SET

on same-or-smaller value) — otherwise MySQL rewrites the whole document and logs it whole to the binlog. Replication lag from JSON-heavy writes is a real production issue.

Through SQL Server 2022, JSON is NVARCHAR(MAX)

— there was no JSON type, no JSON index. You get it via:

ALTER TABLE Events ADD EventType AS
  JSON_VALUE(Payload, '$.type') PERSISTED;
CREATE INDEX IX_Events_Type ON Events (EventType);

OPENJSON

with an explicit WITH

schema is dramatically faster than the default key/value form, and gives you typed columns:

SELECT j.type, j.amount
FROM Events e
CROSS APPLY OPENJSON(e.Payload)
  WITH (type VARCHAR(50) '$.type', amount DECIMAL(18,2) '$.amount') j;

Add CHECK (ISJSON(Payload) = 1)

on the column or you will accumulate malformed rows.

SQL Server 2025 introduced a native json

type with binary storage and JSON indexes; if you're on it, prefer that over NVARCHAR(MAX)

. Confirm the specifics against the docs for your build — this is recent.

Oracle's JSON support (especially 19c+ and 21c/23ai) is arguably the most complete:

-- Constraint on pre-21c storage
ALTER TABLE events ADD CONSTRAINT ck_json CHECK (payload IS JSON);

-- Dot notation (requires table alias)
SELECT e.payload.type, e.payload.amount FROM events e;

-- General-purpose index over the whole document
CREATE SEARCH INDEX idx_payload ON events (payload) FOR JSON;

-- Targeted
CREATE INDEX idx_type ON events (JSON_VALUE(payload, '$.type'
  RETURNING VARCHAR2(50) ERROR ON ERROR NULL ON EMPTY));

The RETURNING

clause and error-handling clauses must match exactly between index definition and query predicate or the index isn't used — a common and frustrating gotcha.

Oracle 23ai adds JSON Relational Duality Views: relational tables underneath, JSON document API on top, with proper concurrency control. Genuinely the best answer to "the app team wants documents, I want normalization," if you're on that version.

If you must support more than one engine: use JSON as an opaque payload — store it, retrieve it whole, parse in the application. The moment you query inside it, you've committed to that engine. Extract the fields you query into real columns at write time.

(a, b, c)

serves predicates on a

, (a,b)

, (a,b,c)

. It does b

alone. All four can do a full index scan in that case, but that's not what you designed for.WHERE tenant_id = ? AND status = ? AND created_at > ?

, index (tenant_id, status, created_at)

. Putting created_at

before status

means the index can only seek to the range start and must filter the rest.| Engine | Table storage | Consequence | |---|---|---| MySQL/InnoDB | Always clustered on the PK (or a hidden rowid if none). Secondary indexes store the PK value as the row pointer. | A wide PK (e.g. UUID CHAR(36) ) is duplicated into every secondary index. Random PKs cause page splits on every insert. | SQL Server | Clustered index optional; without one the table is a heap. Nonclustered indexes point to the clustering key (or RID for heaps). | Same PK-width amplification as InnoDB when clustered. Heaps suffer forwarded records on updates. | PostgreSQL | Always a heap. No clustered index. CLUSTER is a one-time reorganization that doesn't hold. | Physical order drifts; correlation matters for range scan cost. Index-only scans need the visibility map to be current. | Oracle | Heap by default; Index-Organized Tables available for PK-clustered storage. | IOTs are great for narrow lookup tables, less so for wide ones. |

The PK choice consequence: on MySQL and SQL Server, use a narrow, monotonically increasing clustering key. BIGINT

identity/auto_increment is the default correct answer. Random UUID

v4 as a clustered PK on InnoDB is a well-documented performance disaster — insert throughput collapses as the B-tree fragments and every secondary index carries 16–36 bytes per entry.

If you need UUIDs for distributed ID generation, use a time-ordered variant (UUIDv7, or SQL Server's NEWSEQUENTIALID()

, or ULID) stored as BINARY(16)

/ uniqueidentifier

/ uuid

— never as a 36-char string. PostgreSQL tolerates random UUIDs better (heap storage) but still suffers index bloat and worse cache locality.

Feature	PG	MySQL 8.0	SQL Server	Oracle
Partial / filtered index	✅ WHERE
❌	✅ filtered index	❌ (emulate: function-based index returning NULL — Oracle doesn't index all-NULL entries)
Covering non-key columns	✅ INCLUDE (11+)
❌ (add as key cols)	✅ INCLUDE
❌ (add as key cols)
Expression / functional index	✅	✅ (8.0.13+)	⚠️ via persisted computed column	✅
Descending index	✅	✅ (8.0+; earlier it was parsed and ignored)	✅	✅
Bitmap index	❌	❌	⚠️ columnstore serves the use case	✅ (DW only)
Columnstore	⚠️ extensions	❌	✅ (mature)	✅ In-Memory Column Store (licensed)
Hash index	✅ (limited)	✅ MEMORY engine only	✅ (in-memory OLTP)	✅ hash cluster
GIN / inverted (arrays, JSON, FTS)	✅	⚠️ multi-valued only	❌	✅ search index
BRIN (huge, correlated tables)	✅	❌	⚠️ columnstore segment elimination	⚠️ zone maps (Exadata)
Invisible index (test before drop)	⚠️ no	✅	⚠️ via disable	✅
Online index build	✅ CONCURRENTLY
✅ mostly	✅ Enterprise only	✅ ONLINE (EE)

Three engine-specific traps worth memorizing:

WHERE deleted_at IS NULL

cannot use a single-column index on deleted_at

. Workaround: make it composite ((deleted_at, id)

) or use a function-based index. ''

as NULL.col = ''

will silently return nothing.

-- PostgreSQL
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'PENDING';

-- SQL Server
CREATE INDEX IX_Orders_Pending ON Orders (CreatedAt)
WHERE Status = 'PENDING';

If 0.1% of a 500M-row table is pending, this index is ~500k entries instead of 500M. Enormous. MySQL has no equivalent — the standard workaround is a separate "work queue" table that rows are deleted from when processed. That's not a hack; on MySQL it's the right design.

SQL Server filtered-index gotcha: the plan only uses it if the parameter is provably within the filter. Parameterized queries (WHERE Status = @s

) often won't use a filtered index because the optimizer can't prove @s = 'PENDING'

at compile time. OPTION (RECOMPILE)

or a literal fixes it. This surprises people constantly.

Engine	Mechanism	Main failure mode
SQL Server
Auto-update stats on ~20% change (or sqrt-based on 2016+). Plans cached per parameterized statement.
Parameter sniffing — the first execution's parameter values shape the cached plan for everyone. A plan built for tenant_id = <tiny tenant> is then used for <huge tenant> . Remedies: OPTION (RECOMPILE) , OPTIMIZE FOR , Query Store forced plans, Automatic Tuning.
Oracle
Bind peeking + Adaptive Cursor Sharing + SQL Plan Baselines.	Adaptive features mostly help but can cause plan churn. Baselines/SQL Profiles are the stability tool. Stale stats on volatile tables still bite.
PostgreSQL
Autovacuum/autoanalyze. Prepared statements switch to a generic plan after 5 executions if it seems no worse.	Skewed data + generic plan = bad plan. plan_cache_mode = force_custom_plan is the escape hatch. Also: no cross-column statistics by default — use CREATE STATISTICS for correlated columns (city /postcode ).
MySQL
Simplest optimizer of the four. Histograms only since 8.0. No plan cache to speak of.	Weakest cardinality estimation; InnoDB index statistics are sampled (innodb_stats_persistent_sample_pages ) and can be badly wrong on large tables. Raise the sample pages on big tables.

Team practice: on SQL Server, when someone says "the app is slow but it's fast when I run it in SSMS," your first hypothesis is parameter sniffing, second is implicit conversion from NVARCHAR parameters. Those two cover the majority of cases.

LIMIT 20 OFFSET 500000

(or OFFSET ... FETCH

, or ROWNUM

/ROW_NUMBER

filtering) makes the engine produce and discard 500,000 rows. Cost grows linearly with page number. Use keyset (seek) pagination:

SELECT ... FROM orders
WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

Row-value comparison works in PostgreSQL, MySQL, and Oracle. SQL Server does not support row-value constructors in comparisons — expand manually:

WHERE created_at < @last_created_at
   OR (created_at = @last_created_at AND order_id < @last_order_id)

(SQL Server may need an index hint or the rewritten OR

form to seek properly; check the plan.)

Get to 3NF/BCNF and stay there unless you have a measured reason not to. Normalization gives you: one place to update each fact, constraints that actually work, smaller rows (more rows per page, better cache hit rate), and a schema that survives requirement changes.

Denormalization is a performance optimization with a correctness cost. You are trading guaranteed consistency for speed. Like all optimizations: measure first, and know how you'll maintain it.

Denormalize derived data freely with a rebuild path. Duplicate raw data only with a very clear write-path story.

1. Immutable historical snapshots — not really denormalization at all.

An order line must store unit_price

and product_name

as of the transaction. This is not a redundant copy of product.price

— it's a different fact ("what we charged") from a different point in time. Joining to product

for historical price is a bug, not an optimization. Snapshot anything that appears on a legal or financial document.

2. Counter caches / rollups.

post.comment_count

instead of COUNT(*)

on every page load. Worth it when read:write is heavily skewed.

Gotcha at scale: every comment insert updates the same post row. A hot post becomes a lock convoy. Mitigations: sharded counters (N rows per post, summed), async batch updates, or accept eventual consistency and recompute nightly.

3. Precomputed aggregate/summary tables.

Daily/hourly rollups for dashboards. Almost always the right answer for reporting on a large OLTP table. This is the highest-value denormalization pattern in practice, and the one juniors underuse most.

4. Flattening a hot join to avoid a two-hop lookup.

Storing tenant_id

on every table in a multi-tenant system — even where it's derivable through a parent — so that every index can lead with it and every query can filter on it directly. This is standard, correct multi-tenant design, and it also lets row-level security work cleanly.

5. Materialized path / nested sets for deep hierarchies.

Recursive CTEs are fine for shallow trees. For an org chart read thousands of times a second, store a materialized path (/1/17/93/

) alongside the parent FK. Redundant, but a prefix LIKE '/1/17/%'

on an indexed column beats a recursion.

Engine	Mechanism	Notes
Oracle
Materialized views with query rewrite, FAST REFRESH ON COMMIT , materialized view logs
Best in class. Optimizer transparently rewrites queries to use the MV — the application doesn't change.
SQL Server
Indexed views (WITH SCHEMABINDING + unique clustered index)
Maintained synchronously and always correct. Auto-matched by the optimizer in Enterprise Edition; other editions need NOEXPAND . Heavy restrictions: no outer joins, no subqueries, no COUNT(*) (use COUNT_BIG(*) ), SUM requires a COUNT_BIG column.
PostgreSQL
Materialized views — manual REFRESH MATERIALIZED VIEW
No incremental refresh, no automatic query rewrite. REFRESH ... CONCURRENTLY avoids blocking readers but requires a unique index and is slower. For incremental, roll your own with triggers.
MySQL
Nothing. No materialized views, no indexed views.
You build summary tables yourself with triggers, scheduled events, or application logic. Plan for this from day one on MySQL.

Consequence for design: on Oracle you can often denormalize invisibly via MVs with query rewrite — the logical model stays clean. On MySQL every denormalization is explicit, hand-maintained, and a permanent maintenance obligation. Weight the trade-off accordingly: the same denormalization is much cheaper on Oracle than on MySQL.

Three approaches, in order of preference:

Always build #3 regardless of whether you have #1 or #2. A denormalization without a drift-detection job is a future incident. Make "how do we detect and repair drift" a mandatory question in design review.

Note also: CHECK

constraints in MySQL were parsed and ignored until 8.0.16. Any schema older than that may have constraints that never did anything. Verify.

Engine	Auto-indexes FK columns?
MySQL/InnoDB
Yes — automatically creates an index on the child column if one doesn't exist
PostgreSQL
No
SQL Server
No
Oracle
No

Three of four leave you to it. Unindexed FKs cause:

DELETE

/UPDATE

on the parent (must scan children to enforce the constraint)The Oracle-specific severity: an unindexed foreign key causes Oracle to take a share lock on the entire child table during parent deletes and PK updates. Not a row lock — a table lock. On a busy system this is an outage. This is one of the classic Oracle DBA findings, and it's worth running a "find unindexed FKs" audit query on any Oracle schema you inherit.

Rule: every FK column gets an index, on every engine, unless you can prove it's never traversed and the parent is never deleted from.

ON DELETE CASCADE

Supported on all four. Caveats:

Scenario: Vehicle

with subtypes Car

, Truck

, Motorcycle

, each with distinct attributes.

A. Single Table Inheritance — one table, all columns, a vehicle_type

discriminator.

CREATE TABLE vehicle (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL,
  vin           VARCHAR(17) NOT NULL,
  -- car only
  passenger_cnt INT,
  -- truck only
  payload_kg    INT,
  CONSTRAINT ck_car CHECK (vehicle_type <> 'CAR' OR
                           (passenger_cnt IS NOT NULL AND payload_kg IS NULL))
);

NOT NULL

impossible on subtype attributes, degrades badly past ~4 subtypes or when subtypes diverge.Use when: subtypes are few, similar, and attributes are mostly shared.

B. Class Table Inheritance — common attributes in parent, specifics in child tables sharing the PK.

CREATE TABLE vehicle (
  vehicle_id   BIGINT PRIMARY KEY,
  vehicle_type VARCHAR(20) NOT NULL,
  vin          VARCHAR(17) NOT NULL UNIQUE,
  CONSTRAINT ck_type CHECK (vehicle_type IN ('CAR','TRUCK','MOTORCYCLE')),
  CONSTRAINT uq_vehicle_type UNIQUE (vehicle_id, vehicle_type)   -- key trick
);

CREATE TABLE car (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL DEFAULT 'CAR',
  passenger_cnt INT NOT NULL,
  CONSTRAINT ck_car_type CHECK (vehicle_type = 'CAR'),
  CONSTRAINT fk_car_vehicle FOREIGN KEY (vehicle_id, vehicle_type)
    REFERENCES vehicle (vehicle_id, vehicle_type)
);

That composite FK + check constraint pattern is the standard way to make subtype exclusivity declaratively enforced — a row in vehicle

typed CAR

cannot have a truck

row. It works on all four engines. Learn it; most people implement this with application logic and get it wrong.

NOT NULL

on subtype attributes, clean model, no sparse columns. − Every full read needs a join; "give me all vehicles with all attributes" needs a UNION ALL

of per-subtype joins or several LEFT JOIN

s.Use when: subtypes have substantial distinct attributes. This is the default correct answer for most business domains.

C. Concrete Table Inheritance — one independent table per subtype, no parent.

UNION ALL

everywhere. Adding a subtype means touching every polymorphic query.Use when: subtypes barely overlap and are rarely queried together.

-- DON'T
CREATE TABLE comment (
  comment_id     BIGINT PRIMARY KEY,
  commentable_id BIGINT,        -- points to post OR photo OR video
  commentable_type VARCHAR(20)
);

Popular in Rails/Django-shaped codebases. You cannot declare a foreign key, so referential integrity is gone. Joins need CASE

or UNION

. Indexes are less selective.

Better: an exclusive-arc table with one nullable FK per target plus a check that exactly one is non-null; or a shared commentable

supertype table that each concrete type FKs into. The latter is more work up front and correct forever.

INHERITS

) exists but is largely a legacy feature. Its traps: unique constraints and foreign keys Pick the narrowest type that will hold the domain, and pick it correctly the first time. Changing a PK from INT

to BIGINT

on a 2-billion-row table is a multi-hour maintenance window on most of these engines.

INT

vs BIGINT

:BIGINT

from day one. The 4 extra bytes cost far less than the migration. Running out of INT

identity values in production is a rite of passage you should skip.DECIMAL

/NUMBER

always. Never FLOAT

/REAL

/DOUBLE

. 0.1 + 0.2 <> 0.3

in binary floating point, and financial reconciliation will find it.BOOLEAN

. MySQL's BOOLEAN

is an alias for TINYINT(1)

— it accepts any integer. SQL Server has BIT

(0/1/NULL, not a true boolean; can't be used directly in WHERE x

without comparison). NUMBER(1)

or CHAR(1)

with a check constraint.varchar(n)

/text

have identical performance — the length limit is purely a constraint, so use text

• a check if you like. MySQL's VARCHAR

length affects temp table and index size, and index prefix limits apply. SQL Server: NVARCHAR

is 2 bytes/char (pre-2019 non-UTF8 collations); CHAR(n)

blank-pads. VARCHAR2

is the one to use (VARCHAR

is reserved and may change meaning); default NLS_LENGTH_SEMANTICS

is BYTE, so VARCHAR2(50)

may hold fewer than 50 multibyte characters — specify VARCHAR2(50 CHAR)

.timestamptz

(stores UTC, converts on display) — use it, not timestamp

. Oracle: TIMESTAMP WITH TIME ZONE

or WITH LOCAL TIME ZONE

. SQL Server: datetime2

(not datetime

), datetimeoffset

if you need the offset. MySQL: TIMESTAMP

converts to UTC but is bounded to 2038; DATETIME

doesn't convert but has no timezone awareness. Pick one convention and enforce it across the schema.Row width is the quietest performance factor in the whole system. Every engine reads pages, not rows. Halving row width doubles rows per page, which roughly halves I/O for scans and doubles effective buffer cache capacity.

Practical consequences:

TEXT

/CLOB

/NVARCHAR(MAX)

column in a hot table. Move it to a side table keyed 1:1. (PostgreSQL's TOAST does this automatically for large values; Oracle stores LOBs out of line by default above a threshold; SQL Server and MySQL are less automatic — MySQL's DYNAMIC

row format stores overflow pages but the 20-byte pointer stays inline.)SELECT *

in application code is a design smell for exactly this reason, plus it defeats covering indexes and breaks when columns are added.NOT NULL

, CHECK

, UNIQUE

, and FOREIGN KEY

give the optimizer information. All four engines use them:

NOT NULL

declaration lets the optimizer skip null-handling logic and enables NOT IN

optimizations (see §2).UNIQUE

constraint tells the optimizer a join won't fan out, changing cardinality estimates and enabling join elimination.LEFT JOIN

to a parent table and select nothing from it, a validated FK lets SQL Server, Oracle, and PostgreSQL remove the join entirely. This is why views over well-constrained schemas perform better than views over "we enforce it in the app" schemas.Corollary: disabling constraints for a bulk load and re-enabling them NOVALIDATE

(Oracle) or as untrusted (SQL Server) leaves you with constraints the optimizer will not use. Check sys.foreign_keys.is_not_trusted

in SQL Server after any bulk load; check STATUS

/VALIDATED

in Oracle's USER_CONSTRAINTS

.

Engine	Default isolation	Readers block writers?
PostgreSQL	Read Committed (MVCC)	No
Oracle	Read Committed (MVCC, undo-based)	No
MySQL/InnoDB
Repeatable Read (MVCC)
No
SQL Server	Read Committed with locking (unless RCSI is enabled)
Yes

This is the biggest operational difference between SQL Server and the other three. By default, a long-running report in SQL Server takes shared locks and blocks writers. This is why you see WITH (NOLOCK)

scattered through legacy SQL Server codebases — it's a bad fix (it permits dirty reads, missed rows, and duplicate rows from page splits during scans). The correct fix is enabling Read Committed Snapshot Isolation (RCSI) at the database level, which makes SQL Server behave like the others. Understand the tempdb implications before you flip it on a live system.

MySQL's Repeatable Read default is also worth knowing: it means a long transaction sees a consistent snapshot for its whole duration, and it uses gap locks for range operations, which produces deadlocks in patterns that would be fine elsewhere.

Oracle and PostgreSQL never block readers, but PostgreSQL's MVCC means long-running transactions prevent vacuum from cleaning up dead tuples, causing table bloat and eventually transaction-ID wraparound risk. A leaked idle-in-transaction connection is a PostgreSQL-specific production emergency. Monitor pg_stat_activity

for it.

Operation	PG	MySQL 8.0	SQL Server	Oracle
Add nullable column	Instant	Instant	Instant	Instant
Add column with default	Instant (11+)	Instant (8.0.12+, INSTANT algo)
Instant (2012+ Enterprise; metadata-only)	Instant (11g+)
Add index	Blocks writes unless CONCURRENTLY
Online, mostly	Online = Enterprise only	Online = Enterprise
Widen varchar	Instant	Sometimes rebuilds (charset/length byte boundary)	Instant if same byte-length category	Instant
Drop column	Instant (marks it)	Rebuilds table (unless INSTANT)	Instant (marks it)
SET UNUSED then drop

Migration rules for the team:

SET lock_timeout

in PG, lock_wait_timeout

in MySQL, SET LOCK_TIMEOUT

in SQL Server). A migration that queues behind a long query and then blocks every subsequent query is how a 2-second DDL takes down a site.ALTER TABLE

takes an ACCESS EXCLUSIVE

lock. It waits for existing transactions, and everything queues behind it. Even an "instant" ALTER

can cause a total outage if a long-running SELECT

is in flight. Always SET lock_timeout = '2s'

and retry.pt-online-schema-change

/ gh-ost

exist for the cases where native online DDL doesn't cover you.DISTINCT

used to paper over a fan-outNOT EXISTS

, never NOT IN

against a subquerySELECT

listORDER BY

has a deterministic tiebreakerROWS BETWEEN

on any ordered window aggregate (mandatory on SQL Server)UNION ALL

unless dedupe is genuinely required| Engine | Command | |---|---| | PostgreSQL | EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — BUFFERS is the one people skip and shouldn't | | MySQL | EXPLAIN ANALYZE (8.0.18+), EXPLAIN FORMAT=JSON | | SQL Server | Actual execution plan; SET STATISTICS IO, TIME ON ; Query Store for history | | Oracle | DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST') — the actual plan, not EXPLAIN PLAN 's prediction |

What to look for, universally: estimated vs. actual row counts diverging by more than ~10× (bad stats or a bad predicate), scans where you expected seeks, and any operator whose row count explodes relative to its input.

DECIMAL

; timestamps are timezone-explicitEverything else in this doc is roughly portable. These are the ones to internalize:

NOT IN

handling'' IS NULL

, and single-column B-trees don't index NULLs.ACCESS EXCLUSIVE

locks make DDL risky.RANGE

is the default everywhere and is almost never what you want.

source & further reading

dev.to — original article Sandboxing Patterns for Local AI Agents With Filesystem Access Secure, Scalable AI Teams: Building a Multi-Tenant Agent Platform with Docker & Traefik The Velocity Crisis - Rethinking Code Review in the Age of Agentic Coding