SQL Cheatsheet
Common SQL with notes on where results surprise people, and how Postgres, MySQL and SQLite differ.
By BoltQuickTools team · Updated November 8, 2026
Examples use a users and orders schema. Syntax is portable unless a comment names a database. To tidy a long query, use the SQL formatter.
Order of evaluation
You write SELECT first, but a query logically runs in this order:
FROM / JOIN > WHERE > GROUP BY > HAVING > window functions
> SELECT > DISTINCT > ORDER BY > LIMIT
This explains why, in standard SQL, WHERE cannot use a SELECT alias or an aggregate, and why window functions cannot appear in WHERE: those values do not exist yet. To filter on a window function, wrap the query in a CTE.
Filtering
WHERE age >= 18 AND country IN ('US', 'CA')
WHERE name LIKE 'Al%' -- % any run, _ exactly one character
WHERE name ILIKE 'al%' -- Postgres only: case-insensitive
WHERE email IS NULL
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE id = ANY('{1,2,3}'::int[]) -- Postgres array form
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id)
BETWEEN includes both ends. On a timestamp column, BETWEEN '2026-01-01' AND '2026-12-31' stops at midnight at the start of December 31 and misses that day. The half-open range above does not.
NULL rules
col = NULL -- never true; use col IS NULL col <> 5 -- NULL rows are excluded too COALESCE(nickname, name) -- first non-NULL value NULLIF(a, 0) -- NULL if a = 0; avoids divide-by-zero: b / NULLIF(a, 0) a IS NOT DISTINCT FROM b -- NULL-safe equals (Postgres; SQLite 3.39+) a <=> b -- NULL-safe equals (MySQL) a IS b -- NULL-safe equals (SQLite)
Aggregates skip NULLs, so COUNT(col) counts non-NULL values while COUNT(*) counts rows. NOT IN (subquery) returns no rows at all if the subquery yields a NULL; use NOT EXISTS. For sorting, Postgres treats NULLs as largest, and MySQL and SQLite as smallest.
Joins
INNER JOIN
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id;
Plain JOIN means INNER JOIN. Users with no orders disappear.
LEFT JOIN
SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.name;
Keeps every user. Count a column from the right-hand table: COUNT(*) would report 1 for users with no orders.
Filtering a LEFT JOIN
-- Keeps all users, joining only paid orders: LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid' -- Silently becomes an INNER JOIN: LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'
Rows with no match
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
Self-join
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;
Grouping and aggregation
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age, SUM(total_spent) AS revenue FROM users WHERE active = true -- filters rows before grouping GROUP BY country HAVING COUNT(*) > 10 -- filters groups after aggregating ORDER BY revenue DESC;
Conditional totals
SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid, -- Postgres, SQLite 3.30+ SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_too -- works everywhere FROM orders;
Window functions
They compute across related rows without collapsing them. Available in Postgres, MySQL 8.0+ and SQLite 3.25+.
Ranking
SELECT name, country, total_spent, ROW_NUMBER() OVER w AS row_num, -- 1,2,3,4 (ties broken arbitrarily) RANK() OVER w AS rnk, -- 1,2,2,4 DENSE_RANK() OVER w AS dense -- 1,2,2,3 FROM users WINDOW w AS (PARTITION BY country ORDER BY total_spent DESC);
Top N per group
WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders ) SELECT * FROM ranked WHERE rn = 1; -- latest order per user
Running totals and moving averages
SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_total, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM daily_stats;
With ORDER BY and no frame, the default frame is RANGE, which groups rows with equal sort values together. If day can repeat, write ROWS UNBOUNDED PRECEDING explicitly.
Compare with the previous row
SELECT day, views, views - LAG(views, 1) OVER (ORDER BY day) AS delta FROM analytics;
CTEs
WITH recent AS ( SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days' -- Postgres ), high_value AS ( SELECT user_id, SUM(total) AS spent FROM recent GROUP BY user_id HAVING SUM(total) > 500 ) SELECT u.name, hv.spent FROM users u JOIN high_value hv ON hv.user_id = u.id;
CTEs split a query into named steps. See the dialect table for date arithmetic in other databases.
Recursive CTE (org chart)
WITH RECURSIVE org AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, org.level + 1 FROM employees e JOIN org ON e.manager_id = org.id WHERE org.level < 20 -- safety limit against cycles in the data ) SELECT * FROM org;
Changing data
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
-- Upsert (Postgres, SQLite 3.24+)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = excluded.name;
-- Upsert (MySQL 8.0.19+; older versions use VALUES(name))
INSERT INTO users (id, name) VALUES (1, 'Alice') AS new
ON DUPLICATE KEY UPDATE name = new.name;
UPDATE users SET active = false WHERE last_login < '2025-01-01';
DELETE FROM users WHERE active = false AND created_at < '2020-01-01';
Before a risky UPDATE or DELETE, run the same WHERE as a SELECT, or work inside BEGIN; ... ROLLBACK; until the row count looks right. MySQL DDL statements commit implicitly, so they cannot be rolled back.
Indexes
CREATE INDEX idx_users_email ON users(email); CREATE UNIQUE INDEX idx_users_email_uq ON users(email); CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC); -- Partial index (Postgres, SQLite) CREATE INDEX idx_active ON users(email) WHERE active = true; -- Expression index (Postgres, SQLite; MySQL 8.0.13+ needs extra parentheses) CREATE INDEX idx_lower_email ON users(LOWER(email)); -- Postgres: build without blocking writes CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
A multi-column index helps queries that filter on its leftmost columns. (user_id, created_at) serves user_id = ? but not created_at > ? alone. CREATE INDEX CONCURRENTLY cannot run inside a transaction, and if it fails it leaves an invalid index to drop.
Dialect differences
| What | Postgres | MySQL | SQLite |
|---|---|---|---|
| Auto-increment | GENERATED ALWAYS AS IDENTITY or SERIAL | AUTO_INCREMENT | INTEGER PRIMARY KEY |
| UUID type | Native UUID | BINARY(16) or CHAR(36) | TEXT or BLOB |
| String concat | a || b | CONCAT(a,b); || is OR by default | a || b |
| Limit + offset | LIMIT n OFFSET m | LIMIT n OFFSET m or LIMIT m, n | LIMIT n OFFSET m or LIMIT m, n |
| RETURNING | Yes | No (MariaDB has it for INSERT and DELETE) | 3.35+ |
| Boolean | Native | BOOLEAN is an alias for TINYINT(1) | INTEGER 0/1; TRUE and FALSE since 3.23 |
| LIKE case | Case-sensitive; use ILIKE | Follows collation; default is case-insensitive | Case-insensitive for ASCII |
| 30 days ago | NOW() - INTERVAL '30 days' | NOW() - INTERVAL 30 DAY | datetime('now', '-30 days') |
| Aggregate strings | STRING_AGG(x, ',') | GROUP_CONCAT(x) | GROUP_CONCAT(x); STRING_AGG since 3.44 |
| JSON field as text | doc->>'name' | doc->>'$.name' | doc->>'$.name' (3.38+) |
| FULL OUTER JOIN | Yes | No; combine LEFT and RIGHT with UNION | 3.39+ |
| Quote identifiers | "name" | `name` | "name" |
| Query plan | EXPLAIN ANALYZE | EXPLAIN ANALYZE (8.0.18+) | EXPLAIN QUERY PLAN |
Performance notes
- Read the plan before you optimize.
EXPLAIN ANALYZEreally runs the query, so wrap writes in a transaction you roll back. - Indexes speed up reads and slow down writes. Index the columns you filter, join or sort on, and check that the plan uses them.
- Wrapping an indexed column in a function, as in
WHERE LOWER(email) = ?, prevents use of a plain index on it. Add an expression index or compare the raw column. - Name your columns rather than using
SELECT *in application code. It avoids fetching unneeded data and breakage when the table changes. ORacross different columns can be hard to index. If the plan is poor, tryUNIONof two indexed queries. UseUNION ALLonly if a row cannot match both.LIKE '%text%'cannot use a normal B-tree index. Consider a trigram index (Postgrespg_trgm) or full-text search.LIKE 'text%'can use one, subject to collation.OFFSETstill reads the skipped rows, so deep pages get slower. Keyset pagination stays fast:WHERE id > :last_id ORDER BY id LIMIT 20.
Last updated