SQL Dialects Compared: One Cheat Sheet Through T-SQL, PostgreSQL, Oracle, MySQL and Db2

Every few weeks the same SQL cheat sheet comes round again. Twenty lines, four headings, one function per line with a short description. It is decent material and it teaches real things. It is also written in no dialect in particular, which is exactly the problem: it reads as if SQL were one language, and whoever pastes it into a query window finds out otherwise.

So I took the list apart and ran every line, verbatim, against five running instances instead of reading manuals at it: SQL Server 2022, PostgreSQL 17, Oracle 23ai, MySQL 8.4 and Db2 12.1. Below is what those servers returned, what the same idea is called in each dialect, and the portable rewrite where one exists.

The list under test

Twenty constructs, in the four groups the original uses:

CLEANING & FORMATTING
  1  COUNT(DISTINCT col)                      count unique values only
  2  COALESCE(col, 0)                         replace NULL with a default value
  3  NULLIF(col, 0)                           return NULL if value equals zero
  4  CAST(col AS DATE)                        convert data types cleanly
  5  TRIM(col)                                remove leading and trailing spaces
  6  UPPER(col) / LOWER(col)                  standardise text case

DATES
  7  DATE_TRUNC('month', date)                round dates to month or year
  8  EXTRACT(YEAR FROM date)                  pull out part of a date
  9  DATEDIFF(end_date, start_date)           calculate days between two dates

ANALYSIS
 10  CASE WHEN col > 100 THEN 'High' ...      create conditional categories
 11  GROUP BY col HAVING COUNT(*) > 1         filter after aggregation
 12  SUBSTRING(col, 1, 3)                     extract part of a string
 13  WHERE col IN (SELECT col FROM table)     filter using a subquery
 14  WITH cte AS (SELECT ...)                 write cleaner, reusable queries
 15  UNION ALL                                combine results from two queries

WINDOW FUNCTIONS
 16  RANK() OVER (ORDER BY col DESC)          rank rows with ties
 17  LAG(col) OVER (ORDER BY date)            get the previous row's value
 18  LEAD(col) OVER (ORDER BY date)           get the next row's value
 19  SUM(col) OVER (ORDER BY date)            calculate a running total
 20  AVG(col) OVER (PARTITION BY region)      moving average within a group

How this was tested

Each line was executed against a three-row table carrying the columns it needs. No rewriting and no smoothing over: where the statement raised an error, the error is quoted.

Every result below is measured on one of those five. Where an engine rejected a statement, its own error number and wording is quoted.

One caveat worth stating up front: the Oracle and Db2 instances are current releases, and both vendors have moved recently. Several things that work on Oracle 23ai in the tests below do not work on 19c, which is what most Oracle shops are actually running. Those cases are called out individually.

The three lines that do not compile on SQL Server

SQL Server took 17 of the 20 lines. The three it rejected are all in the date section, which is the single least portable corner of SQL. Oracle also scored 17, on a different three.

-- SQL Server 2022, measured
SELECT DATE_TRUNC('month', dt) FROM cheat;
Msg 195: 'DATE_TRUNC' is not a recognized built-in function name.

SELECT EXTRACT(YEAR FROM dt) FROM cheat;
Msg 195: 'EXTRACT' is not a recognized built-in function name.

SELECT DATEDIFF(dt2, dt) FROM cheat;
Msg 174: The datediff function requires 3 argument(s).

7. Rounding a date down to the month

Three engines rejected the line as written. Two accepted it, and they are not the two you would guess: PostgreSQL, and Db2.

MySQL 8.4 returns FUNCTION demo.DATE_TRUNC does not exist, which is the error you get when MySQL treats an unknown name as a call to a stored function in the current schema. There is nothing to enable. The date-formatting round trip is the idiom.

Db2 accepting the PostgreSQL spelling is a good example of why reading the documentation for the version you have beats going on memory. It is also a good example of why to check before assuming the opposite: an older Db2 will want TRUNC_TIMESTAMP.

Two details that bite after the port compiles:

• PostgreSQL changes the column type. date_trunc('month', a_date) resolves to the timestamptz overload. Measured, the result came back as 2024-01-01 00:00:00+00, not as a date. Cast it back if the consumer expects a date.
• Week truncation is not a fixed definition. PostgreSQL and Db2 both start the week on Monday (measured: both returned 2024-05-13 for the 17th). DATETRUNC(week, ...) in SQL Server follows @@DATEFIRST, which depends on the login's language; DATETRUNC(iso_week, ...) does not. On the test instance @@DATEFIRST was 1, so both returned 2024-05-13 and the difference stayed invisible. On a US English login it would not have.

