Skip to content
SQL

PostgreSQL vs MySQL vs SQLite vs SQL Server: Which SQL to Learn First

Ihsan Ullah · Updated · 12 min read
PostgreSQL vs MySQL vs SQLite cover for Lesson 2 of the free SQL course: which SQL to learn first

SQL course · Module 1: Databases and setup · Lesson 2 of 40

Quick answer: PostgreSQL, MySQL, SQLite and SQL Server are all relational databases that speak SQL, and the core of SELECT, WHERE, JOIN and GROUP BY is almost the same in all four. They differ in licence, in how they run (SQLite is a single file inside your program, the others are servers) and in a few pieces of syntax, such as LIMIT versus TOP. A good first choice is PostgreSQL: it is free for any use, it follows the SQL standard closely, and what you learn moves easily to the other three. This course teaches PostgreSQL.

In this lesson you will learn:

  • what PostgreSQL, MySQL, SQLite and SQL Server have in common
  • how their licences and ways of running differ
  • the dialect differences a beginner meets first, with real outputs
  • why this course uses PostgreSQL

Before you start: Lesson 1, What is a relational database?

What the four databases have in common

Everything from Lesson 1 applies to all four. They store data in tables with rows and columns, they use primary and foreign keys, and you query them with SQL. A query like this one runs without changes in three of the four:

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

That LIMIT works in PostgreSQL, MySQL and SQLite, but not in SQL Server. Small differences like this are dialect differences: SQL is a standard, but each product adds its own extras. The PostgreSQL project says it “tries to conform with the SQL standard” and that, as of version 18, it conforms to at least 170 of the 177 mandatory features of SQL:2023 Core. It also says that no relational database fully meets this standard (About PostgreSQL).

The good news: the dialect differences are small next to what is shared. If you learn one database well, most of your SQL works in the others.

The four databases at a glance

Here is what the official sites say about each one.

PostgreSQLMySQLSQLiteSQL Server
Made byopen source communityOracleSQLite projectMicrosoft
LicencePostgreSQL License (similar to BSD or MIT)GPL, or a paid commercial licencepublic domaincommercial; free Express and Developer editions
How it runsserverserverlibrary inside your program, one fileserver
Official description of use“safely store and scale the most complicated data workloads”“highly suited for accessing databases on the Internet”devices, app file formats, low to medium traffic websites, data analysisExpress: “learning and building desktop and small server data-driven applications”

Licences: who can use them for free

  • PostgreSQL uses the PostgreSQL License, which the project calls “a liberal Open Source license, similar to the BSD or MIT licenses”. You may use, copy, change and share it “for any purpose, without fee” (PostgreSQL licence).
  • MySQL is open source under the GPL. The MySQL manual says that if you “need to embed MySQL code into a commercial application, you can buy a commercially licensed version” from Oracle (MySQL 9.7 manual: What is MySQL?).
  • SQLite is in the public domain. Anyone is free to “copy, modify, publish, use, compile, sell, or distribute” it “for any purpose” (SQLite copyright).
  • SQL Server is a commercial product. Microsoft offers a free Express edition, “ideal for development and production for desktop, web, and small server applications”, and a free Developer edition “for non-production development and testing” (SQL Server downloads). Express has limits, for example a maximum database size of 50 GB in SQL Server 2025 (SQL Server 2025 editions).

For learning, all four cost nothing.

How they run: a server or a single file

PostgreSQL, MySQL and SQL Server are client/server systems. A server program runs in the background, and your tools (psql, an app, a script) connect to it.

SQLite is different. Its page Appropriate uses for SQLite says: “SQLite does not compete with client/server databases. SQLite competes with fopen().” The whole database “is contained in a single disk file” that the library reads and writes directly (About SQLite). The trade-off: it allows many readers but “only one writer at any instant in time” (same “Appropriate uses” page).

SQL Server is no longer Windows-only. Microsoft says it “runs on Linux, starting with SQL Server 2017” and also in containers (SQL Server on Linux).

Dialect differences a beginner meets first

We ran the examples below on PostgreSQL 16.15 with the shop database from Lesson 1, and on SQLite 3.53.1 through Python 3.14’s built-in sqlite3 module, loaded with the same customers and products. MySQL and SQL Server were not installed here, so their syntax in the table at the end comes from their official manuals.

Limiting rows: LIMIT, FETCH FIRST and TOP

PostgreSQL accepts LIMIT:

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
            name             | price  
-----------------------------+--------
 Ergonomic Chair             | 259.00
 Noise-Cancelling Headphones | 149.00
 Mechanical Keyboard         |  89.00
(3 rows)

It also accepts the SQL standard form, FETCH FIRST. The PostgreSQL docs say LIMIT is “PostgreSQL-specific syntax, also used by MySQL”, while FETCH FIRST comes from the SQL:2008 standard (PostgreSQL docs: SELECT).

SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
            name             | price  
-----------------------------+--------
 Ergonomic Chair             | 259.00
 Noise-Cancelling Headphones | 149.00
 Mechanical Keyboard         |  89.00
(3 rows)

SQL Server uses TOP instead, written as SELECT TOP (10) ..., and OFFSET ... FETCH for paging (SQL Server docs: TOP). PostgreSQL does not understand TOP:

SELECT TOP 3 name, price FROM products ORDER BY price DESC;
ERROR:  syntax error at or near "3"
LINE 1: SELECT TOP 3 name, price FROM products ORDER BY price DESC;
                   ^

In SQLite, LIMIT worked, but both FETCH FIRST and TOP failed:

sqlite> SELECT name, price FROM products ORDER BY price DESC LIMIT 3
('Ergonomic Chair', 259.0)
('Noise-Cancelling Headphones', 149.0)
('Mechanical Keyboard', 89.0)

sqlite> SELECT name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY
OperationalError: near "FETCH": syntax error

sqlite> SELECT TOP 3 name, price FROM products ORDER BY price DESC
OperationalError: near "3": syntax error

Python prints each row as a tuple, so the output looks different from psql.

Case-insensitive search: LIKE and ILIKE

Customer 8 typed his name in capitals: NOAH WILSON. In PostgreSQL, LIKE is case-sensitive, so a lower-case search finds nothing:

SELECT name FROM customers WHERE name LIKE '%noah%';
 name 
------
(0 rows)

PostgreSQL has ILIKE for a case-insensitive match. The docs say it “is not in the SQL standard but is a PostgreSQL extension” (PostgreSQL docs: Pattern Matching).

SELECT name FROM customers WHERE name ILIKE '%noah%';
    name     
-------------
 NOAH WILSON
(1 row)

SQLite behaves the other way round. Its LIKE ignores case for ASCII letters (SQLite docs: LIKE), and ILIKE does not exist:

sqlite> SELECT name FROM customers WHERE name LIKE '%noah%'
('NOAH WILSON',)

sqlite> SELECT name FROM customers WHERE name ILIKE '%noah%'
OperationalError: near "ILIKE": syntax error

MySQL’s LIKE is also case-insensitive by default, because of its default collation utf8mb4_0900_ai_ci (MySQL 9.7 manual: Case Sensitivity in String Searches). The same LIKE query can return different rows on different databases.

Quotes: text versus names

In PostgreSQL, text goes in single quotes. Double quotes mean the name of a column or table (PostgreSQL docs: Lexical Structure). Using double quotes for text gives a confusing error:

SELECT name FROM customers WHERE city = "Lahore";
ERROR:  column "Lahore" does not exist
LINE 1: SELECT name FROM customers WHERE city = "Lahore";
                                                ^

PostgreSQL looked for a column called Lahore. With single quotes it works:

SELECT name FROM customers WHERE city = 'Lahore';
     name     
--------------
 Ayesha Malik
 Omar Haddad
(2 rows)

To quote a name, MySQL uses backticks by default (MySQL 9.7 manual: Identifiers). SQL Server uses square brackets, or double quotes when QUOTED_IDENTIFIER is on (SQL Server docs: Database identifiers). SQLite accepts double quotes, and also brackets and backticks for compatibility (SQLite keywords).

Data types: strict or flexible

PostgreSQL checks types strictly. Text in a number column is rejected:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (22, 1, 'three', 24.99);
ERROR:  invalid input syntax for type integer: "three"
LINE 2: VALUES (22, 1, 'three', 24.99);
                       ^

SQLite accepted the same row and stored the word as text:

sqlite> INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (22, 1, 'three', 24.99)

sqlite> SELECT quantity, typeof(quantity) FROM order_items
('three', 'text')

The SQLite docs explain that most databases use “static, rigid typing”, while SQLite is flexible unless you create a STRICT table (SQLite datatypes).

Quick syntax table

TaskPostgreSQLMySQLSQLiteSQL Server
First 3 rowsLIMIT 3 or FETCH FIRST 3 ROWS ONLYLIMIT 3LIMIT 3TOP (3) or OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
Ignore case in LIKEILIKELIKE (default collation)LIKE (ASCII letters)depends on the collation
Quote a name"name"backticks around the name"name"[name] or "name"
Auto-numbered idGENERATED ... AS IDENTITYAUTO_INCREMENTINTEGER PRIMARY KEYIDENTITY(1,1)

For SQL Server, the docs say “LIKE comparisons are affected by collation” (SQL Server docs: LIKE). The id syntax comes from each official manual: PostgreSQL identity columns, MySQL AUTO_INCREMENT, SQLite autoincrement and SQL Server IDENTITY. You will meet these again in Lesson 16.

