One Query, Four Dialects: Differences Between SQL Server, PostgreSQL, MySQL and SQLite

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)), PostgreSQL 18.6, MySQL 8.4.13 and SQLite 3.54, with the same Bookstore data.

Guess First

I loaded Bookstore's books, customers, orders, order_items and stock_items tables with the same data into four databases: SQL Server, PostgreSQL, MySQL and SQLite. Then I ran three simple expressions on all four: how many books does the condition title = 'dune' find (the title in the catalog is capitalised, "Dune"), what is 7 / 2, and what does 'a' || 'b' return? Guess the results:

The real output on SQL Server:

dune_matches int_div pipes plus_null concat_null
------------ ------- ----- --------- -----------
           1       3 ab         NULL a

PostgreSQL:

 dune_matches | int_div | pipes | pipes_null | concat_null 
--------------+---------+-------+------------+-------------
            0 |       3 | ab    |            | a
(1 row)

MySQL:

+--------------+---------+------------+-------+-------------+
| dune_matches | int_div | int_div_op | pipes | concat_null |
+--------------+---------+------------+-------+-------------+
|            1 |  3.5000 |          3 |     0 | NULL        |
+--------------+---------+------------+-------+-------------+

SQLite:

+--------------+-----------+---------+-------+------------+-------------+
| dune_matches | dune_like | int_div | pipes | pipes_null | concat_null |
+--------------+-----------+---------+-------+------------+-------------+
|            0 |         1 |       3 | ab    |            | a           |
+--------------+-----------+---------+-------+------------+-------------+

Case: SQL Server and MySQL found "dune", PostgreSQL and SQLite did not (although SQLite's LIKE did). Division: MySQL said 3.5000, the others 3. And 'a' || 'b': three said "ab", MySQL said 0, because in MySQL || is logical OR by default and it converts both strings to numbers before comparing. This post settles the account of the rule we have followed since the start of the series, "the standard first, then T-SQL (Transact-SQL), then a note on differences": we put the same Bookstore queries side by side in four dialects.

Paging: TOP, OFFSET/FETCH and LIMIT

Let us write the "newest books, page 3" query from post 05 of this series with 5 books per page. On SQL Server, the standard OFFSET ... FETCH:

SELECT book_id, title, listed_on
FROM dbo.books
ORDER BY listed_on DESC, book_id DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

book_id title                                             listed_on
------- ------------------------------------------------- ----------
     67 The Little Prince                                 2026-01-11
     66 The Algorithm Design Manual                       2025-12-27
     65 The Art of Computer Programming                   2025-12-22
     64 Structure and Interpretation of Computer Programs 2025-12-02
     63 Domain-Driven Design                              2025-11-26

PostgreSQL accepted the same standard syntax verbatim and returned the same five rows. But the common form in the PostgreSQL world is LIMIT:

SELECT book_id, title, listed_on
FROM books
ORDER BY listed_on DESC, book_id DESC
LIMIT 5 OFFSET 10;

MySQL and SQLite do not recognise the standard OFFSET ... FETCH; both only have LIMIT:

SELECT book_id, title, listed_on
FROM books
ORDER BY listed_on DESC, book_id DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY' at line 4

LIMIT 5 OFFSET 10 works on three of the four engines and not on SQL Server. OFFSET ... FETCH works on SQL Server and PostgreSQL. SQL Server's old TOP (n) is not standard and only covers the first page. The one condition for the same result on all four engines is that ORDER BY ends with a unique tie-breaker; 30 books share the same listed_on value, and without book_id DESC each engine could pick a different order among the ties.

String Concatenation and Lists

The standard operator for concatenating two strings is ||. SQL Server only supported it with the 2025 release; before that the only operator was + (the CONCAT function has existed since 2012). In MySQL, as you saw above, || is OR by default; it does not concatenate unless the PIPES_AS_CONCAT SQL mode is on. MySQL 8.4 also issues a warning when it does this: '|| as a synonym for OR' is deprecated. The only form that works the same on all four engines is the CONCAT() function, but its NULL behaviour differs: on SQL Server and PostgreSQL CONCAT('a', NULL) returns "a" (it treats NULL as an empty string), on MySQL it returns NULL. ||, as the standard requires, produces NULL when combined with NULL.

Producing a list from a group also means four different syntaxes. Customer cities per country:

SELECT country_code,
       STRING_AGG(city, ', ') WITHIN GROUP (ORDER BY city) AS cities
FROM (SELECT DISTINCT country_code, city FROM dbo.customers WHERE city IS NOT NULL) AS c
GROUP BY country_code
ORDER BY country_code;

SELECT country_code,
       string_agg(DISTINCT city, ', ' ORDER BY city) AS cities
FROM customers
GROUP BY country_code
ORDER BY country_code;

SELECT country_code,
       GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR ', ') AS cities