8. Pulling a part out of a date

This is the one case where SQL Server is the odd one out against the ANSI standard and all four of the others. EXTRACT does not exist in T-SQL at any version or compatibility level.

Since YEAR(dt) is valid in T-SQL, MySQL and Db2, and EXTRACT is valid everywhere except T-SQL, there is no single spelling that covers all five. If you are writing SQL that has to move, this is a line you abstract, not a line you get clever with.

9. Days between two dates: the worst line on the sheet

DATEDIFF(end_date, start_date) is MySQL syntax. Nothing else on the list is as dialect-specific while looking as generic.

Three separate traps live in that table.

The argument order is reversed between MySQL and T-SQL. Both accept a call that looks plausible, and one of them silently returns the negative of what you wanted. Measured on SQL Server: DATEDIFF(day, '2024-01-10', '2024-01-01') returns -9, while the MySQL-shaped DATEDIFF('2024-01-10', '2024-01-01') returns 9.

T-SQL DATEDIFF counts boundaries, not elapsed time. Measured: DATEDIFF(year, '2020-12-31', '2021-01-01') returns 1, for two dates one day apart. The same applies at every granularity: two timestamps two minutes apart across midnight return 1 day. Anyone computing an age or a tenure with DATEDIFF(year, ...) is off by up to a year.

-- MySQL 8.4.11, measured
SELECT DATE '2024-02-01' - DATE '2024-01-31'          AS minus_op,    -- 70
       DATEDIFF('2024-02-01', '2024-01-31')           AS datediff_fn, -- 1
       TIMESTAMPDIFF(DAY, '2024-01-31', '2024-02-01') AS tsdiff;      -- 1

T-SQL refuses the operator outright, which is the friendlier failure. Measured: CAST('20240201' AS date) - CAST('20240131' AS date) returns Msg 8117: Operand data type date is invalid for subtract operator. The same expression on two datetime values compiles and returns a datetime offset, which is a different kind of surprise.

Db2 has its own version of this, and it is the most easily misread of the three. Subtracting two dates there produces a date duration: a decimal packed as yyyymmdd, not a count of anything.

-- Db2 v12.1.5.0, measured. dt = 2024-01-05, dt2 = 2024-03-11
SELECT dt2 - dt              FROM cheat;   -- 206.   two months and six days
SELECT DAYS(dt2) - DAYS(dt)  FROM cheat;   -- 66     the answer you wanted

206 is not 206 of anything. It is 0 years, 02 months, 06 days read as a packed number, and it will happily flow into an INT column and sit there looking like a plausible day count. DAYS(end) - DAYS(start) is the form to use. Db2's TIMESTAMPDIFF returned the right 66 here, but IBM documents it as an estimate that assumes 30-day months, so it is not something to bill by.

The lines that compile everywhere and still differ

1. COUNT(DISTINCT col)

Portable as written, on all five. Two places where it stops being portable:

As a window function it is rejected almost everywhere. Four engines, four different ways of saying no:

-- SQL Server 2022
SELECT COUNT(DISTINCT n) OVER () FROM ...
  Msg 10759: Use of DISTINCT is not allowed with the OVER clause.

-- PostgreSQL 17.11
  ERROR: DISTINCT is not implemented for window functions

-- MySQL 8.4.11
  ERROR 1235 (42000): This version of MySQL doesn't yet support
  '<window function>(DISTINCT ..)'

-- Db2 v12.1.5.0
  SQL0441N  Invalid use of keyword DISTINCT or ALL with function "COUNT".

Oracle is the exception, and only halfway. Measured on 23ai, COUNT(DISTINCT col) OVER (PARTITION BY region) ran and returned 1, 2, 2 across the test rows. Add an ORDER BY to make it cumulative and it stops:

-- Oracle 23ai, measured
SELECT COUNT(DISTINCT col) OVER (ORDER BY dt) FROM cheat;
  ORA-30487: ORDER BY not allowed here

So even on the one engine that supports it, you get the partition-wide distinct count and not the running one. Everywhere else the workaround is a DENSE_RANK pair or a pre-aggregated derived table.

