Part 7 — What the market actually runs › Chapter 44

The dialects: SQL Server, Oracle, PostgreSQL, MySQL

The same query in four dialects, the differences that matter, and how to answer “which do you know?”.

From the advert

“Ottima conoscenza del linguaggio MS T-SQL” — and the honest reassurance that T-SQL is the standard you already know plus a lookup table you can absorb in a week.

Most of it is the same

🧒 Simple

Italian, Spanish and Portuguese. If you speak one properly you can read the others on day one and speak one badly by the end of the week. The grammar is shared; what changes is a few hundred words and a handful of habits.

SELECT, joins, GROUP BY, subqueries, CTEs and window functions are standard and behave the same everywhere. What differs is the top-N syntax, the date functions, string concatenation, identity generation and the procedural language — and that is genuinely most of the list.

The lookup table

	SQL Server	Oracle	PostgreSQL	MySQL
Limit rows	`TOP (10)`	`FETCH FIRST 10 ROWS ONLY`	`LIMIT 10`	`LIMIT 10`
Null default	`ISNULL(a,b)`	`NVL(a,b)`	`COALESCE`	`IFNULL`
Concatenate	`a + b`	`a \|\| b`	`a \|\| b`	`CONCAT(a,b)`
Current time	`SYSDATETIME()`	`SYSTIMESTAMP`	`now()`	`NOW()`
Add days	`DATEADD(day,7,d)`	`d + 7`	`d + INTERVAL '7 day'`	`DATE_ADD(d,INTERVAL 7 DAY)`
Auto key	`IDENTITY`	`SEQUENCE` / identity	`GENERATED … AS IDENTITY`	`AUTO_INCREMENT`
Quote identifier	`[name]`	`"NAME"`	`"name"`	`name`
Temp table	`#tmp`	Global temp table	`CREATE TEMP TABLE`	`CREATE TEMPORARY TABLE`
Upsert	`MERGE`	`MERGE`	`ON CONFLICT`	`ON DUPLICATE KEY`
String position	`CHARINDEX`	`INSTR`	`POSITION`/`strpos`	`INSTR`/`LOCATE`
Cast	`CAST`/`CONVERT`	`CAST`/`TO_CHAR`	`CAST`/`::`	`CAST`

The differences that are not syntax

🎓 Six that change how you design, not just how you type

Clustering. SQL Server and InnoDB cluster by the primary key, so that choice is a physical storage decision and every secondary index inherits its width. PostgreSQL has no clustered index at all.
Concurrency model. PostgreSQL, Oracle and InnoDB are MVCC by default; SQL Server locks until you enable READ_COMMITTED_SNAPSHOT. That single setting changes how much of the legacy T-SQL you will read makes sense.
Transactional DDL. PostgreSQL and SQL Server yes; MySQL and Oracle no. This decides how migrations must be written (chapter 36).
The empty string. Oracle treats '' as NULL. Every other engine treats it as a zero-length string. This causes genuine bugs when porting and it is the single most surprising Oracle behaviour.
Nulls in a unique index. SQL Server allows one; PostgreSQL, Oracle and MySQL allow many.
Case sensitivity. PostgreSQL folds unquoted identifiers to lowercase, Oracle to uppercase. So a quoted "MyTable" in one is not the same object as MyTable — the classic cause of "the table exists but the query says it does not".

The Oracle trap worth knowing

The Oracle trap worth knowing: ROWNUM is assigned before the sort.

-- WRONG. ROWNUM is assigned as rows are PRODUCED, before ORDER BY runs, so this
-- takes an arbitrary 10 rows and then sorts those ten. The classic Oracle mistake.
SELECT * FROM orders WHERE ROWNUM <= 10 ORDER BY total_cents DESC;   -- 10 arbitrary rows

-- Right, the old way: sort in a subquery, THEN take the first ten.
SELECT * FROM (SELECT * FROM orders ORDER BY total_cents DESC)       -- sorted first…
WHERE ROWNUM <= 10;                                                  -- …then numbered

-- Right, and standard, from Oracle 12c:
SELECT * FROM orders ORDER BY total_cents DESC FETCH FIRST 10 ROWS ONLY;  -- says what it means

Why the first version is not simply “slower”

It returns the wrong rows. Not fewer, not slower — different. And it looks correct, because ten rows come back in descending order.
The general lesson is about evaluation order: WHERE runs before ORDER BY, so anything the WHERE depends on must be knowable before sorting. ROWNUM is assigned at production time, and that is the whole trap.
The same reasoning explains why WHERE rn = 1 needs a wrapper around a window function (chapter 13). One rule, two dialects.

