Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 125 ยท Getting Started

SQL Dialects and SQLite

See how the SQL standard relates to SQLite, PostgreSQL, MySQL, SQL Server and Oracle, what really differs between them, and how to install a database client.

SQL is a standard language, yet a query written for one database doesn't always run on another. Each database product speaks its own dialect: the standard core plus its own extensions, shortcuts and occasional gaps. The good news is that the core you'll use every day, SELECT, WHERE, joins, grouping and so on, is almost identical everywhere.

This lesson explains where the standard comes from, introduces the five databases you're most likely to meet, shows the differences that actually trip people up, and explains why the examples on this site run on SQLite. It ends with a short guide to installing a real database client on your own computer.

The SQL standard

SQL was born at IBM in the 1970s as SEQUEL, a query language for an experimental relational system. Commercial databases adopted it quickly, and in 1986 it became an official standard: first ANSI (the American standards body), then ISO, the international one. Today the standard is maintained as ISO/IEC 9075 and revised every few years:

Version Notable additions
SQL-86, SQL-89 The first standard: basic queries, tables, simple constraints
SQL-92 Explicit JOIN syntax, CASE, more data types; still the "classic" baseline
SQL:1999 Recursive queries (WITH RECURSIVE), triggers, Boolean type
SQL:2003 Window functions, MERGE, identity columns, XML
SQL:2008, SQL:2011 FETCH FIRST, TRUNCATE, temporal (time-versioned) tables
SQL:2016 JSON functions, row pattern matching
SQL:2023 Property graph queries, a JSON data type

No database implements the whole standard, and every vendor adds features the standard doesn't have. When people say "standard SQL", they usually mean the large, stable part that all serious databases agree on.

The databases you'll meet

Database Kind License Where you'll find it
SQLite Embedded library (no server) Public domain Phones, browsers, desktop apps, small websites, data analysis
PostgreSQL Client/server Open source Web apps, startups, analytics; known for standards support and extensions
MySQL / MariaDB Client/server Open source (MySQL also has commercial editions) Web hosting, WordPress, PHP and Laravel apps; MariaDB is a community fork of MySQL
SQL Server Client/server Commercial, with free Express and Developer editions Companies built on Microsoft tools and .NET
Oracle Database Client/server Commercial, with a free edition Banks, telecoms, large enterprises

A client/server database runs as a separate program, often on another machine. Your application connects to it over the network with a username and password, and many users can work at once. An embedded database like SQLite is a library inside your application: the whole database is one ordinary file, and there's no server to install or manage.

What actually differs

Here are the differences you'll run into most often. Don't memorize the table; come back to it when a query from somewhere else refuses to run.

Task Standard SQL SQLite PostgreSQL MySQL SQL Server
First n rows FETCH FIRST n ROWS ONLY LIMIT n LIMIT n or FETCH FIRST LIMIT n TOP (n) or OFFSET โ€ฆ FETCH
Join text a || b || or concat() || or concat() CONCAT(a, b) a + b or CONCAT()
Quote a name "order" "order" "order" `order` [order] or "order"
Auto-numbered key GENERATED โ€ฆ AS IDENTITY INTEGER PRIMARY KEY IDENTITY or SERIAL AUTO_INCREMENT IDENTITY(1, 1)
True/false type BOOLEAN Integers 0 and 1 BOOLEAN BOOLEAN (alias of TINYINT(1)) BIT
Column types Enforced Flexible unless STRICT Enforced Enforced (strict mode) Enforced
Text comparison Depends on collation Case-sensitive = Case-sensitive Usually case-insensitive Usually case-insensitive

The Database Systems chapter goes deeper into each one: PostgreSQL Essentials, MySQL and MariaDB Essentials and SQL Server and Oracle Essentials.

The same question in four dialects

Here's one question, "the three most expensive products", written for each system:

SQL
-- SQLite, PostgreSQL, MySQL
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

-- Standard SQL (PostgreSQL, Oracle 12c+, SQL Server with OFFSET)
SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

-- SQL Server
SELECT TOP (3) name, price
FROM products
ORDER BY price DESC;

SQLite uses the LIMIT form:

SQL Top three in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Now try the standard form. SQLite doesn't support FETCH FIRST, so this run fails on purpose with Error: near "FETCH": syntax error:

SQL FETCH FIRST is not SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

That error message is typical: when a database meets syntax from another dialect, it usually complains about the first word it doesn't understand. When a query copied from the internet fails with a syntax error near an unfamiliar keyword, a dialect mismatch is the first thing to suspect.

Joining text

The standard operator for joining (concatenating) text is ||. SQLite and PostgreSQL support it, and both also have a concat() function. The two behave differently when a value is missing:

SQL Two ways to join text

Press Run (or Ctrl + Enter) to see the result here.