Counting distinct combinations differs. Measured: MySQL takes COUNT(DISTINCT a, b), PostgreSQL takes the row-constructor form COUNT(DISTINCT (a, b)), and SQL Server takes neither and needs a concatenated key or a subquery. Both workarounds have a collision to watch for. Concatenating without a separator turns ('ab','c') and ('a','bc') into one key, and CONCAT_WS skips nulls entirely, so on SQL Server both CONCAT_WS('|','a',NULL) and CONCAT_WS('|',NULL,'a') measured as 'a'. A subquery over SELECT DISTINCT a, b has neither problem and is the one that ports.

2. COALESCE and the null-replacement family

COALESCE is ANSI and works on all five. Every engine also ships its own shorter version, and the names collide in a genuinely dangerous way.

Every cell in that table was probed rather than looked up. The rejections came back as ORA-00904: "ISNULL": invalid identifier, function nvl(unknown, integer) does not exist, 'NVL' is not a recognized built-in function name and their equivalents.

-- MySQL 8.4.11, measured
SELECT ISNULL(NULL);        -- 1   (a predicate: "is this null?")

-- SQL Server 2022, measured
SELECT ISNULL(NULL, 0);     -- 0   (a replacement)

And T-SQL's ISNULL has a second problem that COALESCE does not: it takes the data type of its first argument, so a replacement value wider than that argument is truncated without a warning.

-- SQL Server 2022, measured
SELECT ISNULL  (CAST(NULL AS varchar(2)), 'abcdef');   -- 'ab'
SELECT COALESCE(CAST(NULL AS varchar(2)), 'abcdef');   -- 'abcdef'

There is more to that pair than the type rule, including how often the first argument is executed. IS DISTINCT FROM: Comparing NULLs Without the Tricks has the plans.

One Oracle-specific hazard sits underneath all of this: in Oracle an empty string is null. Measured on all five with the same expression, Oracle was the only engine that said so:

SELECT CASE WHEN '' IS NULL THEN 'IS NULL' ELSE 'not null' END ...

Oracle 23ai        IS NULL
SQL Server 2022    not null
PostgreSQL 17.11   not null
MySQL 8.4.11       not null
Db2 12.1.5.0       not null

Any null-handling logic ported into or out of Oracle needs re-reading with that in mind, not just re-spelling. A WHERE col = '' that matches rows on four engines matches nothing on Oracle, and a NOT NULL constraint there also rejects the empty string.

3. NULLIF(col, 0)

Identical on all five, same syntax, same semantics, measured on each. It is the one line on the cheat sheet you can paste anywhere without a second thought.

4. CAST(col AS DATE)

Compiles on all five. It does something different on Oracle, because Oracle's DATE type is not a date: it carries year through second. Measured, on the same timestamp:

-- Oracle 23ai
SELECT CAST(TIMESTAMP '2024-05-17 13:45:12' AS DATE) FROM dual;
  2024-05-17 13:45:12      -- the time is still there
SELECT TRUNC(TIMESTAMP '2024-05-17 13:45:12') FROM dual;
  2024-05-17 00:00:00      -- this is the one that truncates

-- Db2 12.1, same expression
SELECT CAST(TIMESTAMP '2024-05-17 13:45:12' AS DATE) ...   -- 2024-05-17

So the cast that cleans a timestamp on four engines leaves the time in place on Oracle, and a GROUP BY on the result produces one group per second instead of one per day. The function that does what the cheat sheet describes is TRUNC(dt).

MySQL 8.4.11       NULL, a warning, and the query keeps going
                   (even under STRICT_TRANS_TABLES)
SQL Server 2022    Msg 241: Conversion failed when converting date
                   and/or time from character string.
PostgreSQL 17.11   ERROR: date/time field value out of range: "2024-02-31"
Oracle 23ai        ORA-01839: date not valid for month specified
Db2 12.1.5.0       SQL0181N The string representation of a datetime
                   value is out of range.

5. TRIM(col)

Works on all five today, and that is a recent development on SQL Server: TRIM arrived in SQL Server 2017, and the TRIM(characters FROM string) form only in SQL Server 2022 (measured working on 16.0.1200.5). On SQL Server 2016 and below the idiom is still LTRIM(RTRIM(col)).

