Database-Specific SQL

This module is a side-by-side reference of how common tasks are written in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

Concept

This module is a side-by-side reference of how common tasks are written in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. Standard SQL is preferred when available; the table marks dialect-specific syntax explicitly.

Why It Matters

Teams migrate between databases, work with several at once, or read examples written for a different system. Knowing the differences avoids subtle bugs (argument order in DATEDIFF, empty string = NULL in Oracle, || as OR in MySQL).

Syntax

Versions assumed: MySQL 8.0+, PostgreSQL 14+, SQL Server 2019+, Oracle 19c+ (23ai noted), SQLite 3.35+.

Example

47.1 Auto-generated IDs id BIGINT AUTO_INCREMENT PRIMARY KEY → LAST_INSERT_ID()

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or BIGSERIAL) → RETURNING id

id BIGINT IDENTITY(1,1) PRIMARY KEY → OUTPUT INSERTED.id, SCOPE_IDENTITY()

id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (12c+) or SEQUENCE → RETURNING id INTO :v

id INTEGER PRIMARY KEY [AUTOINCREMENT] → last_insert_rowid(), RETURNING 47.2 String functions CONCAT(a, b) a || b, CONCAT a + b, CONCAT a || b, CONCAT(a,b) Length (chars) SUBSTRING(s, p, n) SUBSTR(s, p, n) LOCATE(sub, s) POSITION(sub IN s) CHARINDEX(sub, s) INSTR(s, sub) SUBSTR(s, 1, n) GROUP_CONCAT(x SEPARATOR ',') STRING_AGG(x, ',')

LISTAGG(x, ',') WITHIN GROUP (ORDER BY x) GROUP_CONCAT(x, ',') REGEXP_LIKE(s, p) / s REGEXP p LIKE patterns (REGEXP_LIKE in 2025) REGEXP_LIKE(s, p) REGEXP needs extension Default collation is CI

UPPER(s) LIKE / REGEXP_LIKE(s,p,'i') LIKE is CI for ASCII 47.3 Date functions NOW(), CURRENT_TIMESTAMP SYSDATETIME(), GETDATE() CURRENT_TIMESTAMP (UTC) CAST(GETDATE() AS DATE) DATE_ADD(d, INTERVAL 7 DAY) d + INTERVAL '7 days' DATEADD(day, 7, d) DATE(d, '+7 days') DATEDIFF(d2, d1) DATEDIFF(day, d1, d2) JULIANDAY(d2) - JULIANDAY(d1) EXTRACT(YEAR FROM d) STRFTIME('%Y', d) DATE_FORMAT(d, '%Y-%m-01') DATE_TRUNC('month', d) DATEFROMPARTS(YEAR(d), MONTH(d), 1) TRUNC(d, 'MM') DATE(d, 'start of month') DATE_FORMAT(d, '%Y-%m') TO_CHAR(d, 'YYYY-MM') FORMAT(d, 'yyyy-MM') STRFTIME('%Y-%m', d) STR_TO_DATE(s, '%d-%m-%Y') TO_DATE(s, 'DD-MM-YYYY') CONVERT(DATE, s, 105) 47.4 Pagination and LIMIT / TOP / FETCH LIMIT 10 OFFSET 20 LIMIT 10 / FETCH FIRST 10 ROWS ONLY

OFFSET 20 LIMIT 10 / OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY SELECT TOP (10)...

ORDER BY... OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY FETCH FIRST 10 ROWS ONLY (12c+); ROWNUM (older)

OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY 47.5 Boolean support BOOLEAN = TINYINT(1) TRUE/FALSE = 1/0 TRUE, FALSE, 't', 'f'

1, 0 (no boolean expressions in SELECT list; use CASE) BOOLEAN in 23ai; earlier NUMBER(1)/CHAR(1) TRUE/FALSE keywords (3.23+) 47.6 JSON support doc->>'$.name', JSON_EXTRACT

Generated column + index, multi-valued index JSON, JSONB

doc->>'name', doc #>> '{a,b}', jsonb_path_query GIN index on JSONB, expression index

NVARCHAR + functions (json type in 2025/Azure) JSON_VALUE(doc, '$.name'), OPENJSON Computed column + index JSON (21c+), CLOB with IS JSON

JSON_VALUE(doc, '$.name'), dot notation JSON search index, function-based index TEXT + JSON1 functions

json_extract(doc, '$.name'), doc->>'$.name' (3.38+) 47.7 Procedures, functions, triggers Yes (11+, CALL) Yes (PL/SQL, packages) PL/pgSQL (+ others) Yes (RETURNS TABLE, SETOF) Yes (inline, multi-statement) BEFORE/AFTER/INSTEAD OF (views)

No (statement-level with inserted/deleted) BEFORE/AFTER/INSTEAD OF Yes (only kind) SIGNAL SQLSTATE '45000' RAISE EXCEPTION THROW / RAISERROR RAISE(ABORT, 'msg') in triggers 47.8 CTEs and window functions WITH RECURSIVE WITH (no keyword) 2005+ (frames 2012+) FILTER (WHERE...) NULLS FIRST/LAST FULL OUTER JOIN No (emulate with UNION) INTERSECT / EXCEPT INTERSECT, MINUS (EXCEPT 21c+) No (ON DUPLICATE KEY UPDATE) 15+ (ON CONFLICT also) No (ON CONFLICT)

47.9 Identifier quoting and other differences ` order (or " with ANSI_QUOTES) [order] or "order" "order", order , [order] Unquoted identifier case Table names case-sensitive on Linux Folded to lowercase Depends on collation Folded to UPPERCASE '' (empty string) Logical OR (default) Not supported (use +) Integer division 7/2 SELECT without FROM FROM dual (until 23ai) No (implicit commit) ON DUPLICATE KEY UPDATE ON CONFLICT... DO UPDATE / MERGE ON CONFLICT... DO UPDATE`

Expected Result

Same task — "latest 5 orders with customer name" — in each dialect:

All return orders 1006, 1005, 1004, 1003, 1002 with their customers' names.

-- MySQL / SQLite
SELECT o.order_id, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC LIMIT 5;

-- PostgreSQL (either form)
SELECT o.order_id, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC FETCH FIRST 5 ROWS ONLY;

-- SQL Server
SELECT TOP (5) o.order_id, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC;

-- Oracle 12c+ (no AS for table aliases)
SELECT o.order_id, c.name FROM orders o JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC, o.order_id DESC FETCH FIRST 5 ROWS ONLY;

Common Mistakes

Assuming MySQL's || concatenates.

Reversing DATEDIFF arguments between MySQL and SQL Server.

Forgetting Oracle's empty-string-is-NULL rule.

Using AS for table aliases in Oracle.

Relying on transactional DDL in MySQL/Oracle migrations.

Best Practices

Write standard SQL first; isolate dialect-specific parts.

Label every shared snippet with its dialect.

Run the test suite on the actual target DBMS and version.

Interview Relevance

Medium: "How do you paginate in SQL Server vs MySQL?", "Differences between PostgreSQL and MySQL?", "How do you do an upsert in your DB?".

Practice Questions

Translate a PostgreSQL query using ILIKE, DATE_TRUNC, and LIMIT to SQL Server.

Write an upsert for each of the five DBMSs.

List three behaviors that make porting from Oracle to PostgreSQL tricky.

Emulate FULL OUTER JOIN in MySQL.