# SQL Cheat Sheet: MySQL, MariaDB and PostgreSQL Syntax

Source: https://srvscripts.com/guides/sql-cheat-sheet/
Updated: 2026-10-09
Publisher: srvScripts (https://srvscripts.com/)

**Short answer:** Most SQL is the same in MySQL, MariaDB and PostgreSQL: `SELECT`, `JOIN`, `GROUP BY`, CTEs and window functions. The differences are in the details: upserts (`ON DUPLICATE KEY UPDATE` vs `ON CONFLICT`), string aggregation (`GROUP_CONCAT` vs `string_agg`), identifier quoting (backticks vs double quotes), auto-increment keys, LIKE case sensitivity and `RETURNING`. The tables below show each pattern side by side, with admin queries for processes, sizes, users and backups.

The examples in this SQL cheat sheet were run on 9 October 2026 against scratch databases on MariaDB 10.11.14 and PostgreSQL 16.13. MySQL-only syntax was checked against the MySQL 8.4 Reference Manual.

## Selecting and filtering rows

| Syntax | What it does | Notes |
| --- | --- | --- |
| SELECT name, email FROM customers WHERE country = 'GB' ORDER BY name LIMIT 10; | Columns, filter, sort, first 10 rows | Same in all three |
| LIMIT 10 OFFSET 20 | Skip 20 rows, return 10 | PostgreSQL also accepts FETCH FIRST 10 ROWS ONLY |
| SELECT DISTINCT country FROM customers; | Unique values |  |
| WHERE total BETWEEN 50 AND 150 | Inclusive range |  |
| WHERE status IN ('paid','pending') | Match a list | NOT IN returns no rows if the list contains NULL |
| WHERE email IS NULL | Missing values | = NULL is never true |
| WHERE name LIKE 'a%' | Pattern: % any run, _ one character | Case-insensitive in MySQL/MariaDB with the default _ci collations; case-sensitive in PostgreSQL |
| WHERE name ILIKE 'a%' | Case-insensitive LIKE | PostgreSQL only |
| CASE WHEN country = 'GB' THEN 'UK' ELSE 'Other' END | Conditional value | Standard SQL |
| COALESCE(phone, email, 'none') | First non-NULL argument | Standard SQL |

On our test data, `WHERE name LIKE 'a%'` returned Alice on MariaDB and no rows on PostgreSQL. Use `ILIKE` or `lower(name) LIKE 'a%'` in PostgreSQL when you want the MySQL behaviour.

## Joins and subqueries

| Syntax | What it does |
| --- | --- |
| SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id; | Inner join: only rows with a match on both sides |
| ... FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL; | Customers with no orders (anti-join) |
| RIGHT JOIN / FULL OUTER JOIN | FULL OUTER JOIN exists in PostgreSQL only; emulate it in MySQL/MariaDB with a LEFT JOIN UNION a RIGHT JOIN |
| WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 100) | Correlated subquery; stops at the first match |
| SELECT ... UNION SELECT ... | Combine results and remove duplicates; UNION ALL keeps them and is faster |

## Grouping and aggregates

| Syntax | What it does | MySQL / MariaDB vs PostgreSQL |
| --- | --- | --- |
| COUNT(*), COUNT(col), SUM, AVG, MIN, MAX | Aggregates. COUNT(col) skips NULLs | Same |
| GROUP BY status HAVING COUNT(*) > 1 | Filter groups after aggregation | Same |
| List values in one string | Concatenate per group | GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') vs string_agg(name, ', ' ORDER BY name) |
| Group by month | Date bucket | DATE_FORMAT(created_at, '%Y-%m') vs to_char(created_at, 'YYYY-MM') or date_trunc('month', created_at) |
| Join strings | Concatenation | CONCAT(a, b) in both. || concatenates only in PostgreSQL; in MySQL/MariaDB it is logical OR by default ('a' || 'b' returned 0) |

```
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;
```

NULL handling differs: `CONCAT('a', NULL)` returned NULL on MariaDB but `a` on PostgreSQL, where `'a' || NULL` is NULL. Wrap nullable columns in `COALESCE` when building strings.

