Differences between SQL, SQL Server, PostgreSQL, MySQL, MariaDB and SQLite

It’s one of the most common questions when starting out: should I learn SQL, MySQL or PostgreSQL? The short answer is that they aren’t alternatives. SQL is a language; SQL Server, PostgreSQL, MySQL and SQLite are databases (database management systems, DBMS) that understand that language.

It’s a bit like English: it’s spoken in London, New York and Sydney, and people from each place understand each other perfectly, even though each has its own accent and a few words of its own. In SQL, each “accent” is called a dialect.

One standard, many dialects

SQL was born at IBM in the 1970s (under the name SEQUEL) and was standardized in 1986 (ANSI) and 1987 (ISO). The standard defines the core of the language: SELECT, INSERT, UPDATE, DELETE, JOIN, GROUP BY, window functions…

Each database implements that core and adds its own features: its own functions, data types, a language for writing procedures and, sometimes, different ways of writing the same thing. That’s why a simple query works the same everywhere, while a more advanced one may need small changes.

SQL Server

Lesson syntax

Microsoft's database. Widely used by companies built on the Microsoft ecosystem, from Excel and Power BI to Azure.

Its SQL: Transact-SQL (T-SQL)
Where you will find it: Banking, insurance, public sector and .NET applications
In this course: The course's reference: lesson syntax is T-SQL.

PostgreSQL

Editor engine

Open source, very close to the standard and with powerful extensions such as PostGIS. A favourite of many startups and data teams.

Its SQL: PostgreSQL SQL, with PL/pgSQL for procedures
Where you will find it: Web applications, data analysis and cloud services
In this course: It's what the page editors run, right in your browser.

MySQL

Open source and owned by Oracle. For years it was the database of the web: it powers WordPress and much of shared hosting.

Its SQL: MySQL SQL
Where you will find it: Websites, e-commerce and WordPress
In this course: The lesson queries work almost the same; details change, such as LIMIT instead of TOP.

MariaDB

A fork of MySQL created in 2009 by its original author. It's compatible with MySQL in almost everything and is the default in several Linux distributions.

Its SQL: MariaDB SQL, almost identical to MySQL
Where you will find it: Linux servers and websites that used to run MySQL
In this course: What you learn for MySQL also applies to MariaDB.

SQLite

A database in a single file, with no server. It's built into phones, browsers and thousands of applications.

Its SQL: SQLite SQL, with no stored procedures
Where you will find it: Mobile apps, browsers, prototypes and local analysis
In this course: Great for practising without installing anything: almost every SELECT in the course works as is.

Oracle Database

Oracle's commercial database, designed for very large and critical systems.

Its SQL: Oracle SQL, with PL/SQL for procedures
Where you will find it: Large enterprises, telecoms and banking
In this course: The course's SELECT, JOIN and window functions are standard SQL and apply the same way.

The most common syntax differences

These are the ones you notice most when moving from one database to another:

To…	SQL Server	PostgreSQL	MySQL / MariaDB	SQLite	Oracle
Limit rows	`TOP 10`	`LIMIT 10`	`LIMIT 10`	`LIMIT 10`	`FETCH FIRST 10 ROWS ONLY`
Auto-increment column	`IDENTITY(1,1)`	`GENERATED ... AS IDENTITY`	`AUTO_INCREMENT`	`INTEGER PRIMARY KEY`	`GENERATED ... AS IDENTITY`
Concatenate text	`+` or `CONCAT()`	`\|\|` or `CONCAT()`	`CONCAT()`	`\|\|`	`\|\|` or `CONCAT()`
Current date and time	`GETDATE()`	`NOW()`	`NOW()`	`datetime('now')`	`SYSDATE`
Replace a `NULL`	`ISNULL()`	`COALESCE()`	`IFNULL()`	`IFNULL()`	`NVL()`
Names with spaces or reserved words	`[column]`	`"column"`	`column`	`"column"`	`"column"`
Procedural language	T-SQL	PL/pgSQL	SQL procedures	None	PL/SQL

Notice that several rows have an option shared by all of them: COALESCE() works in all six, and so does CURRENT_TIMESTAMP for the current date and time. When there is a standard way, it’s best to use it: the code moves from one database to another without changes. We cover it in the COALESCE lesson.

In MySQL and MariaDB, || doesn’t concatenate: by default it’s the logical OR operator. SELECT 'a' || 'b' returns 0. Always use CONCAT().

The same query in two dialects

To get the three most expensive orders, SQL Server uses TOP:

SELECT TOP 3 product, totalPrice
FROM Orders
ORDER BY totalPrice DESC;

PostgreSQL, MySQL, MariaDB and SQLite use LIMIT. This one you can run here, because the page editors use PostgreSQL:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards

product	totalPrice
Xiaomi Mi 11	4304.25
Echo DOT 4	2999.50
MacBook Pro M1	2449.50

And there is a third way, the standard one, which works in SQL Server, PostgreSQL, Oracle and MariaDB:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards

product	totalPrice
Xiaomi Mi 11	4304.25
Echo DOT 4	2999.50
MacBook Pro M1	2449.50

Which SQL do you learn in this course?

The lessons use SQL Server as their reference, so the syntax you’ll see is T-SQL. There are two reasons: it’s one of the most widely used databases in business, and its documentation and tools (such as SQL Server Management Studio) are very thorough.

But most of what’s taught is standard SQL and works in any database: SELECT, JOIN, GROUP BY, subqueries, CTEs and window functions. What’s specific to SQL Server (variables, cursors, stored procedures, TOP…) is pointed out in each lesson.

The runnable editors use PostgreSQL, which runs inside the browser with nothing to install. That’s why the examples you can run are written to work the same in SQL Server and PostgreSQL, and when something only exists in one of them, the lesson says so.

Which one should you learn first?

It matters less than it seems: once you know SQL well in one of them, switching to another takes days, not months. As a guide:

If you’re going to work at a specific company, learn the one it uses.
If you’re interested in data analysis, PostgreSQL and SQL Server are among the most common, along with cloud data warehouses (BigQuery, Snowflake), whose dialects are very close to the standard.
If you build websites, MySQL or MariaDB.
If you just want to practise, SQLite: it’s a single file and needs no server.

Either way, start with the common core. That’s what this course covers, starting from the first lesson.