SQL Design & Query Reference

A developer with experience in PostgreSQL, MySQL/MariaDB, MSSQL, and Oracle compiled a SQL design and query reference document generated by Claude, an LLM, to serve as a knowledge transfer resource for junior engineers. The reference covers core concepts across four database types, including patterns for common query problems and anti-patterns like DISTINCT misuse.

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 https://dev.to/andreimerlescu/when-ai-creates-a-closed-source-world-2oin 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 https://dev.to/andreimerlescu/codeberg-bans-cryptocurrency-and-llm-generated-code-projects-3aj0 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 https://dev.to/andreimerlescu/how-i-processed-666k-pages-of-flattened-pdfs-into-a-full-text-search-engine-4db3 was custom written from the ground up for an OPEX problem https://dev.to/andreimerlescu/how-i-reduced-my-opex-by-995-using-go-3ff7 . 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 once jsonb | ❌ 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-out NOT EXISTS , never NOT IN against a subquery SELECT list ORDER BY has a deterministic tiebreaker ROWS 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.