SQL Dialects

You now know standard SQL, and that's most of what any database needs. But each system has its own dialect: different functions, data types and conveniences. This lesson gathers the differences you're most likely to meet when moving between SQLite, PostgreSQL, MySQL, SQL Server and Oracle.

The same query, five ways

"The five most recent orders with the customer's full details" uses identical joins everywhere — only the row limit differs:

SQL postgresql-mysql-sqlite

SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_date DESC LIMIT 5;

SQL sql-server

SELECT TOP 5 o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_date DESC;

SQL oracle-and-standard

SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_date DESC FETCH FIRST 5 ROWS ONLY;

Common differences

Feature	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
Limit rows	`LIMIT`	`LIMIT`	`TOP` / `OFFSET FETCH`	`FETCH FIRST`	`LIMIT`
Concatenate	`\|\|`	`CONCAT()`	`+` / `CONCAT()`	`\|\|`	`\|\|`
Auto-increment	`IDENTITY` / `SERIAL`	`AUTO_INCREMENT`	`IDENTITY`	`IDENTITY`	`INTEGER PRIMARY KEY`
Current time	`now()`	`NOW()`	`GETDATE()`	`SYSDATE`	`datetime('now')`
Null default	`COALESCE`	`IFNULL`	`ISNULL`	`NVL`	`IFNULL`
Boolean type	`BOOLEAN`	`TINYINT(1)`	`BIT`	`BOOLEAN` (23ai)	integer 0/1
Quoting names	`"name"`	`name`	`[name]`	`"name"`	`"name"`
Upsert	`ON CONFLICT`	`ON DUPLICATE KEY`	`MERGE`	`MERGE`	`ON CONFLICT`
Case-insensitive match	`ILIKE`	`LIKE` (default collation)	`LIKE` (default collation)	`UPPER()` both sides	`LIKE` (ASCII)

COALESCE, CASE, CAST, joins, CTEs and window functions are standard and work in all five.

Exploring a database's structure

SQL postgresql-mysql-sql-server

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'customers';

SQL

SELECT name, type, "notnull", pk FROM pragma_table_info('customers');

Result 6 rows

name	type	notnull	pk
id	INTEGER	0	1
name	TEXT	1	0
email	TEXT	0	0
city	TEXT	0	0
country	TEXT	1	0
signup_date	DATE	1	0

The standard information_schema views describe tables and columns in PostgreSQL, MySQL and SQL Server. SQLite uses sqlite_schema and pragma_table_info; Oracle has its own data dictionary views (USER_TABLES, USER_TAB_COLUMNS).

Procedural extensions

Each server database adds a procedural language for stored procedures and functions — variables, loops and error handling running inside the database:

Database	Language
PostgreSQL	PL/pgSQL (and others, such as PL/Python)
SQL Server	T-SQL
Oracle	PL/SQL
MySQL	Stored procedures in SQL/PSM style
SQLite	None — logic lives in the application

SQL postgresql

CREATE FUNCTION order_total(p_order_id integer) RETURNS numeric AS $$
  SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order_id;
$$ LANGUAGE sql STABLE;

SELECT id, order_total(id) FROM orders;

Choosing a database

SQLite — embedded in an app, mobile, desktop, tests, small sites; one file, zero setup.
PostgreSQL — a strong default for new server applications: standards-compliant, extensible, rich types (JSONB, arrays, full-text, geospatial with PostGIS).
MySQL / MariaDB — widespread on web hosting and in many established platforms.
SQL Server / Oracle — common in enterprises with existing Microsoft or Oracle ecosystems.
Cloud warehouses (BigQuery, Snowflake, Redshift, Databricks) — analytics over very large datasets, with their own SQL dialects.

Writing portable SQL

Prefer standard features: COALESCE over IFNULL, CASE over IF, CAST over ::.
Use ISO date strings and explicit casts.
Don't rely on implicit behaviours: the order of rows without ORDER BY, case sensitivity, integer division, where NULLs sort.
Test against the database you'll actually run in production.

Try it yourself

Inspect the demo database's own structure.

PreviousQuery Performance NextProject: Analysing a Shop