FROM customers
GROUP BY country_code
ORDER BY country_code;

SELECT country_code,
       string_agg(city, ', ' ORDER BY city) AS cities
FROM (SELECT DISTINCT country_code, city FROM customers WHERE city IS NOT NULL)
GROUP BY country_code
ORDER BY country_code;

country_code cities
------------ ---------------------------------------
DE           Berlin, Munich
GB           London, Manchester
NL           Amsterdam
TR           Ankara, Antalya, Bursa, Istanbul, Izmir

All four returned the same list. The differences are in syntax: SQL Server takes the ordering with WITHIN GROUP and does not support DISTINCT inside STRING_AGG (hence the derived table); PostgreSQL and SQLite take the ordering inside the aggregate; MySQL's GROUP_CONCAT takes the separator with the SEPARATOR keyword. SQLite added string_agg in 3.44 and only accepts DISTINCT in single-argument aggregates:

Parse error near line 2: DISTINCT aggregates must have exactly one argument

Identity and Getting the New Id

The four engines have four different definitions for an auto-incrementing key: INT IDENTITY(1,1) in SQL Server, the standard INT GENERATED BY DEFAULT AS IDENTITY in PostgreSQL (the old SERIAL still works but is not recommended for new code), INT AUTO_INCREMENT in MySQL, and INTEGER PRIMARY KEY in SQLite (it becomes an alias for the row's internal rowid). I wrote these differences in the CREATE TABLE statements of the lab scripts; writing a fully portable CREATE TABLE is not possible in practice.

Getting back the id of the newly inserted row:

INSERT INTO dbo.orders (customer_id, ordered_at, status)
OUTPUT inserted.order_id
VALUES (1, '2026-06-30 12:00:00', 'Pending');

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (1, '2026-06-30 12:00:00', 'Pending')
RETURNING order_id;

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (1, '2026-06-30 12:00:00', 'Pending');
SELECT LAST_INSERT_ID() AS order_id;

order_id
--------
     380

SQL Server uses OUTPUT, PostgreSQL and SQLite use RETURNING; all four returned 380. MySQL has no RETURNING and needs a separate SELECT LAST_INSERT_ID(); that value is per connection, so as long as it is asked on the same connection, concurrent inserts do not affect it. We covered OUTPUT in detail in post 15 of this series.

Upsert: MERGE, ON CONFLICT and ON DUPLICATE KEY

A delivery arrives at the warehouse: Pride and Prejudice (1) has a stock row and gets 5 more copies; Charlotte's Web (70) has no stock row and will be created with 10 copies.

MERGE dbo.stock_items AS s
USING (VALUES (1, 5), (70, 10)) AS d (book_id, quantity)
   ON d.book_id = s.book_id
WHEN MATCHED THEN
    UPDATE SET s.quantity_on_hand = s.quantity_on_hand + d.quantity
WHEN NOT MATCHED THEN
    INSERT (book_id, quantity_on_hand) VALUES (d.book_id, d.quantity)
OUTPUT inserted.book_id, inserted.quantity_on_hand;

INSERT INTO stock_items (book_id, quantity_on_hand)
VALUES (1, 5), (70, 10)
ON CONFLICT (book_id) DO UPDATE
    SET quantity_on_hand = stock_items.quantity_on_hand + EXCLUDED.quantity_on_hand
RETURNING book_id, quantity_on_hand;

INSERT INTO stock_items (book_id, quantity_on_hand)
VALUES (1, 5), (70, 10) AS d
ON DUPLICATE KEY UPDATE
    quantity_on_hand = stock_items.quantity_on_hand + d.quantity_on_hand;
SELECT book_id, quantity_on_hand FROM stock_items WHERE book_id IN (1, 70);

INSERT INTO stock_items (book_id, quantity_on_hand)
VALUES (1, 5), (70, 10)
ON CONFLICT (book_id) DO UPDATE
    SET quantity_on_hand = quantity_on_hand + excluded.quantity_on_hand
RETURNING book_id, quantity_on_hand;

 book_id | quantity_on_hand 
---------+------------------
       1 |               52
      70 |               10
(2 rows)

The result is the same on all four: stock went from 47 to 52, and a new row was created for 70. The mechanisms differ, though. ON CONFLICT in PostgreSQL and SQLite is tied to a unique constraint or primary key conflict and is atomic within a single statement; EXCLUDED represents the row being inserted. MySQL's ON DUPLICATE KEY UPDATE fires on a conflict with any unique key in the table, so on tables with several unique indexes it can update a row you did not expect. Since 8.0.19 the new row is accessed through an alias such as AS d; the old VALUES(column) form is deprecated. SQL Server has the standard MERGE, which comes with the concurrency notes from post 15. PostgreSQL has also supported MERGE since 15.

Date Functions

Order counts grouped by start of month:

SELECT DATETRUNC(month, ordered_at) AS month_start, COUNT(*) AS orders
FROM dbo.orders
WHERE ordered_at >= '2025-10-01' AND ordered_at < '2026-01-01'
GROUP BY DATETRUNC(month, ordered_at)
ORDER BY month_start;

SELECT date_trunc('month', ordered_at) AS month_start, COUNT(*) AS orders
FROM orders
WHERE ordered_at >= '2025-10-01' AND ordered_at < '2026-01-01'
GROUP BY date_trunc('month', ordered_at)
ORDER BY month_start;

SELECT DATE_FORMAT(ordered_at, '%Y-%m-01') AS month_start, COUNT(*) AS orders
FROM orders
WHERE ordered_at >= '2025-10-01' AND ordered_at < '2026-01-01'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m-01')
ORDER BY month_start;

SELECT strftime('%Y-%m-01', ordered_at) AS month_start, COUNT(*) AS orders
FROM orders
WHERE ordered_at >= '2025-10-01' AND ordered_at < '2026-01-01'
GROUP BY strftime('%Y-%m-01', ordered_at)
ORDER BY month_start;

month_start         orders
------------------- ------
2025-10-01 00:00:00     24
2025-11-01 00:00:00     23
2025-12-01 00:00:00     42

The counts are the same on all four (24, 23, 42), but the types are not. SQL Server's DATETRUNC (2022+) and PostgreSQL's date_trunc return a date/time; MySQL's DATE_FORMAT and SQLite's strftime return a string. SQLite does not have a separate date type anyway: dates are stored as TEXT, REAL or INTEGER and the functions interpret them. Writing a date range half-open, like >= '2025-10-01' AND < '2026-01-01' (the rule from post 06 of this series), is one of the rare things that works the same on all four.

Identifier Quoting, Case, Boolean and NULL Ordering

The standard way to quote an alias containing a space is double quotes. SQL Server accepts both [ ] and " ", PostgreSQL only " ", MySQL uses backticks by default:

SELECT TOP (1) title AS [Book Title], list_price AS "List Price" FROM dbo.books ORDER BY book_id;

SELECT title AS "Book Title", "Dune" AS dq FROM books ORDER BY book_id LIMIT 1;

+---------------------+------+
| Book Title          | dq   |
+---------------------+------+
| Pride and Prejudice | Dune |
+---------------------+------+

In MySQL "Dune" was read as a string literal, not a column name: with the ANSI_QUOTES SQL mode off, double quotes mean a string. PostgreSQL has the opposite surprise: it folds unquoted identifiers to lower case and leaves quoted ones as they are. Create the column as title and ask for "Title":

ERROR:  column "Title" does not exist
LINE 1: SELECT "Title" FROM books LIMIT 1;
               ^
HINT:  Perhaps you meant to reference the column "books.title".

That is why it is customary in PostgreSQL to write identifiers in lower case snake_case (the Bookstore schema does). The case difference from the opening, however, is about data, not identifiers: in SQL Server comparison depends on the database collation (SQL_Latin1_General_CP1_CI_AS; the CI (case-insensitive) part means case does not matter), and MySQL 8's default collation utf8mb4_0900_ai_ci behaves the same. PostgreSQL and SQLite are case-sensitive by default; you use ILIKE or lower() in PostgreSQL and, for ASCII (American Standard Code for Information Interchange) characters, LIKE in SQLite. We looked at the Turkish İ and its relationship with collation in post 03 of this series.

The boolean type is also four separate stories. PostgreSQL has a real boolean and you can write WHERE is_active. MySQL's BOOLEAN is really TINYINT(1), and TRUE is an alias for 1. SQLite accepts TRUE as 1 (since 3.23). SQL Server has bit, but even SQL Server 2025 has no TRUE literal:

SELECT COUNT(*) AS active_books FROM dbo.books WHERE is_active = TRUE;

Msg 207, Level 16, State 1, Server b8be943222fa, Line 1
Invalid column name 'TRUE'.

The position of NULLs in ordering differs too. With ascending ORDER BY city, SQL Server, MySQL and SQLite put NULL first, PostgreSQL last:

   city   
----------
 Berlin
 Berlin
 Istanbul
(3 rows)

The standard defines NULLS FIRST / NULLS LAST for this. PostgreSQL and SQLite (3.30+) support it; SQL Server and MySQL do not:

Msg 102, Level 15, State 1, Server b8be943222fa, Line 1
Incorrect syntax near 'NULLS'.

The portable alternative is to add a CASE to the ordering: ORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city.

APPLY, LATERAL and FULL OUTER JOIN

In post 09 of this series we got each customer's last order with SQL Server's CROSS APPLY. The standard equivalent is LATERAL:

SELECT c.customer_id, last_order.order_id, last_order.ordered_at
FROM dbo.customers AS c
CROSS APPLY (
    SELECT TOP (1) o.order_id, o.ordered_at
    FROM dbo.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.ordered_at DESC
) AS last_order
WHERE c.customer_id <= 3
ORDER BY c.customer_id;

SELECT c.customer_id, last_order.order_id, last_order.ordered_at
FROM customers AS c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.ordered_at
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.ordered_at DESC
    LIMIT 1
) AS last_order
WHERE c.customer_id <= 3
ORDER BY c.customer_id;

