SQL Server vs MySQL vs PostgreSQL: Syntax Cheat Sheet

The same everyday task, written for SQL Server, MySQL and PostgreSQL, side by side. Use it when you move between databases, read a tutorial written for a different one, or port a query from one system to another.

MySQL entries are for MySQL 8 and later (most also work in MariaDB); PostgreSQL entries are for version 14 and later. Where a SQL Server feature needs a recent version, the version is noted. The MySQL and PostgreSQL entries were tested on MariaDB 10.11 and PostgreSQL 16.

Want it on your desk? All 31 tasks and the three gotchas fit on one printable page.

Selecting and paging

TaskSQL ServerMySQL / MariaDBPostgreSQL
Top 5 rowsSELECT TOP (5) ... ORDER BY x... ORDER BY x LIMIT 5... ORDER BY x LIMIT 5
Page 3 (20 per page)ORDER BY x OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLYORDER BY x LIMIT 20 OFFSET 40ORDER BY x LIMIT 20 OFFSET 40
Two-way IFIIF(cond, a, b)IF(cond, a, b)CASE WHEN cond THEN a ELSE b END
Replace NULLCOALESCE(x, 0) / ISNULL(x, 0)COALESCE(x, 0) / IFNULL(x, 0)COALESCE(x, 0)
NULL-safe equala IS NOT DISTINCT FROM b (2022+)a <=> ba IS NOT DISTINCT FROM b
Case-insensitive LIKELIKE (default collation)LIKE (default collation)ILIKE
Regex matchREGEXP_LIKE(s, 'p') (2025+)s REGEXP 'p's ~ 'p'

Text

TaskSQL ServerMySQL / MariaDBPostgreSQL
Join textCONCAT(a, b) or a + bCONCAT(a, b)a || b or concat(a, b)
Length (characters)LEN(s)CHAR_LENGTH(s)length(s)
Find positionCHARINDEX('x', s)LOCATE('x', s)strpos(s, 'x')
Part of a stringSUBSTRING(s, 2, 3)SUBSTRING(s, 2, 3)substring(s, 2, 3)
Rows to one listSTRING_AGG(col, ', ')GROUP_CONCAT(col SEPARATOR ', ')string_agg(col, ', ')
Split ‘a,b,c’ to rowsSTRING_SPLIT(s, ',')JSON_TABLE(...) trickstring_to_table(s, ',')

Dates

TaskSQL ServerMySQL / MariaDBPostgreSQL
NowSYSDATETIME() / GETDATE()NOW()now()
Today (no time)CAST(GETDATE() AS date)CURDATE()current_date
Add 7 daysDATEADD(day, 7, d)d + INTERVAL 7 DAYd + INTERVAL '7 days'
Days betweenDATEDIFF(day, d1, d2)DATEDIFF(d2, d1)d2 - d1
Year of a dateYEAR(d)YEAR(d)EXTRACT(YEAR FROM d)
First day of monthDATEFROMPARTS(YEAR(d), MONTH(d), 1)DATE_FORMAT(d, '%Y-%m-01')date_trunc('month', d)::date
Last day of monthEOMONTH(d)LAST_DAY(d)(date_trunc('month', d) + INTERVAL '1 month - 1 day')::date
ISO week numberDATEPART(iso_week, d)WEEK(d, 3)EXTRACT(WEEK FROM d)
UTC to Bahrain timet AT TIME ZONE 'UTC' AT TIME ZONE 'Arab Standard Time'CONVERT_TZ(t, '+00:00', '+03:00')t AT TIME ZONE 'Asia/Bahrain'

Tables and changes

TaskSQL ServerMySQL / MariaDBPostgreSQL
Auto-number columnINT IDENTITY(1,1)INT AUTO_INCREMENTINT GENERATED ALWAYS AS IDENTITY
True / false columnBITBOOLEAN (TINYINT(1))boolean
Unicode textNVARCHAR(100)VARCHAR(100) (utf8mb4)varchar(100) or text
Get the new IDOUTPUT inserted.id / SCOPE_IDENTITY()LAST_INSERT_ID()RETURNING id
Insert or updateMERGE or UPDATE + IF @@ROWCOUNT = 0 INSERTINSERT ... ON DUPLICATE KEY UPDATEINSERT ... ON CONFLICT (key) DO UPDATE
Replace a viewCREATE OR ALTER VIEW v AS ...CREATE OR REPLACE VIEW v AS ...CREATE OR REPLACE VIEW v AS ...
Run a procedureEXEC proc @p = 1CALL proc(1)CALL proc(1)
See the query planCtrl+M in SSMS / SET STATISTICS IO ONEXPLAIN ANALYZE SELECT ...EXPLAIN ANALYZE SELECT ...

Three Differences That Catch People Out

  • Argument order in date differences. SQL Server’s DATEDIFF(day, start, end) puts the start first; MySQL’s DATEDIFF(end, start) puts the end first. Swap them and the sign flips.
  • Case sensitivity. SQL Server and MySQL compare text case-insensitively with their default collations; PostgreSQL is case-sensitive, so use ILIKE or lower().
  • Unquoted names. PostgreSQL folds unquoted table and column names to lowercase, so Customers becomes customers. Use lowercase names with underscores to avoid quoting everywhere.

Keep a copy next to your keyboard: download the one-page PDF version of this cheat sheet.

Learn More

Scroll to Top