Oracle's TRIM accepts only a single trim character, not a set, and it says so plainly rather than silently doing something else:

-- Oracle 23ai, measured
SELECT TRIM('xy' FROM 'xyabcxy') FROM dual;
  ORA-30001: trim set should have only one character

SELECT TRIM('x'  FROM 'xxabcxx') FROM dual;   -- [abc], fine

PostgreSQL, MySQL and Db2 all take a character set there. This is the good kind of incompatibility: it fails at parse time on the first run.

Fixed-length columns are the hidden version of this. Measured on SQL Server: CAST('a' AS char(5)) = 'a' is true and the value still occupies 5 bytes. The comparison ignores trailing blanks; DATALENGTH does not, and neither does whatever writes the value out to a file.

6. UPPER(col) and LOWER(col)

Present on all five with identical syntax. The comparison you do afterwards is what is not portable.

-- SQL Server 2022, collation Latin1_General_CI_AS, measured
SELECT CASE WHEN 'ABC' = 'abc' THEN 'true' ELSE 'false' END;   -- true

-- PostgreSQL 17.11, measured
SELECT 'ABC' = 'abc';                                          -- f

-- MySQL 8.4.11, measured
SELECT 'ABC' COLLATE utf8mb4_0900_ai_ci = 'abc' COLLATE utf8mb4_0900_ai_ci;  -- 1
SELECT 'ABC' COLLATE utf8mb4_0900_as_cs = 'abc' COLLATE utf8mb4_0900_as_cs;  -- 0

-- Oracle 23ai and Db2 12.1, measured
'ABC' = 'abc'  -->  not equal, on both

So the split is two against three: SQL Server and MySQL match by default, PostgreSQL, Oracle and Db2 do not. A query that leans on a case-insensitive default is correct on a typical SQL Server and on a typical MySQL, and silently returns fewer rows on the other three. That difference does not show up as an error anywhere. It shows up as a report with a missing line.

The analysis group

11. GROUP BY and HAVING: where an alias is allowed

The construct ports. The convenience around it does not, and the rules are not what most people assume.

Oracle in that table is the surprise, and it is version-specific. Grouping by a column alias was added in Oracle Database 23ai; on 19c and earlier the same statement fails. So this is a row where the answer depends on which Oracle you have in front of you, and where testing on the target version is the only thing that settles it.

Two engines accept an alias nowhere, one accepts it in GROUP BY only, and two accept it in both. A query written comfortably on MySQL or 23ai needs the expression repeated in full when it moves. The portable shape is to wrap the grouping expression in a derived table or CTE and group by the column name from that.

MySQL's other historical quirk is fixed by default now: ONLY_FULL_GROUP_BY is in the shipped sql_mode on 8.4 (measured), so selecting an ungrouped, unaggregated column raises ERROR 1055 instead of returning an arbitrary row.

There is a second split underneath that, and it goes the other way. Grouping by a primary key and then selecting another column from the same row is legal under the standard, because that column is functionally dependent on the key. Measured on the same three-row table:

SELECT id, name, SUM(amt) FROM fd GROUP BY id;    -- id is the primary key

PostgreSQL 17.11   returns both rows
MySQL 8.4.11       returns both rows, even under ONLY_FULL_GROUP_BY
SQL Server 2022    Msg 8120: Column 'fd.name' is invalid in the select list
                   because it is not contained in either an aggregate
                   function or the GROUP BY clause.
Oracle 23ai        ORA-00979: "NAME": must appear in the GROUP BY clause
                   or be used in an aggregate function
Db2 12.1.5.0       SQL0119N  An expression starting with "NAME" ... is not
                   specified in the GROUP BY clause

Two engines implement the standard's functional-dependency rule, three do not. So the query that is too strict for MySQL and the query that is too loose for SQL Server are different queries, and a port in either direction hits one of them. Listing every selected column in the GROUP BY is the shape that works on all five.

12. SUBSTRING(col, 1, 3)

The comma form runs on four of the five. Oracle does not have a function called SUBSTRING at all:

-- Oracle 23ai, measured
SELECT SUBSTRING(txt, 1, 3) FROM cheat;
  ORA-00904: "SUBSTRING": invalid identifier

And SQL Server has the exact mirror-image gap:

-- SQL Server 2022, measured
SELECT SUBSTR('abcdef', 1, 3);
  Msg 195: 'SUBSTR' is not a recognized built-in function name.

