PostgreSQL vs MySQL vs SQL Server vs SQLite: SQL Syntax Differences

Standard SQL exists on paper. In practice every database speaks its own dialect, and most of the differences hide in exactly the places a tutorial skips: how you limit rows, how you glue strings together, how you ask for “today”. This page puts the four databases you are most likely to meet side by side, so a query that works in one can be translated to another in seconds. Nothing here is theoretical — each row is a mistake someone makes in their first week on a new system.

Why dialects differ at all

The SQL standard defines the core — SELECT, JOIN, GROUP BY, subqueries — and every major database implements that core faithfully. What the standard left unspecified for years (row limiting, string functions, date arithmetic, identity columns) each vendor filled in their own way, and those choices are now decades old and impossible to change without breaking customers. So the rule of thumb: the logic of a query travels; the trimmings do not.

Limiting rows: LIMIT, TOP and FETCH FIRST

The single most common portability error. Three of the four use LIMIT; SQL Server does not.

SELECT name, unit_price
FROM Products
ORDER BY unit_price DESC
LIMIT 5;

SQL Server’s OFFSET ... FETCH form is the standard one and also works in PostgreSQL. It requires an ORDER BY, which is a good habit anyway: a limit without an order is a random sample.

-- SQL Server (and standard SQL)
SELECT name, unit_price
FROM Products
ORDER BY unit_price DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

Pagination follows the same split: LIMIT 10 OFFSET 20 in PostgreSQL, MySQL and SQLite; OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY in SQL Server.

Quoting: identifiers versus strings

Every database uses single quotes for string values. They disagree about how to quote a table or column name that clashes with a keyword or contains a space.

String concatenation

Joining first_name and last_name into one column is where the operators diverge most visibly.

SELECT first_name || ' ' || last_name AS full_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;

CONCAT() is the most portable choice today — PostgreSQL 9.1+, MySQL, SQL Server 2012+ and SQLite 3.44+ all have it — and it has a second advantage: it treats NULL as an empty string, whereas || and + return NULL if any part is NULL.

Case sensitivity when comparing text

Does WHERE country = 'peru' match 'Peru'? It depends on the database, and the answer decides whether your filter silently drops rows.

The portable habit: compare LOWER(column) = LOWER(value) whenever user-typed input is involved. It works everywhere and makes the intent visible in the query.

Dates and times

Date handling is the biggest block of differences, because each vendor built its own function library. The good news is that the questions you ask are always the same four.

The playground on this site accepts YEAR(), MONTH() and SQL Server-style DATEDIFF(day, a, b) — see SQL Date Functions for runnable examples of every date pattern.

Booleans

Write WHERE active = 1 and it runs on all four. Write WHERE active and only PostgreSQL (and SQLite, loosely) accept it. Our sample database uses 1 and 0 for exactly this reason.

Auto-increment primary keys

Getting the id you just inserted differs too: RETURNING id (PostgreSQL, SQLite 3.35+), LAST_INSERT_ID() (MySQL), SCOPE_IDENTITY() (SQL Server).

Upsert: insert or update in one statement

“Insert this row, or update it if the key already exists” is one of the least portable statements in SQL.

-- PostgreSQL and SQLite 3.24+
INSERT INTO Products (product_id, name, category, unit_price, in_stock)
VALUES (201, 'Aurora Laptop 14"', 'Hardware', 1249, 40)
ON CONFLICT (product_id) DO UPDATE
  SET unit_price = EXCLUDED.unit_price, in_stock = EXCLUDED.in_stock;

-- MySQL
INSERT INTO Products (product_id, name, category, unit_price, in_stock)
VALUES (201, 'Aurora Laptop 14"', 'Hardware', 1249, 40)
ON DUPLICATE KEY UPDATE
  unit_price = VALUES(unit_price), in_stock = VALUES(in_stock);

-- SQL Server
MERGE Products AS target
USING (SELECT 201 AS product_id, 1249 AS unit_price, 40 AS in_stock) AS src
  ON target.product_id = src.product_id
WHEN MATCHED THEN UPDATE SET unit_price = src.unit_price, in_stock = src.in_stock
WHEN NOT MATCHED THEN INSERT (product_id, name, category, unit_price, in_stock)
  VALUES (201, 'Aurora Laptop 14"', 'Hardware', 1249, 40);

Window functions

ROW_NUMBER, RANK and friends are standard SQL, but support arrived at very different times, and older servers are still common in companies.

If you are on MySQL 5.7 and need a rank, the classic workaround is a correlated subquery counting rows with a higher value. The window functions guide covers the modern syntax and marks which examples run in this site’s engine.

NULL-safe comparison

NULL = NULL is not true in any database — it is unknown. When you genuinely want “both NULL counts as equal”, each vendor has its own spelling:

Which one should you learn first?

It matters less than beginners fear. Ninety percent of what you learn transfers unchanged; this page is the other ten percent. Practical advice:

• Learning on your own: PostgreSQL. It follows the standard most closely, so habits formed there port cleanly, and it is free everywhere.
• Joining a team: whatever the team runs. Ask on day one, then read the matching column of this page.
• Data analyst roles: expect PostgreSQL, MySQL or a warehouse (BigQuery, Snowflake, Redshift) — all close to PostgreSQL in feel.
• Enterprise / .NET shops: SQL Server. Learn TOP, GETDATE and bracket quoting early.
• Mobile or embedded: SQLite, which is deliberately small but surprisingly standard.

Everything on this site is written to stay dialect-neutral where possible, and to say so plainly where it is not. Start with the beginner’s guide and the syntax you learn will work almost everywhere.

Frequently asked questions

Related reading

• SQL ORDER BY and LIMIT: Sorting, Top-N and Pagination
• SQL Commands: DDL, DML, DCL and TCL Explained
• SQL Date Functions: YEAR, MONTH, DATEDIFF & Ranges
• SQL String Functions: UPPER, SUBSTRING, TRIM & More
• SQL Window Functions: ROW_NUMBER, RANK & PARTITION BY
• SQL Cheat Sheet: Every Clause and Function on One Page