Writing portable SQL — and when not to

✔ Cheap portability, worth doing by default

COALESCE rather than ISNULL/NVL — standard, n-ary, and it will not truncate your default.
CASE rather than IIF/DECODE.
Explicit JOIN syntax, never comma joins with conditions in WHERE.
Standard FETCH FIRST … ROWS ONLY where the engine supports it.
Half-open date ranges rather than BETWEEN.

⚠ Expensive portability, usually not worth it

Avoiding window functions because an ancient version lacked them; refusing ON CONFLICT and hand-rolling an upsert; building an abstraction layer so the SQL could target four engines. That last one is the classic: almost nobody ever changes engine, and the abstraction costs you the specific features that made the engine worth choosing. Portability is a habit worth having and a project worth refusing.

Answering “which databases do you know?”

✔ Depth in one, honest levels for the rest

“SQL Server is where I am deepest — I have written the reporting views and done the query tuning, and I am comfortable in T-SQL including error handling and execution plans. I read PL/SQL comfortably and have written simple procedures, but I have not shipped a package. I use PostgreSQL for personal projects and prefer it, and I know the main differences from SQL Server: no clustered index, MVCC by default, transactional DDL.”

Three named levels, one concrete claim each, and one sentence that proves the comparison is real. It beats a list of nine products every time — and it is unbluffable, which is the point.

One question, four dialects: write it in each without looking, then check.

-- "Top 5 customers by 2026 revenue, excluding cancelled orders."

-- SQL Server
SELECT TOP (5) c.name,                            -- TOP goes BEFORE the column list…
       SUM(o.total_cents)/100.0 AS eur            -- …and takes no ORDER BY of its own
FROM orders o JOIN customers c ON c.id = o.customer_id   -- facts, joined to the name
WHERE o.placed_at >= '2026-01-01'                 -- half-open range, so it stays sargable…
  AND o.placed_at <  '2027-01-01'                 -- …at any precision
  AND o.status <> 'cancelled'                     -- cancellations are not revenue
GROUP BY c.name ORDER BY eur DESC;                -- one row per customer, biggest first

-- PostgreSQL / MySQL / SQLite: identical, with LIMIT 5 at the END instead of TOP.
-- Oracle 12c+: identical, with FETCH FIRST 5 ROWS ONLY at the end.

What is portable here, and what is not

Everything except the row limit. The join, the half-open date range, the aggregate, the grouping and the ordering are identical on all four engines — which is the chapter's whole point: about 85% of what you write is portable.
ORDER BY eur uses the SELECT alias, which every one of the four permits because ORDER BY runs last. GROUP BY c.name could not use an alias so portably.
/100.0 not /100: integer division would silently drop the cents on every engine here.
Grouping by c.name merges two customers who share a name. c.id, c.name is the honest version — worth saying out loud even in an exercise.

The standard is most of it. The differences are top-N, dates, concatenation, identity and the procedural language.
SQL Server and InnoDB cluster by the primary key; PostgreSQL has no clustered index.
SQL Server blocks readers on writers until READ_COMMITTED_SNAPSHOT is enabled.
DDL is transactional in PostgreSQL and SQL Server, not in MySQL or Oracle.
Oracle treats the empty string as NULL. Nothing else does.
SQL Server permits one null in a unique index; the others permit many.
PostgreSQL folds unquoted identifiers to lower case, Oracle to upper case.
ROWNUM is applied before ORDER BY. Use FETCH FIRST.
Cheap portability is a habit; a database abstraction layer is a project that rarely pays.
Claim depth in one engine with a concrete example, and give honest levels for the rest.

Interview questions this chapter answers

You only know PostgreSQL and we use SQL Server. Is that a problem?

The language is largely the same — joins, grouping, CTEs, window functions are standard. What I would need to absorb is the T-SQL spellings and the procedural dialect, which is a week, and the behavioural differences that matter: clustered indexes, the locking model unless read-committed snapshot is on, and identity versus sequences. I would rather name those specifically than claim it is nothing.

Name a difference between SQL Server and PostgreSQL that affects design.

Clustered indexes. In SQL Server the primary key normally determines physical row order and every non-clustered index carries it as the row locator, so the key's width and sequentiality matter enormously. PostgreSQL has no clustered index — the table is a heap and every index is secondary — so that whole consideration disappears.

Should SQL be written to be portable across engines?

The cheap parts, yes — COALESCE, explicit joins, standard FETCH FIRST, half-open date ranges. Building an abstraction layer so the same code runs on four engines, no: almost nobody changes engine, and it costs exactly the features that made the engine worth choosing.