SELECT SUBSTRING('abcdef' FROM 1 FOR 3);
  Msg 156: Incorrect syntax near the keyword 'FOR'.

PostgreSQL and MySQL accept all three spellings (measured: SUBSTRING(x FROM 1 FOR 3), SUBSTRING(x, 1, 3) and SUBSTR(x, 1, 3) all return abc), and Db2 took both SUBSTRING(txt, 1, 3) and SUBSTR(txt, 1, 3). So SUBSTR(col, 1, 3) works everywhere except SQL Server, and SUBSTRING(col, 1, 3) works everywhere except Oracle. There is no third option that covers all five, which makes this the one string function you have to branch on.

Oracle adds a semantic difference on top: a negative start position counts from the end of the string. Measured, SUBSTR('abcdef', -3, 2) returns de.

13. WHERE col IN (SELECT ...)

Fully portable, including the trap. Measured identically on all five: if the subquery returns a single null, NOT IN returns no rows at all.

-- SQL Server 2022, PostgreSQL 17, Oracle 23ai, MySQL 8.4, Db2 12.1
-- all five returned 0
SELECT COUNT(*) FROM t
WHERE  n NOT IN (SELECT x FROM u);     -- u contains (2), (NULL)

This is three-valued logic doing exactly what the standard says, and it is the most consistent result in the whole exercise: five engines, five identical answers, and the answer surprises people every time. NOT EXISTS is the rewrite that behaves the way they expect, and it is equally portable.

14. WITH cte AS (SELECT ...)

Supported on all five, MySQL only since 8.0. Three differences that matter at porting time:

• The RECURSIVE keyword is incompatible in both directions. PostgreSQL and MySQL require WITH RECURSIVE; measured, the plain form fails on both (PostgreSQL: there is a WITH item named c, but it cannot be referenced from this part of the query; MySQL: Table 'demo.c' doesn't exist). SQL Server, Oracle and Db2 recurse without it, and the last two reject the keyword outright: Oracle answers ORA-02000: missing AS keyword and Db2 answers SQL0104N: An unexpected token "c" was found following "WITH RECURSIVE ". There is no spelling of a recursive CTE that compiles on all five.
• A CTE is not a temp table. Measured on SQL Server 2022: a CTE containing NEWID() and referenced twice in the same query produces two different values. It was evaluated twice. If the CTE body is expensive, referencing it three times costs three executions.
• PostgreSQL changed its mind about this. Up to version 11 a CTE was always an optimization fence, evaluated once and materialized. From version 12 it is inlined when it is referenced once and has no side effects, which changed the performance of a great deal of existing code. MATERIALIZED and NOT MATERIALIZED let you state the intent explicitly (measured working on 17.11). Oracle has the same idea as a hint.

The leading semicolon that T-SQL developers put in front of WITH is not superstition either: in T-SQL the preceding statement must be terminated, and ;WITH is how people guarantee it. On the other four it is unnecessary noise.

The window functions, and the two lines that are simply wrong

Lines 16 through 20 all compile on all five engines, MySQL since 8.0. That is the good news. Two of the five descriptions do not match what the SQL does, on any engine, and those are worth more attention than the lines that fail to parse: a parse error gets fixed in thirty seconds, a wrong number gets shipped.

19. SUM(col) OVER (ORDER BY date) is not a running total

It is a running total only if the ordering column is unique. Omitting the frame clause selects the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every row that ties on the ORDER BY value, not just the rows up to this one.

All five engines returned the same three numbers, to the digit. This is ANSI behaviour, not an engine quirk, which is both the good news and the bad news: it ports perfectly, and it is wrong in the same way everywhere. The fix is one clause and it ports too:

SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)

There is a second reason to be explicit. RANGE with a moving frame requires the engine to look up peer values rather than count rows, and on large partitions the row-based frame is the cheaper one to execute.

20. AVG(col) OVER (PARTITION BY region) is not a moving average

With no ORDER BY in the OVER clause there is no frame and no movement. Every row in the partition gets the same number: the average of the whole region. That is a useful thing, and it is not what the description says.

-- the group average, repeated on every row of the region
AVG(amount) OVER (PARTITION BY region)