customer_id order_id ordered_at
----------- -------- -------------------
          1      380 2026-06-30 12:00:00
          2      366 2026-06-06 09:39:30
          3      377 2026-06-20 16:58:05

The CROSS JOIN LATERAL query from PostgreSQL also ran unchanged on MySQL 8.4 (MySQL added LATERAL in 8.0.14) and returned the same three rows. SQLite does not recognise LATERAL:

Parse error near line 2: near "SELECT": syntax error
  r_id FROM customers AS c CROSS JOIN LATERAL (     SELECT o.order_id FROM order
                                      error here ---^

In SQLite you write the same result with a correlated subquery or a window function. There is a gap in the other direction in MySQL: no FULL OUTER JOIN.

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id' at line 3

In MySQL a FULL OUTER JOIN is written as the UNION of a LEFT JOIN and a RIGHT JOIN. SQLite added FULL OUTER JOIN in 3.39.

Production Notes

A summary table, based on the tests in this post:

One SQLite trait does not fit in this table, and it causes the most surprises during a migration: type flexibility (type affinity). A column type is a suggestion, not a rule:

INSERT INTO books (isbn, title, list_price, listed_on, is_active)
VALUES ('9798900000999', 'Test', 'cheap', 'tomorrow', 'yes');
SELECT book_id, list_price, typeof(list_price) AS price_type, listed_on FROM books WHERE isbn = '9798900000999';

+---------+------------+------------+-----------+
| book_id | list_price | price_type | listed_on |
+---------+------------+------------+-----------+
|      78 | cheap      | text       | tomorrow  |
+---------+------------+------------+-----------+

"cheap" was written into a price column defined as NUMERIC, "tomorrow" into a date column, and no error. STRICT tables, which came with SQLite 3.37, prevent this:

CREATE TABLE price_check (price REAL NOT NULL) STRICT;
INSERT INTO price_check (price) VALUES ('cheap');

Error near line 3: cannot store TEXT value in REAL column price_check.price

In the Entity Framework Core (EF Core) series we talked about the traps of using SQLite in tests; this type flexibility is one reason data that SQL Server would reject in production passes silently in tests.

My checklist for portable SQL:

• In paging, always end ORDER BY with a unique key; keep the LIMIT/FETCH syntax in one place in the data access layer.
• For string concatenation, use CONCAT or COALESCE knowing their NULL behaviour, instead of || or +.
• Widen the type explicitly in integer division (* 1.0 or CAST); remember that MySQL behaves differently.
• Do not leave case sensitivity to the collation; if you need case-insensitive search, use LOWER() or a suitable collation explicitly.
• Write and test upsert and new-id code per dialect; there is no common syntax.
• Actually run the target engine and version in your CI (Continuous Integration) environment; the "ANSI (American National Standards Institute) compliant" label is not enough.

If you use an ORM (Object-Relational Mapping), the provider hides most of these differences; EF Core's SQL Server, PostgreSQL (Npgsql), MySQL and SQLite providers generate different SQL from the same LINQ (Language Integrated Query) query. But the moment you write raw SQL (the Raw SQL post of the EF Core series, the whole Dapper series), the dialect becomes your responsibility. PostgreSQL-specific topics will be covered in the upcoming PostgreSQL series.

Common Pitfalls

Trusting the "ANSI compliant" label and moving a query without running it on the target engine. None of the differences in this post is caught at compile time, and some never raise an error at all.

Concatenating strings with || in MySQL. No error, it returns 0.

Creating capitalised, quoted identifiers in PostgreSQL ("BookId"). You then have to quote them in every query.

Case-insensitive searches silently returning nothing when moving from SQL Server to PostgreSQL.

Assuming NULL ordering is the same on every engine.

Using SQLite as a test engine in place of the production engine and missing type and collation differences.

A Small Experiment

★☆☆ Write the "each customer's last order" query from this post for SQLite with a window function (ROW_NUMBER) and verify you get the same result on all four engines.

★★☆ In MySQL, without FULL OUTER JOIN, write the list of "customers who never ordered and orders whose customer was deleted" with LEFT JOIN + RIGHT JOIN + UNION. Do you need UNION or UNION ALL, and why?

★★★ Run the gaps and islands query from post 14 of this series on all four engines. Which date functions did you have to change? Note how PostgreSQL's date - integer arithmetic differs from SQL Server's DATEADD.

Some dialect differences close over time: SQL Server 2022 and 2025 added a lot of syntax that moves closer to the standard. In the next post we examine what SQL Server 2022 brought to T-SQL, from IS DISTINCT FROM to GENERATE_SERIES, with before/after code and compatibility level requirements.