## CTEs and window functions

Both need MySQL 8.0+ or MariaDB 10.2+; PostgreSQL has had them for years.

| Syntax | What it does |
| --- | --- |
| WITH paid AS (SELECT ...) SELECT ... FROM paid; | Named subquery (common table expression) |
| WITH RECURSIVE ... | Walk trees and hierarchies |
| ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) | Number rows inside each group |
| SUM(total) OVER (ORDER BY created_at) | Running total |
| RANK(), DENSE_RANK(), LAG(col), LEAD(col) | Ranking and previous/next row values |

Latest order per customer, a common pattern:

```
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;
```

## Insert, update, delete and upsert

| Task | MySQL / MariaDB | PostgreSQL |
| --- | --- | --- |
| Insert rows | INSERT INTO tags (name) VALUES ('a'), ('b'); | Same |
| Update | UPDATE orders SET status = 'cancelled' WHERE status = 'pending'; | Same |
| Delete | DELETE FROM orders WHERE status = 'cancelled'; | Same |
| Upsert | INSERT ... ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits); | INSERT ... ON CONFLICT (name) DO UPDATE SET hits = tags.hits + EXCLUDED.hits; |
| Skip duplicates | INSERT IGNORE INTO ... (turns errors into warnings) | INSERT ... ON CONFLICT DO NOTHING; |
| Get the new ID | SELECT LAST_INSERT_ID(); | INSERT ... RETURNING id; |
| RETURNING | MariaDB 10.5+ on INSERT and DELETE (not UPDATE); MySQL has none | INSERT, UPDATE and DELETE |

MySQL 8.0.20 and later deprecate `VALUES()` inside ON DUPLICATE KEY UPDATE. Use a row alias instead: `INSERT INTO tags (name, hits) VALUES ('linux', 1) AS new ON DUPLICATE KEY UPDATE hits = hits + new.hits;`. MariaDB still uses `VALUES()`.

Run the `WHERE` clause as a `SELECT` first, then turn it into UPDATE or DELETE. An UPDATE without WHERE changes every row. Both upsert forms consumed an ID value on our test even when they updated an existing row, so gaps in AUTO_INCREMENT and identity columns are normal.

## Tables, indexes and data types

| Task | MySQL / MariaDB | PostgreSQL |
| --- | --- | --- |
| Auto-increment key | id INT AUTO_INCREMENT PRIMARY KEY | id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (serial is the older form) |
| Quote an identifier | `order` (backticks) | "order" (double quotes; unquoted names fold to lower case) |
| Date and time | DATETIME, TIMESTAMP | timestamptz (stores UTC, shows in session time zone) |
| Boolean | BOOLEAN is an alias for TINYINT(1) | Real boolean type |
| JSON | JSON_VALUE(doc, '$.a.b') | jsonb with doc ->> 'key' and doc #>> '{a,b}' |
| List tables | SHOW TABLES; | \dt in psql |
| Describe a table | DESCRIBE orders; or SHOW CREATE TABLE orders\G | \d orders |
| Add a column | ALTER TABLE t ADD COLUMN c INT NOT NULL DEFAULT 0; | Same |
| Rename a column | ALTER TABLE t RENAME COLUMN a TO b; | Same |
| Create an index | CREATE INDEX idx_orders_customer ON orders (customer_id, created_at); | Same; add CONCURRENTLY to avoid blocking writes |

## Transactions and EXPLAIN

| Syntax | What it does |
| --- | --- |
| BEGIN; or START TRANSACTION; | Start a transaction |
| COMMIT; / ROLLBACK; | Keep or undo the changes |
| EXPLAIN SELECT ... | Show the plan without running the query |
| EXPLAIN ANALYZE SELECT ... | Run the query and show real timings (PostgreSQL, MySQL 8.0.18+). MariaDB uses ANALYZE SELECT ... |