Why this course uses PostgreSQL

  • Free for any use. Its licence lets you use it for learning, for work and in products, with no fees.
  • Close to the standard. It supports standard syntax such as FETCH FIRST, so habits you build here carry over.
  • Strict. It tells you when you mix up types or quotes, which is exactly when a beginner needs to know.
  • A real server. You learn the same way of working that MySQL and SQL Server use.
  • Easy to install. The project offers packages and installers for Linux, macOS and Windows (PostgreSQL downloads). Lesson 3 walks you through it.

When a lesson topic differs in MySQL, SQLite or SQL Server, you will see a short note.

Where this fits

Whichever database you start with, the SQL you learn carries over to the roles described in our data analyst roadmap, data engineer roadmap and data science roadmap. Later in this course you will practise advanced queries with the SQL window functions practice problems.

Try it yourself

Exercise 1: Rewrite a SQL Server query

A colleague sends you this SQL Server query: SELECT TOP 2 name, price FROM products ORDER BY price;. Rewrite it for PostgreSQL and find the two cheapest products.

Exercise 2: A name typed in lower case

Customer 4 typed his name in lower case. Find every customer whose name contains “Ahmed”, first with LIKE '%Ahmed%', then with ILIKE. What changes?

Exercise 3: Fix the quotes

This query fails in PostgreSQL: SELECT name FROM customers WHERE country = "Pakistan";. Fix it.

Solutions

Solution 1

SELECT name, price FROM products ORDER BY price LIMIT 2;
       name       | price 
------------------+-------
 Gel Pens 10-Pack |  6.75
 Notebook Pack    | 12.50
(2 rows)

TOP 2 becomes LIMIT 2 at the end of the query.

Solution 2

SELECT name FROM customers WHERE name LIKE '%Ahmed%';
SELECT name FROM customers WHERE name ILIKE '%ahmed%';
 name 
------
(0 rows)

    name     
-------------
 bilal ahmed
(1 row)

LIKE is case-sensitive in PostgreSQL, so only ILIKE finds bilal ahmed.

Solution 3

SELECT name FROM customers WHERE country = 'Pakistan' ORDER BY id;
     name      
---------------
 Ayesha Malik
 bilal ahmed
 Hira Siddiqui
 Omar Haddad
(4 rows)

Text needs single quotes. With double quotes PostgreSQL reported ERROR: column "Pakistan" does not exist.

Common mistakes

  • Using TOP in PostgreSQL. It fails with ERROR: syntax error at or near "3". Use LIMIT or FETCH FIRST.
  • Double quotes around text. PostgreSQL reads "Lahore" as a column name: ERROR: column "Lahore" does not exist. Use single quotes for text.
  • Expecting LIKE to ignore case everywhere. It does in SQLite (ASCII) and in MySQL’s default collation, but not in PostgreSQL. Use ILIKE in PostgreSQL.
  • Using ILIKE outside PostgreSQL. SQLite answers near "ILIKE": syntax error. It is a PostgreSQL extension.
  • Trusting SQLite to check types. SQLite stored the word three in an INTEGER column. PostgreSQL rejects it with invalid input syntax for type integer.

Frequently asked questions

Is PostgreSQL harder to learn than MySQL?

For the topics a beginner learns first (SELECT, WHERE, JOIN, GROUP BY), the SQL is nearly the same in both. PostgreSQL is stricter about types and quotes, so it shows more errors at the start, but those errors teach good habits.

Can I learn SQL with SQLite instead?

Yes. SQLite needs no server, and the core SQL is the same. Just remember the differences shown above: flexible types, case-insensitive LIKE for ASCII letters, and no ILIKE. This course uses PostgreSQL, so some queries here will not run in SQLite without small changes.

Should I learn SQL Server if jobs ask for it?

Look at the job ads you are interested in. If they ask for SQL Server, you will mainly need to learn its dialect (TOP, square brackets, IDENTITY) on top of the SQL you learn here. The free Developer edition lets you practise on it.

Summary

  • PostgreSQL, MySQL, SQLite and SQL Server all use tables, keys and SQL. The core SQL is shared.
  • Licences: PostgreSQL License (free, any use), MySQL GPL or commercial, SQLite public domain, SQL Server commercial with free Express and Developer editions.
  • SQLite is a library that stores the whole database in one file; the other three are servers.
  • First dialect differences: LIMIT vs TOP, ILIKE only in PostgreSQL, quote characters for names, and strict vs flexible types.
  • This course teaches PostgreSQL and adds short notes where other databases differ.

Next lesson

Next: Lesson 3, Install PostgreSQL and Load a Sample Database for SQL Practice.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *