MySQL, PostgreSQL and SQL Server share more than most people expect, and they differ in more than they hope. The core of the language is genuinely standard. The SELECT, the JOIN, the GROUP BY, the window functions and the CTEs all behave the same in PostgreSQL, in MySQL and in SQL Server.
What differs is the dialect around the edges: how you quote an identifier, how you limit the rows, how you spell an upsert, and which date function extracts a month.
You learn the core once, and you learn the edges by lookup. After that, a query moves between the engines with a handful of edits instead of a rewrite.
The previous lesson in this module covered "What Query Conventions Keep Team SQL Maintainable?". The next lesson in this module is about "What Goes Into a Complete Sales Analytics Project?".
What differs between MySQL, PostgreSQL and SQL Server?
This lesson compares the three engines side by side. Nothing here runs in a single engine, because the point is that the same idea has three spellings. The spelling a feature needs is the thing to look up, not something to memorise in advance.
How do the engines quote identifiers?
MySQL uses backticks, and SQL Server uses square brackets. Both of them accept them around a reserved word such as order or user.
PostgreSQL prefers double quotes, and this is the part that catches people. In PostgreSQL, double quotes mean "a case-sensitive identifier", while single quotes mean "a string literal". MySQL treats double quotes as a string literal by default, so a query written with the PostgreSQL quoting silently produces a string where PostgreSQL expects a name.
The PostgreSQL spelling, quoting an identifier that is also a keyword.
SELECT "order" FROM "order_line" WHERE "user" = 'ada';The MySQL spelling, which is the same idea in backticks.
SELECT `order` FROM `order_line` WHERE `user` = 'ada';The SQL Server spelling, which is the same idea again in square brackets.
SELECT [order] FROM [order_line] WHERE [user] = 'ada';The portable advice is the same as it has been since Module 02. Do not use reserved words for column names, and then you never have to quote anything.
How do the engines join strings?
The || operator is standard SQL, and PostgreSQL and SQLite implement it.
MySQL, unless the PIPES_AS_CONCAT server mode is enabled, treats || as a logical OR. That is a genuinely dangerous difference, because the query runs and returns the wrong rows instead of failing.
SQL Server uses + for strings and + for numbers alike, which means that a concatenation with a NULL silently produces a NULL rather than an error.
The PostgreSQL spelling, using the standard operator or the function.
SELECT first_name || ' ' || last_name AS full_name FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;The MySQL spelling, where CONCAT is the only safe choice.
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM customers;The SQL Server spelling, with CONCAT as the NULL-safe option.
SELECT first_name + ' ' + last_name AS full_name FROM customers;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;MySQL's CONCAT_WS and PostgreSQL's concat_ws are close relatives that skip NULL arguments, which makes them the tidiest way to build a label from optional parts.
SQL Server has no direct equivalent. It uses COALESCE, or CONCAT_WS from SQL Server 2017 onwards.
How do the engines limit rows?
LIMIT is the standard, and it works in PostgreSQL and in MySQL.
SQL Server has TOP, which goes before the select list, and it also supports the standard FETCH FIRST since version 2012.
Offset paging is LIMIT n OFFSET m in PostgreSQL and in MySQL, and it is OFFSET m ROWS FETCH NEXT n ROWS ONLY in SQL Server.
The PostgreSQL and the MySQL spelling, which is the same in both.
SELECT id FROM sales ORDER BY id LIMIT 10 OFFSET 20;The SQL Server spelling, with TOP.
SELECT TOP 10 id FROM sales ORDER BY id OFFSET 20 ROWS;The SQL Server spelling, with the standard form. It is the safer one, because other engines accept it too.
SELECT id FROM sales ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;One portability warning belongs here. LIMIT without ORDER BY returns an arbitrary subset. If the same query has to return the same rows every time, then the ordering has to be explicit, and the ordering key has to be unique.
How does each engine upsert?
The upsert from Module 35 has three spellings.
PostgreSQL and modern SQLite use ON CONFLICT. MySQL uses ON DUPLICATE KEY UPDATE, which is triggered by any unique key and not by a named column. SQL Server and Oracle use the full MERGE statement, and PostgreSQL added MERGE in version 15.
The PostgreSQL and the SQLite spelling, with the conflict target named in the statement.
INSERT INTO customers (customer_id, full_name) VALUES (1, 'Acme Ltd')
ON CONFLICT (customer_id) DO UPDATE SET full_name = EXCLUDED.full_name;The MySQL spelling, where the key itself decides what happens.
INSERT INTO customers (customer_id, full_name) VALUES (1, 'Acme Ltd')
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);The SQL Server spelling, with MERGE and its two branches: matched, and not matched.
MERGE INTO customers AS target
USING (SELECT 1 AS customer_id, 'Acme Ltd' AS full_name) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET target.full_name = source.full_name
WHEN NOT MATCHED THEN INSERT (customer_id, full_name) VALUES (source.customer_id, source.full_name);MySQL's VALUES(full_name) is deprecated in favour of a row alias in MySQL 8.0.19 and later, and PostgreSQL's EXCLUDED is the same idea under a different name.
MySQL also fires the BEFORE INSERT triggers on an upsert, where PostgreSQL's ON CONFLICT does not. That is the sort of difference that makes cross-engine testing worthwhile and not optional.
What about the types and the dates?
Dates are the biggest source of cross-engine surprise. A date stored one way and read another way gives you a wrong month, and a wrong month is hard to notice.
PostgreSQL has a real DATE type and a family of timestamp types. MySQL has DATE, DATETIME, and TIMESTAMP with its own zero-date rules. SQL Server has DATE, DATETIME2, and a legacy DATETIME with millisecond rounding.
Extracting a month therefore has three spellings.
The PostgreSQL spelling, with the standard EXTRACT and the to_char function.
SELECT EXTRACT(MONTH FROM sale_date) AS month FROM sales;
SELECT to_char(sale_date, 'YYYY-MM') AS month_key FROM sales;The MySQL spelling, with MONTH and DATE_FORMAT.
SELECT MONTH(sale_date) AS month FROM sales;
SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month_key FROM sales;The SQL Server spelling, with DATEPART and FORMAT.
SELECT DATEPART(month, sale_date) AS month FROM sales;
SELECT FORMAT(sale_date, 'yyyy-MM') AS month_key FROM sales;None of these three is the standard EXTRACT that the SQL standard specifies, and it is one of the few places where the standard and the market diverge completely.
MySQL's format strings use percent signs, and its STR_TO_DATE is picky about the order. SQL Server's FORMAT is slow on large result sets, and its CONVERT with a style code is usually the better tool.
The portable fallback is the one from Module 03. Store the dates as ISO text, and extract them with SUBSTR.
How do booleans and generated identifiers differ?
PostgreSQL has a real boolean type. MySQL and SQL Server do not. MySQL stores a boolean as a tinyint with the 0 and the 1, and SQL Server stores it as a bit. SQLite, as Module 45 showed, stores it as an integer as well.
A query written with WHERE completed = TRUE works in PostgreSQL and fails in MySQL. An engine with no boolean type has no TRUE keyword either.
The PostgreSQL spelling, with a real boolean and an identity column.
CREATE TABLE flags (id INTEGER GENERATED ALWAYS AS IDENTITY, is_done BOOLEAN NOT NULL DEFAULT FALSE);The MySQL spelling, with TINYINT(1) and AUTO_INCREMENT.
CREATE TABLE flags (id INT AUTO_INCREMENT PRIMARY KEY, is_done TINYINT(1) NOT NULL DEFAULT 0);The SQL Server spelling, with BIT and IDENTITY(1,1).
CREATE TABLE flags (id INT IDENTITY(1,1) PRIMARY KEY, is_done BIT NOT NULL DEFAULT 0);The same three-way split applies to generated identifiers, and Module 46 covered it in detail. All three have an integer identity column, with the exact syntax above.
What is genuinely portable?
There is a long list, and it covers most of what this course has taught.
The SELECT list, the aliases, and the expressions.
All the join types, including the outer joins and the CROSS JOIN.
The WHERE, the GROUP BY, the HAVING, and the aggregation functions.
The CASE expression.
The window functions with PARTITION BY and ORDER BY, and the whole ranking, framing, LAG and LEAD family. All three engines have supported these since 2012.
The common table expressions, including the recursive ones.
The INSERT, the UPDATE, the DELETE, and the basic CREATE TABLE syntax with the portable type names: INTEGER, REAL, TEXT, VARCHAR(n), DATE, and TIMESTAMP.
And the transaction statements: BEGIN, COMMIT, and ROLLBACK, with savepoints in all three.
What is not portable is the edge: identifier quoting, string concatenation, row limiting, upserts, date functions, boolean literals, generated identifiers, and the proprietary extras each engine adds. PostgreSQL has arrays and JSONB, MySQL has GROUP_CONCAT, and SQL Server has APPLY and PIVOT.
You learn where the edges are, and then you can write a query that runs on all three. Or you can deliberately use one engine's edge and say so in a comment, so that the next reader knows the price of that choice.
Quick reference
- The core is the standard. The joins, the aggregates, the
CASE, the window functions, the CTEs and the transactions all port. - The identifiers are backticks in MySQL, double quotes in PostgreSQL, and square brackets in SQL Server.
- The concatenation is the standard
||, but MySQL needsCONCAT, andCONCAT_WSis the tidiest option. - The row limits are
LIMITeverywhere, except for theTOPor theFETCH FIRSTof SQL Server. - The upserts are
ON CONFLICT,ON DUPLICATE KEY UPDATE, andMERGErespectively. - The dates are the
EXTRACT, theDATE_FORMAT, and theDATEPART, which are three spellings of one idea. Or you store ISO text and you useSUBSTR.
Portability drill: you write the same query three times, once per dialect, to list the top three customers by revenue. Then you mark every difference you had to make. That list is your portability budget, and it is usually shorter than people expect.