DDL behaves differently. In MySQL and MariaDB, `CREATE`, `ALTER` and `DROP` commit the open transaction implicitly: our `CREATE TABLE` inside a transaction survived `ROLLBACK` on MariaDB. In PostgreSQL the same rollback removed the table. Also remember that EXPLAIN ANALYZE really executes the statement, so wrap an UPDATE or DELETE in `BEGIN` and `ROLLBACK`.

## Users and privileges

| Task | MySQL / MariaDB | PostgreSQL |
| --- | --- | --- |
| Create a login | CREATE USER 'app'@'localhost' IDENTIFIED BY 'S3cret-pass'; | CREATE ROLE app LOGIN PASSWORD 'S3cret-pass'; |
| Grant data access | GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'localhost'; | GRANT CONNECT ON DATABASE shop TO app; then GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app; |
| Cover future tables | Database-level grant already does | ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app; |
| Show grants | SHOW GRANTS FOR 'app'@'localhost'; | \du app and \dp |
| Revoke | REVOKE DELETE ON shop.* FROM 'app'@'localhost'; | REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app; |

`FLUSH PRIVILEGES` is not needed after GRANT or CREATE USER; it only matters if someone edits the grant tables directly. In PostgreSQL 15 and later, ordinary roles can no longer create objects in the `public` schema unless you grant `CREATE` on it.

## Admin queries for server admins

| Task | MySQL / MariaDB | PostgreSQL |
| --- | --- | --- |
| Running queries | SHOW FULL PROCESSLIST; | SELECT pid, usename, state, now() - query_start AS runtime, left(query, 60) FROM pg_stat_activity WHERE state  'idle'; |
| Stop a query | KILL QUERY 1234; (KILL 1234 drops the connection) | SELECT pg_cancel_backend(1234); or pg_terminate_backend(1234) |
| Connections now / limit | SHOW GLOBAL STATUS LIKE 'Threads_connected'; and SHOW VARIABLES LIKE 'max_connections'; | SELECT count(*) FROM pg_stat_activity; and SHOW max_connections; |
| Database sizes | Query below | SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database; |
| Backup | mariadb-dump --single-transaction --routines --triggers shop > shop.sql (mysqldump on MySQL) | pg_dump -Fc shop > shop.dump |
| Restore | mariadb shop < shop.sql | pg_restore -d shop_copy shop.dump |

```
SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
```

The defaults we saw for max_connections were 151 on MariaDB and 100 on PostgreSQL. On cPanel servers, also check per-user limits such as `max_user_connections` before raising the global value.

**Official documentation (checked 9 October 2026):** [MariaDB: SQL Statements](https://mariadb.com/docs/server/reference/sql-statements) · [MariaDB: INSERT ON DUPLICATE KEY UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-on-duplicate-key-update) · [MySQL 8.4 Reference Manual: SQL Statements](https://dev.mysql.com/doc/refman/8.4/en/sql-statements.html) · [MySQL 8.4: INSERT … ON DUPLICATE KEY UPDATE](https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html) · [PostgreSQL: SQL Commands](https://www.postgresql.org/docs/current/sql-commands.html) · [PostgreSQL: INSERT (ON CONFLICT, RETURNING)](https://www.postgresql.org/docs/current/sql-insert.html) · [PostgreSQL: Window Functions](https://www.postgresql.org/docs/current/functions-window.html) · [PostgreSQL: Cumulative Statistics System (pg_stat_activity)](https://www.postgresql.org/docs/current/monitoring-stats.html)

## SQL Cheat Sheet at a glance

In short: Everyday SQL syntax with the MySQL, MariaDB and PostgreSQL differences that trip people up, plus admin queries for database servers.

SQL Cheat Sheet: what this guide covers.

**Related:** [SQL Formatter and Beautifier](https://srvscripts.com/tools/sql-formatter/) · [AI Slow Query Advisor for MySQL and MariaDB](https://srvscripts.com/tools/ai-slow-query-advisor/) · [Find cPanel, DirectAdmin and MySQL Passwords from the CLI (and One-Time Login Links)](https://srvscripts.com/guides/cpanel-directadmin-mysql-password-cli/).