|| with a NULL gives NULL, while concat() simply skips it. In MySQL, || means logical OR by default, which is a classic portability trap: 'a' || 'b' returns 0 there instead of ab. Use CONCAT() in MySQL. (SQLite added concat() in version 3.44, so very old SQLite versions only have ||.)

Why this site uses SQLite

Every example on this site runs on SQLite 3.49, compiled to WebAssembly and running inside your browser. There's no server and nothing to install. That choice has real advantages for learning:

  • It's real SQL. SQLite supports joins, subqueries, common table expressions, window functions, upserts, JSON functions, triggers and views.
  • It's everywhere. SQLite is the most widely deployed database in the world. It's built into Android, iOS, every major web browser and countless desktop programs, so it's worth knowing for its own sake.
  • It's safe to experiment. Each run starts from a fresh copy of the sample database. Delete every row; the next run brings them back.

You can ask the database which version it is:

SQL Which SQLite is this?

Press Run (or Ctrl + Enter) to see the result here.

What SQLite does differently

SQLite is honest about its trade-offs, and you should know them:

  • No users or permissions. Access is controlled by who can open the file, so GRANT and REVOKE don't exist. The lesson on users and permissions shows them in other databases.
  • No stored procedures. Logic lives in your application or in triggers. See Stored Procedures and Functions.
  • One writer at a time. Many connections can read at once, but writes take turns. That's fine for most apps and a limit for very busy ones.
  • Flexible typing. A column's type is a preference, not a rule, unless you create the table as STRICT.

The last point surprises everyone who comes from another database. Watch what an INTEGER column accepts:

SQL Flexible typing

Press Run (or Ctrl + Enter) to see the result here.

SQLite converts the text '42' into the integer 42, because it can do so without losing anything. But it stores 'forty-two' as text and 4.5 as a real number. PostgreSQL would reject both. Add the STRICT keyword and SQLite behaves like the others. This run fails on purpose with Error: cannot store TEXT value in INTEGER column readings.value:

SQL A STRICT table

Press Run (or Ctrl + Enter) to see the result here.

You'll learn the full rules, called type affinity, in Types and CAST and Data Types.

Writing portable SQL

Most queries you write will run unchanged on any database if you follow a few habits:

  • Use standard syntax when there's a choice: <> for "not equal", CASE instead of vendor functions like IF(), COALESCE() instead of IFNULL() or ISNULL().
  • Put text values in single quotes and only use double quotes (if at all) for names.
  • Don't rely on quirks such as SQLite's flexible typing or MySQL's case-insensitive comparisons.
  • Accept that a few things are never portable: row limits, auto-numbering, date functions and upserts. Keep them in one place in your code.

Throughout the course, standard SQL comes first. Where dialects differ, you'll see the SQLite version you can run, plus read-only code marked with a comment like -- PostgreSQL.

Using a real database on your computer

Running SQL in the browser is perfect for learning, but sooner or later you'll want a database of your own. You don't need any of this to follow the course; read it now or come back later.

SQLite: the command-line shell

The sqlite3 program is a small shell for SQLite files. macOS and most Linux systems already have it; on Windows, download the "command-line tools" bundle from the official SQLite website or install it with a package manager. A session looks like this:

TEXT
$ sqlite3 shop.db
sqlite> CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
sqlite> INSERT INTO notes (body) VALUES ('Hello, SQLite');
sqlite> SELECT * FROM notes;
1|Hello, SQLite
sqlite> .tables
notes
sqlite> .mode table
sqlite> .quit

Commands that start with a dot, like .tables and .quit, belong to the shell, not to SQL, so they don't end with a semicolon. The file shop.db now holds your database; copy it and you've copied the whole thing.

PostgreSQL, MySQL and SQL Server

Server databases need a running server and a client to talk to it. The quickest way to try one without changing your system is Docker:

Terminal
# PostgreSQL: then connect with: psql -h localhost -U postgres
docker run --name pg -e POSTGRES_PASSWORD=devpassword -p 5432:5432 -d postgres

# MySQL: then connect with: mysql -h 127.0.0.1 -u root -p
docker run --name mysql -e MYSQL_ROOT_PASSWORD=devpassword -p 3306:3306 -d mysql

Each one also has an official installer for Windows, macOS and Linux. Every server database ships a command-line client (psql, mysql, sqlcmd), and you'll meet their basics in the Database Systems chapter.

Graphical clients

Many people prefer a graphical tool that shows tables, lets you browse rows and keeps a history of queries:

Tool Works with
DB Browser for SQLite SQLite
DBeaver (Community edition) Almost everything: SQLite, PostgreSQL, MySQL, SQL Server, Oracle
pgAdmin PostgreSQL
MySQL Workbench MySQL
SQL Server Management Studio SQL Server (Windows)
Code editor extensions (for example in VS Code) Most databases

