SQL Standard Vs Dialect Map

SQL Standard vs Dialect Map

"SQL is well known for its lack of portability ... The standard is generally followed, but each vendor has its own dialect with proprietary extensions, and conformance is only ever partial." — paraphrasing the modern-sql.com — SQL Standard survey of versions and optional features

"Although the SQL standard is fairly comprehensive ... PostgreSQL does not implement every feature, and conversely, some PostgreSQL features are not in the standard." — PostgreSQL — SQL Conformance

The SQL standard (ISO/IEC 9075) defines a mandatory Core SQL plus a long list of optional features. No engine implements all of it, every engine adds proprietary spellings, and "optional" means a conforming engine is free to omit a feature entirely. So the same statement can be standard, ubiquitous, and unportable at once (LIMIT is the textbook case — everywhere except Oracle/SQL Server, and not in the standard at all).

This skill is the portability index for the SQL plugin. It does not teach any feature — each feature's meaning, traps, and correct usage live in its own skill. This file answers exactly one question: does engine X support feature Y, and how does it spell it? Every other skill's "Portability" section routes here; this is the SQL analogue of a language version/feature map. It builds on the set-semantics and three-valued-logic floor in sql-relational-and-null-discipline (the policy root) — portability never overrides correctness.

Every row below is a portability statement: read it as "the standard says X; PostgreSQL ✓ / SQLite ~ / MySQL ✗ (spells Y)." Legend: ✓ native standard-ish support · ~ supported but spelled differently or version-gated · ✗ absent (use the fallback in the notes). The most-used ~13 features are tabled below; the exhaustive feature×engine matrix lives in references/dialect-matrix.md.

1. Pagination — OFFSET … FETCH vs LIMIT

FETCH FIRST is the standard but "MySQL and SQLite do not support fetch at all"; LIMIT is ubiquitous-but-non-standard and absent on Oracle pre-12c / SQL Server. Who suffers: the dev who shipped LIMIT 10 against an Oracle 11g target where it does not parse. Feature owner: sql-pagination-and-keyset.

2. Auto-Generated Keys — GENERATED AS IDENTITY vs SERIAL/AUTO_INCREMENT/IDENTITY

Prefer the standard GENERATED AS IDENTITY on PostgreSQL; SERIAL is legacy. SQLite's rowid INTEGER PRIMARY KEY and MySQL's AUTO_INCREMENT are the portable substitutes. Feature owner: sql-generated-and-identity-columns.

3. Upsert — MERGE vs ON CONFLICT vs ON DUPLICATE KEY UPDATE

There is no single portable upsert. ON CONFLICT (PG/SQLite) and ON DUPLICATE KEY UPDATE (MySQL) are different statements with different conflict-target and new-value-alias rules; MERGE arrived in PostgreSQL only in 15. Feature owner: sql-merge-and-upsert.

4. String Concatenation — || vs + vs CONCAT()

|| is standard but MySQL reads it as logical OR by default, and SQL Server uses +. CONCAT() is the most portable. NULL handling also diverges: 'a' || NULL is NULL everywhere except Oracle (which treats '' as NULL → yields 'a') and MySQL's CONCAT (NULL-propagating). Owners: sql-expressions-case-and-functions; NULL semantics in sql-relational-and-null-discipline.

5. Null-Safe Equality — IS [NOT] DISTINCT FROM vs <=>

The standard IS NOT DISTINCT FROM is the null-aware equals; MySQL spells it <=>; SQLite's bare IS/IS NOT already compare NULLs as equal. Owner: sql-relational-and-null-discipline §8.

6. String Aggregation — LISTAGG / STRING_AGG / GROUP_CONCAT

Four names for one operation; ordering syntax differs too (WITHIN GROUP for LISTAGG/PG, ORDER BY inside the call for MySQL). Owner: sql-aggregation-and-grouping.

7. Catalog Introspection — INFORMATION_SCHEMA vs sqlite_master/PRAGMA vs SHOW

INFORMATION_SCHEMA is standard and portable across PG/MySQL/SQL Server, but SQLite and Oracle have it not — SQLite uses sqlite_master/PRAGMA, Oracle uses its data dictionary. Owner: sql-views-and-introspection.

8. JSON Access — standard functions vs ->/->>

The standard JSON_* functions are arriving unevenly (PG only at 17); the ->/->> operators are near-universal but vendor extensions, absent on Oracle. Owner: sql-json.

9. Lateral Joins — LATERAL vs CROSS/OUTER APPLY

SQL Server spells lateral as APPLY and has no LATERAL keyword; SQLite has neither — emulate with a correlated subquery or ROW_NUMBER() OVER (PARTITION BY …). Owner: sql-lateral-and-correlated-derived.

10. Set Operations — EXCEPT vs MINUS, and the ALL/precedence gaps

