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, give or take a few behaviours such as integer division and where NULLs sort. 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. In SQL Server 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. The playground, being SQLite, understands only the LIMIT form.

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 spelling today — PostgreSQL 9.1+, MySQL, SQL Server 2012+ and SQLite 3.44+ all have it — but what it does with a NULL is not portable. PostgreSQL, SQL Server and SQLite skip NULL arguments; MySQL’s CONCAT() returns NULL if any argument is NULL, and only its CONCAT_WS() skips them. The operators are stricter still: || in PostgreSQL and SQLite, and + in SQL Server by default, return NULL if any part is NULL, while Oracle’s || treats NULL as empty text. In the playground, 'a' || NULL is NULL and CONCAT('a', NULL) is 'a'. When a part can be missing, wrap it in COALESCE(part, '') and every database agrees; the string functions guide runs all three side by side.

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 SQLite row is easy to see in the playground: typed in lower case, = misses both Peruvian customers, while LIKE and LOWER() find them.

SELECT
  (SELECT COUNT(*) FROM Customers WHERE country = 'peru') AS equals_peru,
  (SELECT COUNT(*) FROM Customers WHERE country LIKE 'peru') AS like_peru,
  (SELECT COUNT(*) FROM Customers WHERE LOWER(country) = 'peru') AS lower_peru;

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.

In the playground the SQLite row of each table is the one that runs: julianday() for day counts, date(d, '+30 days') for shifting, strftime() for month starts. SQLite has no YEAR(), MONTH() or DAY(), so the playground adds those three as helpers; DATEDIFF, DATEADD and NOW() are not available. SQL Date Functions has runnable examples of each pattern.

Booleans

With a 1/0 integer column like the sample database’s active, WHERE active = 1 runs on all four. A bare WHERE active works on that column in MySQL and SQLite; PostgreSQL accepts a bare column only when it is a real BOOLEAN (and then rejects = 1), and SQL Server never does. Our sample database uses 1 and 0 because that is the form every database can read.

Integer division

Divide one whole number by another and three of the four keep the answer whole: 7 / 2 is 3, not 3.5. Nothing errors, which makes it the quietest bug on this page: a share worked out from two counts loses its fraction and can come back as 0.

SELECT SUM(active) / COUNT(*) AS active_share_wrong,
       ROUND(SUM(active) * 1.0 / COUNT(*), 3) AS active_share
FROM Employees;

Eleven of the twelve employees are active, yet the first column says 0: both sides are integers, so SQLite divides 11 by 12 and drops the fraction. Multiplying by 1.0 first turns one side into a decimal, and 0.917 comes back. The money columns in the sample database are stored as REAL, so salary / 12 already keeps its fraction; the trap is counts and 1/0 flags.

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);

The first form runs in the playground as written: product 201 already exists, so SQLite updates its price and stock instead of inserting a second row.

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. SQLite, and so the playground, supports window functions including frames, LAG and LEAD.

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 row of each table on 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. It is also what this site’s playground runs.

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: Core Syntax, Joins and Functions on One Page