Practice on a copy. Never learn on a production database. A single UPDATE without a WHERE clause can change every row in a table, and a real database has no "fresh copy on every run" like the examples here.

Hosting and managed databases

A database on your laptop is for learning and development. When an application goes live, its database has to run somewhere that's always on, backed up and secure. There are three broad ways to host one.

Self-managed on your own server. You install PostgreSQL or MySQL on a virtual machine or physical server and run it yourself: installation, upgrades, security patches, backups, monitoring, replication and failover are all your job. You get full control and the lowest raw cost, but it takes real expertise and time.

A managed database service (often called DBaaS, "database as a service"). A cloud provider runs the server for you, and you get a connection string. Examples include Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL/MySQL and Azure SQL Database, and specialists such as Neon, Supabase, PlanetScale, Aiven and DigitalOcean Managed Databases. The provider handles:

  • installing, patching and upgrading the database software (often in a maintenance window you choose)
  • automated backups and point-in-time restore
  • replicas and automatic failover to a standby server
  • monitoring dashboards, metrics and alerts
  • scaling the server up (more CPU and memory) with a few clicks

Serverless and edge databases. Some services separate storage from compute and scale to zero when idle, so you pay per query or per second of use (Neon, Aurora Serverless, Azure SQL serverless). Others run SQLite-compatible databases close to users (Cloudflare D1, Turso). They're attractive for side projects and spiky traffic, but check their limits on connections, cold starts and SQL features.

Self-managed Managed service
Who patches, backs up and fails over You The provider
Control over configuration and extensions Full Limited to what the provider allows
Superuser / file system access Yes Usually no
Typical cost Lower raw cost, higher staff time Higher price per server, much less staff time
Moving away later Easy Usually fine for standard SQL; harder if you use provider-only features

Managed doesn't mean "no work". You still own the schema, the queries, the indexes, user permissions, connection limits and the bill. A slow query is just as slow on a managed server, and the provider's backups only help if you know how to restore them and have tested it.

Whichever option you choose, the first thing to check when you connect is what you're talking to: which product and which version. Each dialect has its own way to ask. Here's a slightly fuller check than plain sqlite_version() for SQLite:

SQL Which database am I connected to?

Press Run (or Ctrl + Enter) to see the result here.

The browser runner reports SQLite 3.49.1, the main database, the default delete journal mode and 0 tables (this example starts from an empty database). Running a check like this first catches the classic mistake of being connected to the wrong server or an empty database. The other systems answer the same questions like this (read-only):

SQL
-- PostgreSQL
SELECT version(), current_database(), current_user;

-- MySQL / MariaDB
SELECT VERSION(), DATABASE(), CURRENT_USER();

-- SQL Server
SELECT @@VERSION, DB_NAME(), SUSER_NAME();

-- Oracle
SELECT banner_full FROM v$version;

Managed services also give you a connection string, a single line holding the host, port, database name, user and options such as required TLS encryption. You'll learn how applications use them in Connecting to Databases from Code, and how to protect the data in transit and at rest in Encrypting Data in the Database. For learning, most providers have a free tier that's plenty for practicing everything in this course on a real server.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement about the SQL standard is true?

    The standard is ISO/IEC 9075. Each product implements a different subset and adds its own extensions.
  2. 2. You copy a query that ends with FETCH FIRST 5 ROWS ONLY into SQLite. What happens?

    SQLite doesn't support FETCH FIRST; it uses LIMIT 5 instead, so the parser stops with a syntax error near FETCH.
  3. 3. In a normal (non-STRICT) SQLite table, what happens when you insert 'forty-two' into an INTEGER column?

    SQLite uses flexible typing: it converts values when it can do so without loss and otherwise keeps them as they are. STRICT tables reject the value.
  4. 4. What does SELECT 'Cast Iron' || NULL; return in SQLite?

    Concatenating anything with NULL gives NULL. The concat() function skips NULL arguments instead.
  5. 5. Which of these does SQLite NOT have?

    SQLite has no users or permissions; access depends on who can open the database file.

Summary

  • SQL is an ISO standard (ISO/IEC 9075), but each database speaks a dialect with its own extensions and gaps.
  • The everyday core (SELECT, WHERE, joins, grouping) is nearly identical everywhere; row limits, auto-numbering, quoting, dates and upserts differ.
  • SQLite is an embedded, serverless database in a single file. PostgreSQL, MySQL/MariaDB, SQL Server and Oracle are client/server systems.
  • SQLite uses LIMIT, flexible typing (unless STRICT), and has no users or stored procedures.
  • To work outside the browser, use the sqlite3 shell, Docker images or installers for server databases, and a graphical client such as DBeaver.

Next: SQL Command Types: DDL, DML, DQL, DCL and TCL, a tour of the five families of SQL statements.