Tags: web-dev analytics concept

SQL Dialects

Date: 2026-09-27

---

The SQL standard covers the shape; every engine differs on the details you’d assume were standard — division, quoting, string matching, dates, NULL ordering. The dangerous differences are the ones that don’t error: the same query runs everywhere and returns a different number.

---

A SQL dialect is one engine’s version of the language — the standard, minus what it doesn’t implement, plus its own extensions and defaults. The four met most in web and analytics work: Postgres and MySQL (application databases), SQLite (embedded, local, tests) and BigQuery (warehouse; its dialect is called GoogleSQL).

The silent ones — same query, different answer

Integer division

SELECT 5 / 2

Postgres   2         integer ÷ integer = integer, truncated
SQLite     2
MySQL      2.5000
BigQuery   2.5       / always returns FLOAT64; integer division is DIV(5, 2)

This is the one that corrupts conversion rates. COUNT(purchases) / COUNT(sessions) is 0 in Postgres for any rate under 100%. Multiply by 1.0 or cast one side to a decimal first.

String comparison and case

SELECT 'Email' = 'email'

MySQL      true      default collations are case-insensitive
Postgres   false
SQLite     false     but LIKE is case-insensitive for ASCII
BigQuery   false

A GROUP BY channel that merges Email and email in MySQL keeps them as two rows in BigQuery. The same data, migrated, gives different channel totals — UTM Governance. Postgres has ILIKE for case-insensitive matching; elsewhere, LOWER() both sides.

NULL sort order

ORDER BY x ASC       where do NULLs go?       NULLS FIRST / LAST available?

Postgres   last       (treated as largest)    yes
MySQL      first      (treated as smallest)   no — ORDER BY x IS NULL, x
SQLite     first                              yes, since 3.30
BigQuery   first                              yes

Matters for “latest value” queries: ORDER BY last_seen DESC LIMIT 1 returns a NULL row first in Postgres.

Loose GROUP BY

• Postgres and BigQuery reject a selected column that is neither grouped nor aggregated
• MySQL rejects it by default (ONLY_FULL_GROUP_BY), but many older installs switched that off
• SQLite accepts it and returns a value from some row in the group — Aggregation and Grouping

The loud ones — errors, at least

Quoting

            identifiers          string literals
Postgres    "double"             'single' only
SQLite      "double" (backticks  'single'
            accepted)
MySQL       `backtick`           'single' or "double" (unless ANSI_QUOTES)
BigQuery    `backtick`           'single' or "double"

WHERE status = "paid" works in MySQL and BigQuery and fails in Postgres with column “paid” does not exist — because double quotes name a column there.

Concatenation. 'a' || 'b' is concatenation in Postgres, SQLite and BigQuery; in MySQL || is logical OR unless the PIPES_AS_CONCAT mode is on. CONCAT() works in all four.

Dates. The area with the least agreement.

truncate to month       Postgres   date_trunc('month', ts)
                        BigQuery   DATE_TRUNC(d, MONTH) / TIMESTAMP_TRUNC(ts, MONTH)
                        MySQL      DATE_FORMAT(ts, '%Y-%m-01')
                        SQLite     strftime('%Y-%m-01', ts)

difference              Postgres   ts2 - ts1  → an interval
                        BigQuery   TIMESTAMP_DIFF(ts2, ts1, MINUTE)
                        MySQL      TIMESTAMPDIFF(MINUTE, ts1, ts2)   ← argument order reversed
                        SQLite     (julianday(ts2) - julianday(ts1)) * 1440

date series             Postgres   generate_series(...)
                        BigQuery   GENERATE_DATE_ARRAY(...) + UNNEST
                        MySQL, SQLite   recursive CTE

The recursive version is in Common Table Expressions.

Timestamps and time zones — each engine has a type that stores an absolute instant and a type that stores wall-clock time with no zone, under different names: Postgres timestamptz vs timestamp; BigQuery TIMESTAMP vs DATETIME; MySQL TIMESTAMP vs DATETIME. SQLite has no date type at all — text, real or integer, by convention. Mixing the two kinds is how a day’s orders end up split across two dates — Timezones and Date Boundaries.

Feature gaps

                        Postgres   MySQL        SQLite         BigQuery
window functions        yes        8.0+         3.25+          yes
FULL OUTER JOIN         yes        no           3.39+          yes
QUALIFY                 no         no           no             yes
upsert                  ON CONFLICT   ON DUPLICATE   ON CONFLICT   MERGE
                                      KEY UPDATE
arrays / nested rows    arrays     JSON only    JSON only      ARRAY, STRUCT, UNNEST

• QUALIFY filters on a window function’s result without a wrapping subquery — also in Snowflake, DuckDB and Databricks. Not in the SQL standard. [CHECK: a Postgres patch was proposed in 2025; check whether any release has shipped it] — Window Functions
• MySQL’s missing FULL OUTER JOIN is emulated as a LEFT JOIN UNION a RIGHT JOIN — Joins
• BigQuery’s nested and repeated fields — an event row holding an array of item structs, as the GA4 (Google Analytics 4) export does — need UNNEST to flatten, and an unguarded UNNEST in a join is a fan-out waiting to happen

[CHECK: the version thresholds above are from memory and documentation searches as of 2026-09; confirm against the engine version actually deployed.]

Working across them

• Test against the engine production uses. SQLite in tests and Postgres in production is how integer division and case-sensitivity bugs pass CI — Test Data · Integration Testing
• ORMs and query builders paper over the loud differences, not the silent ones. They translate quoting and upserts; they don’t change what 5 / 2 returns
• SQLGlot (a Python library) transpiles between dialects, and is useful for migrations — useful, not authoritative on semantics
• When migrating analytics from an app database to a warehouse, reconcile totals by day and by channel before switching dashboards over. Case sensitivity and time zones produce most of the difference — Replatforming