SQL Dialect Reference
The same operation in PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and SQLite, side by side — checked syntax with version notes.
● Runs 100% in your browser — nothing you type leaves this page.
ⓘ This is a reference, not a converter: pick an operation and see the exact syntax for each engine. It does not translate free text.
Syntax works on these versions or newer, unless marked “since”: PostgreSQL 13+ · MySQL 8.0+ · MariaDB 10.6+ · SQL Server 2017+ · Oracle 19c+ · SQLite 3.35+
PostgreSQL and SQLite examples are run automatically on the real engines (PostgreSQL 18.6, SQLite 3.54.0). MySQL, MariaDB, SQL Server and Oracle examples follow each vendor’s documentation.
Querying
Return only the first N rows
Get the 5 highest salaries.
PostgreSQL 13+
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
MySQL 8.0+
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
MariaDB 10.6+
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
SQL Server 2017+
SELECT TOP (5) first_name, salary FROM employees ORDER BY salary DESC;
Oracle 19c+
since 12cSELECT first_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
Before 12c: filter on ROWNUM in an outer query.
SQLite 3.35+
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 5;
Querying
Pagination (skip rows, then take N)
Page 3 with 5 rows per page: skip 10, take 5.
PostgreSQL 13+
SELECT id, first_name FROM employees ORDER BY id LIMIT 5 OFFSET 10;
MySQL 8.0+
SELECT id, first_name FROM employees ORDER BY id LIMIT 5 OFFSET 10;
Also written LIMIT 10, 5 (offset first).
MariaDB 10.6+
SELECT id, first_name FROM employees ORDER BY id LIMIT 5 OFFSET 10;
Also written LIMIT 10, 5 (offset first).
SQL Server 2017+
since 2012SELECT id, first_name FROM employees ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
ORDER BY is required with OFFSET/FETCH.
Oracle 19c+
since 12cSELECT id, first_name FROM employees ORDER BY id OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
SQLite 3.35+
SELECT id, first_name FROM employees ORDER BY id LIMIT 5 OFFSET 10;
Querying
Case-insensitive search
Find last names containing "es", whatever the case.
PostgreSQL 13+
SELECT first_name, last_name FROM employees WHERE last_name ILIKE '%es%';
LIKE is case-sensitive in PostgreSQL; ILIKE is not.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT first_name, last_name FROM employees WHERE last_name LIKE '%es%';
Case-insensitive with the default _ci collations (e.g. utf8mb4_0900_ai_ci).
MariaDB 10.6+
SELECT first_name, last_name FROM employees WHERE last_name LIKE '%es%';
Case-insensitive with the default _ci collations.
SQL Server 2017+
SELECT first_name, last_name FROM employees WHERE last_name LIKE '%es%';
Depends on the column collation; the default (…_CI_AS) is case-insensitive.
Oracle 19c+
SELECT first_name, last_name
FROM employees
WHERE UPPER(last_name) LIKE UPPER('%es%');Comparisons are case-sensitive by default.
SQLite 3.35+
SELECT first_name, last_name FROM employees WHERE last_name LIKE '%es%';
LIKE is case-insensitive for ASCII letters only.
▶ Run in SQL PlaygroundQuerying
Top N rows per group
The best-paid employee of each department, with a window function.
PostgreSQL 13+
SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;MySQL 8.0+
since 8.0SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;MariaDB 10.6+
since 10.2SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;SQL Server 2017+
SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) AS ranked
WHERE rn = 1;The derived table needs an alias.
Oracle 19c+
SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn = 1;Table aliases cannot use the AS keyword in Oracle.
SQLite 3.35+
since 3.25SELECT department_id, first_name, salary
FROM (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;Querying
Recursive query (hierarchy)
Everyone who reports to employee 1, directly or indirectly.
PostgreSQL 13+
WITH RECURSIVE chain AS ( SELECT id, first_name, manager_id, 1 AS depth FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
MySQL 8.0+
since 8.0WITH RECURSIVE chain AS ( SELECT id, first_name, manager_id, 1 AS depth FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
MariaDB 10.6+
since 10.2WITH RECURSIVE chain AS ( SELECT id, first_name, manager_id, 1 AS depth FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
SQL Server 2017+
WITH chain AS ( SELECT id, first_name, manager_id, 1 AS depth FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
No RECURSIVE keyword. The statement before WITH must end with a semicolon.
Oracle 19c+
since 11gR2WITH chain (id, first_name, manager_id, depth) AS ( SELECT id, first_name, manager_id, 1 FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
No RECURSIVE keyword; the column list after the CTE name is required. CONNECT BY is the older alternative.
SQLite 3.35+
WITH RECURSIVE chain AS ( SELECT id, first_name, manager_id, 1 AS depth FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.first_name, e.manager_id, c.depth + 1 FROM employees e JOIN chain c ON e.manager_id = c.id ) SELECT * FROM chain ORDER BY depth, id;
Querying
Pick a random row
One random employee (fine for small tables; slow on big ones).
MySQL 8.0+
SELECT first_name FROM employees ORDER BY RAND() LIMIT 1;
MariaDB 10.6+
SELECT first_name FROM employees ORDER BY RAND() LIMIT 1;
SQL Server 2017+
SELECT TOP (1) first_name FROM employees ORDER BY NEWID();
Oracle 19c+
SELECT first_name FROM employees ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROWS ONLY;
Querying
Join values from many rows into one string
A comma-separated list of first names per department.
PostgreSQL 13+
SELECT department_id, string_agg(first_name, ', ' ORDER BY first_name) AS people FROM employees GROUP BY department_id;
MySQL 8.0+
SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS people FROM employees GROUP BY department_id;
Output is cut at group_concat_max_len (1024 bytes by default).
MariaDB 10.6+
SELECT department_id, GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS people FROM employees GROUP BY department_id;
Output is cut at group_concat_max_len.
SQL Server 2017+
since 2017SELECT department_id, STRING_AGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS people FROM employees GROUP BY department_id;
Oracle 19c+
SELECT department_id, LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS people FROM employees GROUP BY department_id;
SQLite 3.35+
SELECT department_id, group_concat(first_name, ', ') AS people FROM employees GROUP BY department_id;
ORDER BY inside the aggregate is supported from 3.44: group_concat(first_name, ', ' ORDER BY first_name).
▶ Run in SQL PlaygroundFunctions & expressions
Concatenate strings
Build "First Last" from two columns.
PostgreSQL 13+
SELECT first_name || ' ' || last_name AS full_name FROM employees;
CONCAT(first_name, ' ', last_name) also works and treats NULL as an empty string.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
|| means OR unless the PIPES_AS_CONCAT SQL mode is enabled. CONCAT returns NULL if any argument is NULL; CONCAT_WS skips NULLs.
MariaDB 10.6+
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
|| means OR unless sql_mode includes PIPES_AS_CONCAT (or ORACLE).
SQL Server 2017+
since 2012SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
The + operator also works (first_name + ' ' + last_name) but returns NULL if any part is NULL.
Oracle 19c+
SELECT first_name || ' ' || last_name AS full_name FROM employees;
CONCAT() takes exactly two arguments in Oracle.
SQLite 3.35+
SELECT first_name || ' ' || last_name AS full_name FROM employees;
concat() exists from 3.44.
▶ Run in SQL PlaygroundFunctions & expressions
Replace NULL with a default value
Show 0 when an employee has no manager.
PostgreSQL 13+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
MySQL 8.0+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
IFNULL(manager_id, 0) is the two-argument shortcut.
MariaDB 10.6+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
IFNULL(manager_id, 0) is the two-argument shortcut.
SQL Server 2017+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
ISNULL(manager_id, 0) is the T-SQL shortcut (result type follows the first argument).
Oracle 19c+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
NVL(manager_id, 0) is the Oracle shortcut.
SQLite 3.35+
SELECT first_name, COALESCE(manager_id, 0) AS manager FROM employees;
IFNULL(manager_id, 0) also works.
▶ Run in SQL PlaygroundFunctions & expressions
Conditional value (if / else)
Label salaries as high, medium or low.
PostgreSQL 13+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;MySQL 8.0+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;IF(cond, a, b) also works for two branches.
MariaDB 10.6+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;IF(cond, a, b) also works for two branches.
SQL Server 2017+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;IIF(cond, a, b) also works for two branches (2012+).
Oracle 19c+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;SQLite 3.35+
SELECT first_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'medium'
ELSE 'low' END AS band
FROM employees;iif(cond, a, b) also works (3.32+).
▶ Run in SQL PlaygroundFunctions & expressions
Part of a string
The first 3 letters of each last name.
PostgreSQL 13+
SELECT SUBSTRING(last_name FROM 1 FOR 3) AS short FROM employees;
substr(last_name, 1, 3) also works.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT SUBSTRING(last_name, 1, 3) AS short FROM employees;
MariaDB 10.6+
SELECT SUBSTRING(last_name, 1, 3) AS short FROM employees;
SQL Server 2017+
SELECT SUBSTRING(last_name, 1, 3) AS short FROM employees;
All three arguments are required.
Oracle 19c+
SELECT SUBSTR(last_name, 1, 3) AS short FROM employees;
Functions & expressions
String length
Number of characters in each email.
PostgreSQL 13+
SELECT email, length(email) AS chars FROM employees;
octet_length() counts bytes.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT email, CHAR_LENGTH(email) AS chars FROM employees;
LENGTH() counts bytes, not characters — they differ for non-ASCII text.
MariaDB 10.6+
SELECT email, CHAR_LENGTH(email) AS chars FROM employees;
LENGTH() counts bytes (unless sql_mode=ORACLE).
SQL Server 2017+
SELECT email, LEN(email) AS chars FROM employees;
LEN ignores trailing spaces; DATALENGTH counts bytes.
Oracle 19c+
SELECT email, LENGTH(email) AS chars FROM employees;
LENGTH('') is NULL, because Oracle stores empty strings as NULL.
Functions & expressions
Convert a value to another type
Turn the text '42' into a number.
PostgreSQL 13+
SELECT CAST('42' AS integer) AS n;PostgreSQL shorthand: '42'::integer
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT CAST('42' AS SIGNED) AS n;MySQL cannot CAST AS INTEGER: use SIGNED, UNSIGNED or DECIMAL.
MariaDB 10.6+
SELECT CAST('42' AS INTEGER) AS n;INTEGER is short for SIGNED INTEGER here.
SQL Server 2017+
SELECT CAST('42' AS int) AS n;TRY_CAST returns NULL instead of an error (2012+).
Oracle 19c+
SELECT CAST('42' AS NUMBER) AS n FROM dual;TO_NUMBER(...) is the common alternative. Before 23ai every SELECT needs FROM dual.
Functions & expressions
Current date and time
Today's date and the current timestamp.
PostgreSQL 13+
SELECT CURRENT_DATE, now();
now() is the start of the current transaction; clock_timestamp() is the actual time.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT CURDATE(), NOW();
MariaDB 10.6+
SELECT CURDATE(), NOW();
SQL Server 2017+
SELECT CAST(GETDATE() AS date), SYSDATETIME();
Oracle 19c+
SELECT TRUNC(SYSDATE), SYSTIMESTAMP FROM dual;
An Oracle DATE always includes a time of day; TRUNC sets it to midnight.
SQLite 3.35+
SELECT date('now'), datetime('now');Results are UTC; add 'localtime' for local time.
▶ Run in SQL PlaygroundFunctions & expressions
Add days to a date
The date 30 days after each hire date.
PostgreSQL 13+
SELECT hired_on, hired_on + INTERVAL '30 days' AS probation_end FROM employees;
date + 30 (an integer) also works and returns a date.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT hired_on, DATE_ADD(hired_on, INTERVAL 30 DAY) AS probation_end FROM employees;
MariaDB 10.6+
SELECT hired_on, DATE_ADD(hired_on, INTERVAL 30 DAY) AS probation_end FROM employees;
SQL Server 2017+
SELECT hired_on, DATEADD(day, 30, hired_on) AS probation_end FROM employees;
Oracle 19c+
SELECT hired_on, hired_on + 30 AS probation_end FROM employees;
hired_on + INTERVAL '30' DAY also works.
SQLite 3.35+
SELECT hired_on, date(hired_on, '+30 days') AS probation_end FROM employees;
Functions & expressions
Days between two dates
How many days each employee has been with the company until 2026-01-01.
PostgreSQL 13+
SELECT first_name, DATE '2026-01-01' - hired_on AS days FROM employees;
Subtracting two dates returns an integer number of days.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT first_name, DATEDIFF('2026-01-01', hired_on) AS days
FROM employees;DATEDIFF(later, earlier).
MariaDB 10.6+
SELECT first_name, DATEDIFF('2026-01-01', hired_on) AS days
FROM employees;DATEDIFF(later, earlier).
SQL Server 2017+
SELECT first_name, DATEDIFF(day, hired_on, '2026-01-01') AS days FROM employees;
Argument order is (unit, start, end) — the opposite of MySQL.
Oracle 19c+
SELECT first_name, DATE '2026-01-01' - hired_on AS days FROM employees;
The result can have a fraction if the dates include times.
SQLite 3.35+
SELECT first_name, julianday('2026-01-01') - julianday(hired_on) AS days
FROM employees;Functions & expressions
Format a date as text
Show the hire date as YYYY-MM.
MySQL 8.0+
SELECT DATE_FORMAT(hired_on, '%Y-%m') AS hired FROM employees;
MariaDB 10.6+
SELECT DATE_FORMAT(hired_on, '%Y-%m') AS hired FROM employees;
SQL Server 2017+
since 2012SELECT FORMAT(hired_on, 'yyyy-MM') AS hired FROM employees;
FORMAT uses .NET patterns and is slow on large sets; CONVERT(char(7), hired_on, 126) is faster.
Oracle 19c+
SELECT TO_CHAR(hired_on, 'YYYY-MM') AS hired FROM employees;
Functions & expressions
Read a value from JSON
Extract "name" from a JSON document.
PostgreSQL 13+
SELECT '{"name": "Ana", "skills": ["sql"]}'::jsonb ->> 'name' AS name;-> returns JSON, ->> returns text.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "Ana"}', '$.name')) AS name;On a JSON column: data->>'$.name'.
MariaDB 10.6+
SELECT JSON_VALUE('{"name": "Ana"}', '$.name') AS name;MariaDB has no -> / ->> operators.
SQL Server 2017+
since 2016SELECT JSON_VALUE(N'{"name": "Ana"}', '$.name') AS name;Oracle 19c+
since 12cSELECT JSON_VALUE('{"name": "Ana"}', '$.name') AS name FROM dual;SQLite 3.35+
since 3.38SELECT '{"name": "Ana"}' ->> '$.name' AS name;Before 3.38: json_extract(doc, '$.name').
▶ Run in SQL PlaygroundChanging data
Insert several rows at once
Add two departments in one statement.
PostgreSQL 13+
INSERT INTO departments (name, location) VALUES
('Legal', 'Amsterdam'),
('Support', 'Madrid');MySQL 8.0+
INSERT INTO departments (name, location) VALUES
('Legal', 'Amsterdam'),
('Support', 'Madrid');MariaDB 10.6+
INSERT INTO departments (name, location) VALUES
('Legal', 'Amsterdam'),
('Support', 'Madrid');SQL Server 2017+
INSERT INTO departments (name, location) VALUES
('Legal', 'Amsterdam'),
('Support', 'Madrid');Up to 1000 rows per VALUES list.
Oracle 19c+
INSERT ALL
INTO departments (name, location) VALUES ('Legal', 'Amsterdam')
INTO departments (name, location) VALUES ('Support', 'Madrid')
SELECT * FROM dual;Oracle 23ai also accepts a multi-row VALUES list.
SQLite 3.35+
INSERT INTO departments (name, location) VALUES
('Legal', 'Amsterdam'),
('Support', 'Madrid');Changing data
Insert and get the new id
Insert a department and read back its generated id.
PostgreSQL 13+
INSERT INTO departments (name, location)
VALUES ('Legal', 'Amsterdam')
RETURNING id;MySQL 8.0+
INSERT INTO departments (name, location)
VALUES ('Legal', 'Amsterdam');
SELECT LAST_INSERT_ID();MySQL has no RETURNING clause.
MariaDB 10.6+
since 10.5INSERT INTO departments (name, location)
VALUES ('Legal', 'Amsterdam')
RETURNING id;SQL Server 2017+
INSERT INTO departments (name, location)
OUTPUT inserted.id
VALUES ('Legal', 'Amsterdam');SCOPE_IDENTITY() after the insert also works.
Oracle 19c+
INSERT INTO departments (name, location)
VALUES ('Legal', 'Amsterdam')
RETURNING id INTO :new_id;Needs a bind variable or a PL/SQL variable to receive the value.
SQLite 3.35+
since 3.35INSERT INTO departments (name, location)
VALUES ('Legal', 'Amsterdam')
RETURNING id;last_insert_rowid() works in older versions.
▶ Run in SQL PlaygroundChanging data
Insert or update (upsert)
Create department 6, or rename it if it already exists.
PostgreSQL 13+
since 9.5INSERT INTO departments (id, name, location) VALUES (6, 'Research & Development', 'Remote') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, location = EXCLUDED.location;
MySQL 8.0+
since 8.0.19INSERT INTO departments (id, name, location) VALUES (6, 'Research & Development', 'Remote') AS new ON DUPLICATE KEY UPDATE name = new.name, location = new.location;
Older versions use VALUES(name), deprecated since 8.0.20.
MariaDB 10.6+
INSERT INTO departments (id, name, location) VALUES (6, 'Research & Development', 'Remote') ON DUPLICATE KEY UPDATE name = VALUES(name), location = VALUES(location);
MariaDB does not support the "AS new" row alias.
SQL Server 2017+
MERGE INTO departments AS target USING (VALUES (6, 'Research & Development', 'Remote')) AS src (id, name, location) ON target.id = src.id WHEN MATCHED THEN UPDATE SET name = src.name, location = src.location WHEN NOT MATCHED THEN INSERT (id, name, location) VALUES (src.id, src.name, src.location);
MERGE must end with a semicolon. With an IDENTITY id, inserting explicit ids also needs SET IDENTITY_INSERT.
Oracle 19c+
MERGE INTO departments target USING (SELECT 6 AS id, 'Research & Development' AS name, 'Remote' AS location FROM dual) src ON (target.id = src.id) WHEN MATCHED THEN UPDATE SET target.name = src.name, target.location = src.location WHEN NOT MATCHED THEN INSERT (id, name, location) VALUES (src.id, src.name, src.location);
SQLite 3.35+
since 3.24INSERT INTO departments (id, name, location) VALUES (6, 'Research & Development', 'Remote') ON CONFLICT (id) DO UPDATE SET name = excluded.name, location = excluded.location;
Changing data
Update using another table
Give everyone in the Sales department a 10% raise.
PostgreSQL 13+
UPDATE employees e SET salary = e.salary * 1.10 FROM departments d WHERE d.id = e.department_id AND d.name = 'Sales';
MySQL 8.0+
UPDATE employees e JOIN departments d ON d.id = e.department_id SET e.salary = e.salary * 1.10 WHERE d.name = 'Sales';
MariaDB 10.6+
UPDATE employees e JOIN departments d ON d.id = e.department_id SET e.salary = e.salary * 1.10 WHERE d.name = 'Sales';
SQL Server 2017+
UPDATE e SET e.salary = e.salary * 1.10 FROM employees AS e JOIN departments AS d ON d.id = e.department_id WHERE d.name = 'Sales';
Oracle 19c+
UPDATE employees SET salary = salary * 1.10 WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');
Oracle 23ai adds UPDATE … FROM; MERGE is the classic way for multi-column joins.
SQLite 3.35+
since 3.33UPDATE employees AS e SET salary = e.salary * 1.10 FROM departments AS d WHERE d.id = e.department_id AND d.name = 'Sales';
Changing data
Delete all rows from a table
Empty the project assignments table.
PostgreSQL 13+
TRUNCATE TABLE employee_projects;
Transactional: can be rolled back. Fails if other tables reference this one (use CASCADE).
▶ Run in SQL PlaygroundMySQL 8.0+
TRUNCATE TABLE employee_projects;
Causes an implicit COMMIT and cannot be rolled back. Fails on tables referenced by foreign keys.
MariaDB 10.6+
TRUNCATE TABLE employee_projects;
Causes an implicit COMMIT and cannot be rolled back.
SQL Server 2017+
TRUNCATE TABLE employee_projects;
Can be rolled back inside an explicit transaction. Not allowed on tables referenced by foreign keys.
Oracle 19c+
TRUNCATE TABLE employee_projects;
DDL: commits immediately and cannot be rolled back.
SQLite 3.35+
DELETE FROM employee_projects;
SQLite has no TRUNCATE; DELETE without WHERE is optimized to a fast truncate.
▶ Run in SQL PlaygroundTables & schema
Auto-increment primary key
A table whose id is generated automatically.
PostgreSQL 13+
since 10CREATE TABLE tickets ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL );
serial is the older shorthand.
▶ Run in SQL PlaygroundMySQL 8.0+
CREATE TABLE tickets ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL );
MariaDB 10.6+
CREATE TABLE tickets ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL );
SQL Server 2017+
CREATE TABLE tickets ( id INT IDENTITY(1,1) PRIMARY KEY, title NVARCHAR(200) NOT NULL );
Oracle 19c+
since 12cCREATE TABLE tickets ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR2(200) NOT NULL );
Before 12c: a SEQUENCE plus a trigger.
SQLite 3.35+
CREATE TABLE tickets ( id INTEGER PRIMARY KEY, title TEXT NOT NULL );
INTEGER PRIMARY KEY is an alias for the rowid and auto-assigns values. Add AUTOINCREMENT only if ids must never be reused.
▶ Run in SQL PlaygroundTables & schema
Create a table only if it does not exist
Safe to run twice.
PostgreSQL 13+
CREATE TABLE IF NOT EXISTS audit_log ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, message text NOT NULL );
MySQL 8.0+
CREATE TABLE IF NOT EXISTS audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, message TEXT NOT NULL );
MariaDB 10.6+
CREATE TABLE IF NOT EXISTS audit_log ( id INT AUTO_INCREMENT PRIMARY KEY, message TEXT NOT NULL );
SQL Server 2017+
IF OBJECT_ID(N'dbo.audit_log', N'U') IS NULL
CREATE TABLE dbo.audit_log (
id INT IDENTITY(1,1) PRIMARY KEY,
message NVARCHAR(MAX) NOT NULL
);SQL Server 2022 has no CREATE TABLE IF NOT EXISTS.
Oracle 19c+
since 23aiCREATE TABLE IF NOT EXISTS audit_log ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, message VARCHAR2(4000) NOT NULL );
Before 23ai: run CREATE TABLE inside a PL/SQL block and ignore error ORA-00955.
SQLite 3.35+
CREATE TABLE IF NOT EXISTS audit_log ( id INTEGER PRIMARY KEY, message TEXT NOT NULL );
Tables & schema
Drop a table only if it exists
No error when the table is already gone.
MySQL 8.0+
DROP TABLE IF EXISTS employee_projects;
MariaDB 10.6+
DROP TABLE IF EXISTS employee_projects;
SQL Server 2017+
since 2016DROP TABLE IF EXISTS employee_projects;
Oracle 19c+
since 23aiDROP TABLE IF EXISTS employee_projects;
Before 23ai: a PL/SQL block that ignores error ORA-00942.
Tables & schema
Add a column
Add a phone column to employees.
MySQL 8.0+
ALTER TABLE employees ADD COLUMN phone VARCHAR(30);
MariaDB 10.6+
ALTER TABLE employees ADD COLUMN phone VARCHAR(30);
SQL Server 2017+
ALTER TABLE employees ADD phone NVARCHAR(30);
No COLUMN keyword in SQL Server.
Oracle 19c+
ALTER TABLE employees ADD (phone VARCHAR2(30));
Tables & schema
Rename a column
Rename hired_on to start_date.
MySQL 8.0+
since 8.0ALTER TABLE employees RENAME COLUMN hired_on TO start_date;
MariaDB 10.6+
since 10.5.2ALTER TABLE employees RENAME COLUMN hired_on TO start_date;
Older: ALTER TABLE … CHANGE hired_on start_date DATE NOT NULL.
SQL Server 2017+
EXEC sp_rename 'dbo.employees.hired_on', 'start_date', 'COLUMN';
Oracle 19c+
ALTER TABLE employees RENAME COLUMN hired_on TO start_date;
SQLite 3.35+
since 3.25ALTER TABLE employees RENAME COLUMN hired_on TO start_date;
Tables & schema
Change a column type
Make job_title longer / a different type.
PostgreSQL 13+
ALTER TABLE employees ALTER COLUMN job_title TYPE varchar(200);
Add USING <expression> when values need converting.
▶ Run in SQL PlaygroundMySQL 8.0+
ALTER TABLE employees MODIFY COLUMN job_title VARCHAR(200) NOT NULL;
MODIFY replaces the whole definition: repeat NOT NULL, DEFAULT, etc.
MariaDB 10.6+
ALTER TABLE employees MODIFY COLUMN job_title VARCHAR(200) NOT NULL;
MODIFY replaces the whole definition: repeat NOT NULL, DEFAULT, etc.
SQL Server 2017+
ALTER TABLE employees ALTER COLUMN job_title NVARCHAR(200) NOT NULL;
Repeat NOT NULL, or the column becomes nullable.
Oracle 19c+
ALTER TABLE employees MODIFY (job_title VARCHAR2(200));
SQLite 3.35+
✕ Not supported
SQLite cannot change a column type in place: create a new table, copy the data, drop the old table and rename the new one.
Tables & schema
Drop a column
Remove the active column.
MySQL 8.0+
ALTER TABLE employees DROP COLUMN active;
MariaDB 10.6+
ALTER TABLE employees DROP COLUMN active;
SQL Server 2017+
ALTER TABLE employees DROP COLUMN active;
Drop DEFAULT or CHECK constraints on the column first.
Oracle 19c+
ALTER TABLE employees DROP COLUMN active;
SQLite 3.35+
since 3.35ALTER TABLE employees DROP COLUMN active;
Fails if the column is part of an index, key or constraint.
▶ Run in SQL PlaygroundTables & schema
Rename a table
Rename projects to initiatives.
MySQL 8.0+
RENAME TABLE projects TO initiatives;
ALTER TABLE projects RENAME TO initiatives also works.
MariaDB 10.6+
RENAME TABLE projects TO initiatives;
SQL Server 2017+
EXEC sp_rename 'dbo.projects', 'initiatives';
Oracle 19c+
ALTER TABLE projects RENAME TO initiatives;
Tables & schema
Create an index
Speed up lookups by last name.
PostgreSQL 13+
CREATE INDEX IF NOT EXISTS idx_employees_last_name ON employees (last_name);
Add CONCURRENTLY to avoid blocking writes on a busy table.
▶ Run in SQL PlaygroundMySQL 8.0+
CREATE INDEX idx_employees_last_name ON employees (last_name);
No IF NOT EXISTS for CREATE INDEX in MySQL.
MariaDB 10.6+
CREATE INDEX IF NOT EXISTS idx_employees_last_name ON employees (last_name);
SQL Server 2017+
CREATE INDEX idx_employees_last_name ON employees (last_name);
Oracle 19c+
CREATE INDEX idx_employees_last_name ON employees (last_name);
IF NOT EXISTS from 23ai.
SQLite 3.35+
CREATE INDEX IF NOT EXISTS idx_employees_last_name ON employees (last_name);
Tables & schema
Add a foreign key
A table of laptops that must point to an existing employee.
PostgreSQL 13+
CREATE TABLE laptops ( serial text PRIMARY KEY, employee_id integer REFERENCES employees (id) ON DELETE SET NULL );
MySQL 8.0+
CREATE TABLE laptops ( serial VARCHAR(50) PRIMARY KEY, employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE SET NULL );
Enforced by InnoDB (the default engine). Use a separate FOREIGN KEY clause: older MySQL versions silently ignore an inline REFERENCES on the column.
MariaDB 10.6+
CREATE TABLE laptops ( serial VARCHAR(50) PRIMARY KEY, employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE SET NULL );
SQL Server 2017+
CREATE TABLE laptops ( serial NVARCHAR(50) PRIMARY KEY, employee_id INT REFERENCES employees (id) ON DELETE SET NULL );
Oracle 19c+
CREATE TABLE laptops ( serial VARCHAR2(50) PRIMARY KEY, employee_id NUMBER REFERENCES employees (id) ON DELETE SET NULL );
Oracle has no ON UPDATE action.
SQLite 3.35+
CREATE TABLE laptops ( serial TEXT PRIMARY KEY, employee_id INTEGER REFERENCES employees (id) ON DELETE SET NULL );
Only enforced after PRAGMA foreign_keys = ON; (per connection).
▶ Run in SQL PlaygroundTables & schema
Boolean column
A true/false flag.
PostgreSQL 13+
CREATE TABLE flags ( name text PRIMARY KEY, enabled boolean NOT NULL DEFAULT false );
MySQL 8.0+
CREATE TABLE flags ( name VARCHAR(50) PRIMARY KEY, enabled BOOLEAN NOT NULL DEFAULT FALSE );
BOOLEAN is TINYINT(1); TRUE and FALSE are 1 and 0.
MariaDB 10.6+
CREATE TABLE flags ( name VARCHAR(50) PRIMARY KEY, enabled BOOLEAN NOT NULL DEFAULT FALSE );
BOOLEAN is TINYINT(1).
SQL Server 2017+
CREATE TABLE flags ( name NVARCHAR(50) PRIMARY KEY, enabled BIT NOT NULL DEFAULT 0 );
No BOOLEAN type: BIT stores 0 or 1.
Oracle 19c+
CREATE TABLE flags ( name VARCHAR2(50) PRIMARY KEY, enabled NUMBER(1) DEFAULT 0 NOT NULL CHECK (enabled IN (0, 1)) );
Oracle 23ai adds a real BOOLEAN type.
SQLite 3.35+
CREATE TABLE flags ( name TEXT PRIMARY KEY, enabled INTEGER NOT NULL DEFAULT 0 );
No boolean type: values are stored as 0 and 1 (TRUE/FALSE keywords work).
▶ Run in SQL PlaygroundUsers & permissions
Create a user that can log in
A login for an application.
PostgreSQL 13+
CREATE ROLE app_user WITH LOGIN PASSWORD 'S3cure-Passw0rd!';
CREATE USER is the same as CREATE ROLE … LOGIN.
▶ Run in SQL PlaygroundMySQL 8.0+
CREATE USER 'app_user'@'%' IDENTIFIED BY 'S3cure-Passw0rd!';
A user is name@host: '%' means any host, 'localhost' only local connections.
MariaDB 10.6+
CREATE USER 'app_user'@'%' IDENTIFIED BY 'S3cure-Passw0rd!';
SQL Server 2017+
CREATE LOGIN app_user WITH PASSWORD = 'S3cure-Passw0rd!'; -- then, inside the target database: CREATE USER app_user FOR LOGIN app_user;
A server-level LOGIN, mapped to a database USER.
Oracle 19c+
CREATE USER app_user IDENTIFIED BY "S3cure-Passw0rd!"; GRANT CREATE SESSION TO app_user;
Without CREATE SESSION the user cannot connect.
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
Give read-only access to one table
app_user may SELECT from employees, nothing else.
PostgreSQL 13+
GRANT SELECT ON employees TO app_user;
Tables in other schemas than public also need GRANT USAGE ON SCHEMA <name> TO app_user.
▶ Run in SQL PlaygroundMySQL 8.0+
GRANT SELECT ON company.employees TO 'app_user'@'%';
MariaDB 10.6+
GRANT SELECT ON company.employees TO 'app_user'@'%';
SQL Server 2017+
GRANT SELECT ON dbo.employees TO app_user;
Oracle 19c+
GRANT SELECT ON hr.employees TO app_user;
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
Give read-only access to all tables
Read everything in one schema / database, including future tables where possible.
PostgreSQL 13+
GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
ALL TABLES covers existing tables; DEFAULT PRIVILEGES covers tables created later by the same role. PostgreSQL 14+ also has the built-in role pg_read_all_data.
▶ Run in SQL PlaygroundMySQL 8.0+
GRANT SELECT ON company.* TO 'app_user'@'%';
Covers existing and future tables in the database.
MariaDB 10.6+
GRANT SELECT ON company.* TO 'app_user'@'%';
SQL Server 2017+
since 2012ALTER ROLE db_datareader ADD MEMBER app_user;
db_datareader can read every table in the current database.
Oracle 19c+
since 23aiGRANT SELECT ANY TABLE ON SCHEMA hr TO app_user;
Before 23ai there is no schema-level grant: grant each table, or use a role.
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
Take a permission back
Remove SELECT on employees from app_user.
MySQL 8.0+
REVOKE SELECT ON company.employees FROM 'app_user'@'%';
MariaDB 10.6+
REVOKE SELECT ON company.employees FROM 'app_user'@'%';
SQL Server 2017+
REVOKE SELECT ON dbo.employees FROM app_user;
DENY blocks the permission even if it comes from a role.
Oracle 19c+
REVOKE SELECT ON hr.employees FROM app_user;
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
Change a user's password
Rotate the password of app_user.
MySQL 8.0+
ALTER USER 'app_user'@'%' IDENTIFIED BY 'N3w-Passw0rd!';
MariaDB 10.6+
ALTER USER 'app_user'@'%' IDENTIFIED BY 'N3w-Passw0rd!';
SQL Server 2017+
ALTER LOGIN app_user WITH PASSWORD = 'N3w-Passw0rd!';
Oracle 19c+
ALTER USER app_user IDENTIFIED BY "N3w-Passw0rd!";
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
See what a user is allowed to do
List the table privileges of app_user.
PostgreSQL 13+
SELECT table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'app_user';
In psql: \dp employees
▶ Run in SQL PlaygroundMySQL 8.0+
SHOW GRANTS FOR 'app_user'@'%';
MariaDB 10.6+
SHOW GRANTS FOR 'app_user'@'%';
SQL Server 2017+
SELECT OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc FROM sys.database_permissions AS p JOIN sys.database_principals AS u ON u.principal_id = p.grantee_principal_id WHERE u.name = 'app_user';
Oracle 19c+
SELECT owner, table_name, privilege FROM dba_tab_privs WHERE grantee = 'APP_USER';
Unquoted names are stored in upper case. Needs DBA access; users can query user_tab_privs for themselves.
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Users & permissions
Delete a user
Remove app_user.
PostgreSQL 13+
DROP ROLE app_user;
Fails while the role owns objects or holds privileges: run REASSIGN OWNED BY app_user TO postgres; DROP OWNED BY app_user; first.
▶ Run in SQL PlaygroundMySQL 8.0+
DROP USER 'app_user'@'%';
MariaDB 10.6+
DROP USER 'app_user'@'%';
SQL Server 2017+
DROP USER app_user; -- in each database DROP LOGIN app_user; -- on the server
Oracle 19c+
DROP USER app_user CASCADE;
CASCADE also drops every object in the user's schema.
SQLite 3.35+
✕ Not supported
SQLite has no users or permissions: access is controlled by who can read the database file.
Inspecting the database
Server version
Which version am I connected to?
MySQL 8.0+
SELECT VERSION();
MariaDB 10.6+
SELECT VERSION();
SQL Server 2017+
SELECT @@VERSION;
Oracle 19c+
since 18cSELECT banner_full FROM v$version;
Use the banner column on older versions.
Inspecting the database
List databases
All databases on the server.
PostgreSQL 13+
SELECT datname FROM pg_database WHERE NOT datistemplate;
In psql: \l
▶ Run in SQL PlaygroundMySQL 8.0+
SHOW DATABASES;
MariaDB 10.6+
SHOW DATABASES;
SQL Server 2017+
SELECT name FROM sys.databases;
Oracle 19c+
SELECT name, open_mode FROM v$pdbs;
In a multitenant (CDB) setup, databases are pluggable databases (PDBs). Users and schemas play the role other engines give to databases.
SQLite 3.35+
PRAGMA database_list;
One file is one database; this lists the attached ones.
▶ Run in SQL PlaygroundInspecting the database
List tables
All tables in the current database / schema.
PostgreSQL 13+
SELECT tablename FROM pg_tables WHERE schemaname = 'public';
In psql: \dt
▶ Run in SQL PlaygroundMySQL 8.0+
SHOW TABLES;
MariaDB 10.6+
SHOW TABLES;
SQL Server 2017+
SELECT name FROM sys.tables;
Oracle 19c+
SELECT table_name FROM user_tables;
all_tables lists every table you can access.
SQLite 3.35+
SELECT name FROM sqlite_schema WHERE type = 'table';
sqlite_master on versions before 3.33. In the sqlite3 shell: .tables
▶ Run in SQL PlaygroundInspecting the database
Show a table's columns
Column names, types and nullability of employees.
PostgreSQL 13+
SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'employees' ORDER BY ordinal_position;
In psql: \d employees
▶ Run in SQL PlaygroundMySQL 8.0+
DESCRIBE employees;
MariaDB 10.6+
DESCRIBE employees;
SQL Server 2017+
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'employees' ORDER BY ORDINAL_POSITION;
EXEC sp_help employees shows more detail.
Oracle 19c+
SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES' ORDER BY column_id;
In SQL*Plus or SQLcl: DESC employees
SQLite 3.35+
SELECT name, type, "notnull"
FROM pragma_table_info('employees');Or: PRAGMA table_info(employees);
▶ Run in SQL PlaygroundInspecting the database
See running queries
What is the database busy with right now?
PostgreSQL 13+
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state <> 'idle';
Stop one with SELECT pg_cancel_backend(pid);
▶ Run in SQL PlaygroundMySQL 8.0+
SHOW FULL PROCESSLIST;
Stop one with KILL QUERY <id>;
MariaDB 10.6+
SHOW FULL PROCESSLIST;
Stop one with KILL QUERY <id>;
SQL Server 2017+
SELECT r.session_id, r.status, r.command, t.text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t;
Oracle 19c+
SELECT sid, serial#, username, status, sql_id FROM v$session WHERE type = 'USER' AND status = 'ACTIVE';
SQLite 3.35+
✕ Not supported
SQLite runs inside your application process: there is no server and no session list.
Inspecting the database
Table size on disk
How big is the employees table?
PostgreSQL 13+
SELECT pg_size_pretty(pg_total_relation_size('employees')) AS total_size;Total includes indexes and TOAST data.
▶ Run in SQL PlaygroundMySQL 8.0+
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'employees';
Values are estimates for InnoDB.
MariaDB 10.6+
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'employees';
SQL Server 2017+
EXEC sp_spaceused N'dbo.employees';
Oracle 19c+
SELECT segment_name, ROUND(bytes / 1024 / 1024, 2) AS size_mb FROM user_segments WHERE segment_name = 'EMPLOYEES';
SQLite 3.35+
SELECT page_count * page_size AS database_bytes FROM pragma_page_count(), pragma_page_size();
This is the whole database file. Per-table sizes need the dbstat virtual table, which is not always compiled in.
▶ Run in SQL PlaygroundTransactions
Run changes in a transaction
Change salaries, check the result, then undo everything.
PostgreSQL 13+
BEGIN; UPDATE employees SET salary = salary * 2; SELECT sum(salary) FROM employees; ROLLBACK; -- or COMMIT;
DDL (CREATE, ALTER, DROP) is transactional too.
▶ Run in SQL PlaygroundMySQL 8.0+
START TRANSACTION; UPDATE employees SET salary = salary * 2; SELECT SUM(salary) FROM employees; ROLLBACK; -- or COMMIT;
DDL causes an implicit COMMIT. Only transactional engines (InnoDB) can roll back.
MariaDB 10.6+
START TRANSACTION; UPDATE employees SET salary = salary * 2; SELECT SUM(salary) FROM employees; ROLLBACK; -- or COMMIT;
DDL causes an implicit COMMIT.
SQL Server 2017+
BEGIN TRANSACTION; UPDATE employees SET salary = salary * 2; SELECT SUM(salary) FROM employees; ROLLBACK TRANSACTION; -- or COMMIT TRANSACTION;
Most DDL is transactional.
Oracle 19c+
UPDATE employees SET salary = salary * 2; SELECT SUM(salary) FROM employees; ROLLBACK; -- or COMMIT;
A transaction starts automatically with the first change; there is no BEGIN. DDL commits implicitly.
SQLite 3.35+
BEGIN; UPDATE employees SET salary = salary * 2; SELECT sum(salary) FROM employees; ROLLBACK; -- or COMMIT;
DDL is transactional.
▶ Run in SQL PlaygroundThe address bar updates as you go, so you can bookmark or share the result.
What this is — and what it isn’t
This page is a side-by-side reference. Pick an operation — “return the first N rows”, “auto-increment primary key”, “give read-only access to a table” — and you see the exact syntax for six engines, with the minimum version and the gotchas worth knowing.
It is not an automatic converter: you can’t paste arbitrary SQL and get it translated. Automatic translation between dialects is never 100% reliable, and a wrong GRANT or MERGE in production is expensive. Every example here was written and checked on purpose.
How the examples are checked
- PostgreSQL and SQLite: every example is executed automatically on the real engines (PostgreSQL 18.6 and SQLite 3.54) against the same sample database you can open in the SQL Playground — use Run in SQL Playground to try one.
- MySQL, MariaDB, SQL Server and Oracle: examples follow each vendor’s official documentation. They can’t run in a browser, so they are not executed here.
All examples use the same tables (employees, departments, projects), so you can compare the engines line by line.
Versions covered
Unless an example says “since …”, it works on: PostgreSQL 13+, MySQL 8.0+, MariaDB 10.6+, SQL Server 2017+, Oracle 19c+ and SQLite 3.35+. Newer features — like IF EXISTS and BOOLEAN in Oracle 23ai — are marked.
Shell, we have a problem!
The differences that bite most often in real migrations: || means OR in MySQL, Oracle treats an empty string as NULL, TRUNCATE can’t be rolled back in MySQL and Oracle, and SQL Server’s DATEDIFF takes its arguments in the opposite order from MySQL’s.
Articles
Learn more
MySQL ERROR 1045 (28000): Access denied for user — find which part is wrong
Fix MySQL "ERROR 1045 Access denied for user": check the user@host account, the password, socket vs TCP, option files, and Docker init passwords.
Beginner · 5 min read
PostgreSQL "duplicate key value violates unique constraint": causes and fixes
Fix "duplicate key value violates unique constraint" in PostgreSQL: tell a real duplicate from a sequence that is out of sync, and fix both safely.
Intermediate · 4 min read
PostgreSQL "permission denied for table": causes and fixes
Fix "ERROR: permission denied for table" in PostgreSQL: check what a role can do, grant the right privileges, and fix the schema, sequence and future-table cases.
Intermediate · 4 min read