-- an actual moving average, three-row trailing window
AVG(amount) OVER (PARTITION BY region
                  ORDER BY order_date
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Measured on SQL Server 2022, the second form over 10, 20, 60, 40 returns 10.00, 15.00, 30.00, 40.00, which is the trailing three-row mean at each step. The first form would have returned 32.50 four times.

-- the values 1 and 2, AVG(n)
SQL Server 2022   -->  1
Db2 12.1.5.0      -->  1
PostgreSQL 17.11  -->  1.5000000000000000
Oracle 23ai       -->  1.5
MySQL 8.4.11      -->  1.5000

A 33% error on an average, with no warning, in a query that ports without a single syntax change. That is the shape of the problem this whole article is about.

The same integer rule catches SUM(n) / COUNT(*), which truncates on SQL Server and on PostgreSQL (measured: 1), while MySQL returns 1.5000. So PostgreSQL agrees with SQL Server on operator division and disagrees with it on the avg aggregate, in the same session, on the same two values. There is no way to reason your way to that from first principles. You measure it.

16. RANK, and the sibling the cheat sheet leaves out

RANK() OVER (ORDER BY col DESC) is portable and does rank rows with ties, but it also leaves gaps after them, which is a choice and not a detail. Measured identically on SQL Server 2022 and MySQL 8.4 over the values 5, 5, 3:

Third place is rank 3 and dense rank 2. Which one is right depends entirely on whether your business definition of "second place" survives a tie. Window Functions: A Complete Guide works through the rest of the family.

17 and 18. LAG and LEAD

Portable, with one modern extension that is not. Skipping nulls to find the last real value is spelled IGNORE NULLS, and support splits cleanly:

-- SQL Server 2022 and Oracle 23ai, measured: both work, same syntax
SELECT LAG(n) IGNORE NULLS OVER (ORDER BY i) FROM ...   -- 5, 5 after the nulls

-- Db2 12.1.5.0, measured: works, but as a fourth argument
SELECT LAG(v, 1, CAST(NULL AS INT), 'IGNORE NULLS') OVER (ORDER BY i) ...

-- PostgreSQL 17.11, measured
  ERROR: syntax error at or near "NULLS"

-- MySQL 8.4.11, measured
  ERROR 1235 (42000): This version of MySQL doesn't yet support 'IGNORE NULLS'

Three engines support it, two do not, and the one that does support it spells it as a string literal in the argument list rather than as a clause. On PostgreSQL and MySQL the substitute is a MAX(...) OVER against a grouping key built from a conditional running count, which is considerably less readable and worth a comment in the code.

One report, five dialects

Here is the practical version of everything above: monthly revenue, distinct customers, a running total and the previous month, over the same seven-row orders table. This is the shape of query the cheat sheet is aiming at.

CREATE TABLE orders (
  order_id    int,
  order_date  date,
  region      varchar(20),
  customer_id int,
  amount      decimal(10,2)
);
-- 7 rows spread over January, February and March 2024

SQL Server 2022

WITH m AS (
  SELECT month_start = DATETRUNC(month, order_date),
         customers   = COUNT(DISTINCT customer_id),
         revenue     = SUM(amount)
  FROM   orders
  GROUP  BY DATETRUNC(month, order_date)
)
SELECT month_start,
       customers,
       revenue,
       running_total = SUM(revenue) OVER (ORDER BY month_start ROWS UNBOUNDED PRECEDING),
       prev_month    = LAG(revenue) OVER (ORDER BY month_start)
FROM   m
ORDER  BY month_start;

PostgreSQL 17

WITH m AS (
  SELECT date_trunc('month', order_date)::date AS month_start,
         COUNT(DISTINCT customer_id)           AS customers,
         SUM(amount)                           AS revenue
  FROM   orders
  GROUP  BY date_trunc('month', order_date)
)
SELECT month_start, customers, revenue,
       SUM(revenue) OVER (ORDER BY month_start ROWS UNBOUNDED PRECEDING) AS running_total,
       LAG(revenue) OVER (ORDER BY month_start)                          AS prev_month
FROM   m
ORDER  BY month_start;

MySQL 8.4

WITH m AS (
  SELECT CAST(DATE_FORMAT(order_date, '%Y-%m-01') AS DATE) AS month_start,
         COUNT(DISTINCT customer_id)                       AS customers,
         SUM(amount)                                       AS revenue
  FROM   orders
  GROUP  BY CAST(DATE_FORMAT(order_date, '%Y-%m-01') AS DATE)
)
SELECT month_start, customers, revenue,
       SUM(revenue) OVER (ORDER BY month_start ROWS UNBOUNDED PRECEDING) AS running_total,
       LAG(revenue) OVER (ORDER BY month_start)                          AS prev_month
FROM   m
ORDER  BY month_start;

Oracle and Db2 differ only in the truncation expression, which is the point:

-- Oracle
TRUNC(order_date, 'MM')                AS month_start

-- Db2 12.1: either of these
DATE_TRUNC('month', order_date)        AS month_start
TRUNC_TIMESTAMP(order_date, 'MONTH')   AS month_start

The three engines this report was run end to end on returned the identical result set:

month_start  customers  revenue  running_total  prev_month
-----------  ---------  -------  -------------  ----------
2024-01-01           2   500.00         500.00      (null)
2024-02-01           2   200.00         700.00      500.00
2024-03-01           2   400.00        1100.00      200.00

Everything in that query except the date truncation is portable, and the date truncation is a single expression. That is the shape to aim for: push the dialect into one place instead of spreading it through the statement.

Beyond the cheat sheet: the differences that break ports

The twenty lines do not cover the things that actually stop a migration. These do.

On SQL Server, measured: 7/2 returned 3; 'a' + NULL returned null while CONCAT('a', NULL) returned 'a'; SELECT 'a' || 'b' failed with incorrect syntax near '|'; SELECT CURRENT_DATE and SELECT DATE '2024-05-17' both failed to parse; SELECT CAST(1 AS boolean) failed with Msg 243: Type boolean is not a defined system type. Oracle rejected LIMIT 2 with ORA-03047; Db2 ran the same LIMIT 2 without complaint.

The GREATEST row is the quiet one. Given (1, NULL, 3), SQL Server and PostgreSQL skip the null and answer 3; Oracle, MySQL and Db2 propagate it and answer null. Both readings are defensible, neither is an error, and a "highest of these three columns" expression silently empties out on three of the five engines the moment one input is missing.

And one more that deserves its own line. || in MySQL is not an error. Measured, SELECT 'a' || 'b' returned 0: it is a logical OR of two strings that are not numbers. A concatenation ported from Oracle, PostgreSQL or Db2 into MySQL produces a number where a string used to be, and the query still runs.

The portable subset

After running the whole list, this is what survives everywhere without a rewrite:

• COALESCE, NULLIF, CASE WHEN, UPPER, LOWER, TRIM (SQL Server 2017 and up)
• COUNT(DISTINCT col) as an aggregate, never as a window function
• GROUP BY with HAVING, as long as you repeat the expression instead of using an alias
• IN and NOT EXISTS with subqueries; UNION ALL
• A non-recursive WITH clause. A recursive one has no spelling that works on all five.
• ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and windowed SUM, AVG, MIN, MAX, COUNT with an explicit ROWS frame
• EXTRACT, on all four engines that are not SQL Server

And this is what needs an abstraction layer, a per-dialect snippet, or a very careful comment:

• Every date function without exception, starting with truncation and difference
• String function names: SUBSTR against SUBSTRING, the concatenation operator, null handling inside concatenation
• Row limiting. Even FETCH FIRST, which four of the five accept, is not portable: SQL Server rejects the bare form and requires OFFSET 0 ROWS FETCH NEXT n ROWS ONLY (measured: Invalid usage of the option FIRST in the FETCH statement)
• Identifier quoting, and case folding of unquoted identifiers
• Anything that depends on collation, integer versus decimal arithmetic, or on empty string not being null

Q&A

The Bottom Line

Of twenty lines presented as "SQL", three fail to compile on SQL Server, three on Oracle, one each on PostgreSQL, MySQL and Db2, and two run on all five while describing something they do not do. The parse failures are the harmless kind: they announce themselves the first time you press F5. The other two are not.

SUM(col) OVER (ORDER BY date) is a running total only when the date is unique, and AVG(col) OVER (PARTITION BY region) was never a moving average. Both compile on all five engines. Both are exactly the kind of line that goes into a report, gets checked against a total that happens to match, and stays there.

If you take one habit away from this: write the frame clause. ROWS UNBOUNDED PRECEDING is six characters of insurance, it ports to every engine on this list, and it makes the query say what you meant rather than what the default happened to be.