SQL, PostgreSQL, MySQL and SQL Server: what are the differences?

One common language, multiple implementations

Saying "I use SQL" is like saying "I use a language": it describes a language, not the software that stores the document. PostgreSQL, MySQL, and Microsoft SQL Server are DBMSs that include SQL and add their own capabilities.

A base is the structured content managed by the DBMS. The server is the service to which applications and tools often connect. On SQL.tn, PGlite runs a compiled PostgreSQL for the browser: no remote server receives the dummy exercise data.

The SQL base travels quite well

SELECT, WHERE, GROUP BY, HAVING, JOIN, INSERT, UPDATE, DELETE, keys and constraints have common ideas. A simple query can work on several engines or only require a small adaptation.

Portability decreases with dates, text, types, JSON functions, identifier generation, UPSERT, procedures, and administration. The motor must be specified as soon as an example goes beyond the base.

SELECT ville, COUNT(*) AS clients
FROM clients
WHERE actif = TRUE
GROUP BY ville
ORDER BY clients DESC;

PostgreSQL: extensibility and rigor

PostgreSQL is an open source relational DBMS known for its conformance, rich types, window functions, JSONB, text search, extensions, and a powerful procedural language. PGlite takes much of this engine from WebAssembly.

Constructs like INSERT ... ON CONFLICT, RETURNING, JSONB or PL/pgSQL functions are characteristic of PostgreSQL, even if other engines offer equivalent solutions with other syntax.

INSERT INTO stocks (id_produit, quantite)
VALUES (42, 8)
ON CONFLICT (id_produit)
DO UPDATE SET quantite = EXCLUDED.quantite
RETURNING id_produit, quantite;

MySQL and SQL Server meet the same families of needs

MySQL is very common in web applications and has its own ecosystem, storage engines and syntax. SQL Server is Microsoft's DBMS, heavily integrated with its tools and uses the T-SQL dialect.

Limiting rows illustrates the difference: PostgreSQL and MySQL commonly use LIMIT, while SQL Server uses TOP or OFFSET/FETCH. The business objective remains the same, the form changes.

-- PostgreSQL and MySQL
SELECT produit, prix FROM produits
ORDER BY prix DESC
LIMIT 3;

-- SQL Server
SELECT TOP (3) produit, prix FROM produits
ORDER BY prix DESC;

Which engine should you learn?

To start, learn the concepts and a coherent dialect. PostgreSQL is a solid choice and SQL.tn uses it for their simulator. If your company uses SQL Server or MySQL, then practice its differences on the relevant environment.

The best engine also depends on the existing engine, team skills, hosting, connectors, operating requirements and total cost. A tutorial alone is not enough for this architectural decision.

Common errors

Always specify the dialect of an advanced example and distinguish what belongs to the language from what belongs to DBMS or its administration.

• Present PostgreSQL as a language.
• Saying that a complex query is universal without testing it elsewhere.
• Copy a function SQL Server into PostgreSQL because both use SQL.
• Choosing an engine solely from a list of features without considering operation.

Reading exercise

Find a query you're using. Classify each element: common SQL, engine-specific syntax, or administrative operation. For example, SELECT is common, ON CONFLICT is PostgreSQL, and configuring replication is an administrative matter.

The dedicated micro-training will detail this method. It remains in preparation: no purchase is proposed before its validated exercises.