Oracle's MINUS, MySQL's late (8.0.31) arrival of INTERSECT/EXCEPT, and SQLite's flat left-to-right precedence (no INTERSECT priority) are the three traps. Owner: sql-set-operations.

11. Identifier Quoting & Case-Folding — "double" vs backtick vs ANSI_QUOTES

The damaging cell is MySQL's default "abc" = string: WHERE name = "John" passes review on MySQL, then breaks porting SQLite→PostgreSQL where "John" is a (nonexistent) column name. Write to the standard — single-quote every string, lowercase snake_case every identifier — and you never touch either deviation. Owner: sql-style-and-naming.

12. Boolean — BOOLEAN vs BIT vs TINYINT(1)

Only PostgreSQL (and DuckDB) have a real three-valued BOOLEAN. MySQL stores TINYINT(1), SQL Server uses BIT, SQLite uses integer 0/1. Comparisons like WHERE active (bare boolean) are portable only where a true boolean exists. Owner: sql-data-types-and-numerics.

13. Datetime "now" and date math — CURRENT_TIMESTAMP vs NOW()/GETDATE()/SYSDATE

Use the standard CURRENT_TIMESTAMP (not NOW()) for portability. Date math has no portable spelling at all — INTERVAL, DATEADD, and strftime are mutually exclusive. SQLite has no dedicated temporal type. Owner: sql-datetime-and-intervals.

14. Default Transaction Isolation Level

The level names are standard; the default differs, so identical un-configured transaction code has different concurrency semantics on each engine. Set the level explicitly for portable behavior. Owner: sql-transactions-and-isolation.

15. Cast, substring, null-default — small but pervasive

Always prefer CAST over :: and COALESCE over IFNULL/NVL/ISNULL. Owner: sql-expressions-case-and-functions.

16. Advanced / Low-Portability Features — confirm before recommending

These are the rows where "is it portable?" is usually no. When the target engine is on the ✗ side, the correct answer is the fallback, not the syntax that will not parse. Deep coverage: sql-match-recognize, sql-temporal-tables, sql-property-graph-queries, sql-window-functions.

17. How to Use This Map

1. Identify the feature the user is about to write (upsert, pagination, JSON access…).
2. Look up the target engine's column. ✓ = write the standard spelling; ~ = use the noted vendor spelling or check the version gate; ✗ = use the fallback in the notes.
3. Default to the standard spelling when more than one engine is in play — it is the intersection that ports. Reach for a vendor spelling only when targeting one engine, and say so.
4. For depth, route to the feature's own skill (named per section) — this map says whether and how, not why.

The recurring lesson: a statement can be standard, common, and unportable simultaneously. Verify the cell before claiming "this works on X."

18. Who Suffers When Portability Is Assumed

Portability bugs do not error in the IDE — they parse on the dev's engine and fail on the target's:

• The backend dev who shipped SELECT … LIMIT 10 to an Oracle 11g target and got ORA-00933 in production — LIMIT never existed there pre-12c (§1).
• The maintainer porting a SQLite schema to PostgreSQL whose WHERE name = "active" silently broke: MySQL/SQLite tolerated the double-quoted string, PostgreSQL read "active" as a missing column (§11).
• The data engineer whose INSERT … ON DUPLICATE KEY UPDATE upsert was rewritten for a PostgreSQL warehouse and threw a syntax error — PG speaks ON CONFLICT, and MERGE only since 15 (§3).
• The analyst whose EXCEPT report returned a syntax error on Oracle, which spells set difference MINUS (§10).
• The on-call engineer debugging a concurrency anomaly that only reproduces on MySQL, because its default REPEATABLE READ differs from PostgreSQL's READ COMMITTED (§14).

Checking the cell before you write the spelling is a gift to whoever runs the query on the other engine.

19. Routing

This skill is the index every other SQL skill's Portability section links to, the way every Go skill routes to go-version-feature-map. Inbound from all 29 siblings; the highest-traffic outbound owners:

• sql-pagination-and-keyset (§1) · sql-generated-and-identity-columns (§2) · sql-merge-and-upsert (§3)
• sql-relational-and-null-discipline (§5, foundation/policy root) · sql-aggregation-and-grouping (§6) · sql-views-and-introspection (§7)
• sql-json (§8) · sql-lateral-and-correlated-derived (§9) · sql-set-operations (§10) · sql-style-and-naming (§11)
• sql-data-types-and-numerics (§12) · sql-datetime-and-intervals (§13) · sql-transactions-and-isolation (§14)
• sql-match-recognize, sql-temporal-tables, sql-property-graph-queries (§16, the low-portability anchors)

20. Reference Files

The exhaustive feature×engine matrix — every feature in this map plus the long tail, as full markdown tables:

references/dialect-matrix.md

High-frequency portability mistakes in LLM-generated SQL, each with wrong/right code and a source:

references/common-mistakes.md

Source provenance for every claim in this skill:

references/sources.yaml