Cheatsheets / SQL

SQL Cheatsheet

Complete SQL reference. Hit Ctrl+P to print.

SELECT & Filtering

SELECT * FROM usersSelect all columns from table
SELECT id, name FROM usersSelect specific columns
SELECT DISTINCT country FROM usersSelect unique values
SELECT name AS full_name FROM usersColumn alias
SELECT * FROM users WHERE age > 18Filter rows with WHERE
WHERE age BETWEEN 18 AND 30Range filter (inclusive)
WHERE name LIKE 'A%'Pattern match - % any chars, _ single char
WHERE status IN (1, 2, 3)Match any value in list
WHERE email IS NULLCheck for NULL
WHERE email IS NOT NULLCheck for non-NULL
WHERE a = 1 AND b = 2Both conditions must be true
WHERE a = 1 OR b = 2Either condition must be true
WHERE NOT activeNegate condition
ORDER BY name ASCSort ascending (default)
ORDER BY created_at DESCSort descending
ORDER BY country, name ASCSort by multiple columns
LIMIT 10Return at most 10 rows
LIMIT 10 OFFSET 20Pagination: skip 20, take 10

Joins

FROM a INNER JOIN b ON a.id = b.a_idINNER JOIN - rows with match in both tables
FROM a LEFT JOIN b ON a.id = b.a_idLEFT JOIN - all rows from left, matched from right
FROM a RIGHT JOIN b ON a.id = b.a_idRIGHT JOIN - all rows from right, matched from left
FROM a FULL OUTER JOIN b ON a.id = b.a_idFULL OUTER JOIN - all rows from both tables
FROM a CROSS JOIN bCartesian product - every row paired with every other
FROM a LEFT JOIN b ON ... WHERE b.id IS NULLRows in a with NO match in b
FROM users u JOIN orders o ON u.id = o.user_idTable aliases for readability
JOIN orders o ON o.user_id = u.id AND o.status = 1Additional condition in JOIN

Aggregation

SELECT COUNT(*) FROM usersCount all rows
SELECT COUNT(email) FROM usersCount non-NULL values
SELECT COUNT(DISTINCT country) FROM usersCount unique values
SELECT SUM(amount) FROM ordersSum of column values
SELECT AVG(price) FROM productsAverage value
SELECT MIN(price), MAX(price) FROM productsMinimum and maximum
GROUP BY countryGroup rows sharing the same value
SELECT country, COUNT(*) FROM users GROUP BY countryCount per group
HAVING COUNT(*) > 5Filter on aggregated result (like WHERE for groups)
GROUP BY country ORDER BY COUNT(*) DESCSort groups by aggregate
SELECT country, SUM(amount) FROM orders GROUP BY country WITH ROLLUPAdd subtotals and grand total

Subqueries

WHERE id IN (SELECT user_id FROM orders)Subquery in WHERE - filter by related data
WHERE id NOT IN (SELECT user_id FROM orders)Exclude rows with match in subquery
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)EXISTS - true if subquery returns any row
SELECT (SELECT COUNT(*) FROM o WHERE o.user_id = u.id) FROM users uScalar subquery in SELECT
FROM (SELECT ...) AS subDerived table - subquery as FROM source
WHERE age > (SELECT AVG(age) FROM users)Compare against aggregate subquery

CTEs & Window Functions

WITH cte AS (SELECT ...) SELECT * FROM cteNamed temporary result set (CTE)
WITH a AS (...), b AS (...) SELECT * FROM a JOIN bMultiple CTEs
WITH RECURSIVE cte AS (...) SELECT * FROM cteRecursive CTE - useful for trees/hierarchies
ROW_NUMBER() OVER (ORDER BY created_at)Row number within result set
RANK() OVER (ORDER BY score DESC)Rank with gaps for ties
DENSE_RANK() OVER (ORDER BY score DESC)Rank without gaps for ties
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)Row number within each partition
SUM(amount) OVER (PARTITION BY user_id)Aggregate per partition without collapsing rows
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)Cumulative sum (running total)
LAG(amount, 1) OVER (ORDER BY date)Value from previous row
LEAD(amount, 1) OVER (ORDER BY date)Value from next row
FIRST_VALUE(name) OVER (...)First value in window frame
NTILE(4) OVER (ORDER BY score)Divide rows into 4 buckets

Modifying Data

INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com')Insert a single row
INSERT INTO users (name) VALUES ('Alice'), ('Bob')Insert multiple rows
INSERT INTO archive SELECT * FROM logs WHERE date < "2024-01-01"Insert from SELECT
UPDATE users SET status = 1 WHERE id = 5Update matching rows
UPDATE users SET age = age + 1Update with expression (no WHERE = all rows!)
DELETE FROM users WHERE id = 5Delete matching rows
DELETE FROM usersDelete all rows (prefer TRUNCATE for full clear)
TRUNCATE TABLE logsDelete all rows fast - resets auto-increment, no rollback
INSERT INTO users (id, name) VALUES (1, "Alice") ON DUPLICATE KEY UPDATE name = "Alice"Upsert (MySQL)
INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.nameUpsert (PostgreSQL)
REPLACE INTO users (id, name) VALUES (1, "Alice")Delete and re-insert on conflict (MySQL)

Schema & DDL

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ciCreate database with charset
DROP DATABASE mydbDrop database
USE mydbSwitch to database
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)Create table
CREATE TABLE IF NOT EXISTS users (...)Create table only if it does not exist
DROP TABLE usersDrop table
DROP TABLE IF EXISTS usersDrop table if it exists
ALTER TABLE users ADD COLUMN age INTAdd column
ALTER TABLE users DROP COLUMN ageRemove column
ALTER TABLE users MODIFY COLUMN name VARCHAR(500)Change column definition
ALTER TABLE users RENAME COLUMN name TO full_nameRename column
RENAME TABLE old_name TO new_nameRename table
CREATE TABLE new AS SELECT * FROM oldCreate table from SELECT result
DESCRIBE usersShow column definitions for a table
SHOW TABLESList tables in current database
SHOW CREATE TABLE usersShow CREATE TABLE statement
SHOW INDEX FROM usersList indexes on a table

Indexes & Constraints

PRIMARY KEY (id)Unique, non-null identifier for each row
UNIQUE (email)All values must be unique (NULL allowed)
NOT NULLColumn cannot contain NULL
DEFAULT 0Value to use when none is supplied
CHECK (age >= 0)Enforce a condition on column values
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADEForeign key with cascade delete
ON DELETE SET NULLSet FK column to NULL when parent is deleted
ON DELETE RESTRICTPrevent deletion if child rows exist
CREATE INDEX idx_email ON users(email)Create standard index
CREATE UNIQUE INDEX idx_email ON users(email)Create unique index
CREATE INDEX idx_name ON users(last_name, first_name)Composite index
DROP INDEX idx_email ON usersRemove index
EXPLAIN SELECT * FROM users WHERE email = "a@b.com"Show query execution plan
ANALYZE TABLE usersUpdate table statistics for optimizer

Transactions

START TRANSACTIONBegin a transaction (also: BEGIN)
COMMITPersist all changes in the transaction
ROLLBACKUndo all changes since START TRANSACTION
SAVEPOINT sp1Set a named savepoint within transaction
ROLLBACK TO SAVEPOINT sp1Roll back to savepoint without full rollback
RELEASE SAVEPOINT sp1Remove a savepoint
SET autocommit = 0Disable auto-commit for session
SET TRANSACTION ISOLATION LEVEL REPEATABLE READSet isolation level: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
SELECT ... FOR UPDATELock selected rows for update within transaction
SELECT ... LOCK IN SHARE MODEShared lock - allows reads, blocks writes

Functions & Expressions

COALESCE(a, b, c)Return first non-NULL argument
NULLIF(a, b)Return NULL if a = b, else return a
IFNULL(expr, alt)Return alt if expr is NULL (MySQL)
CASE WHEN x > 0 THEN "pos" WHEN x < 0 THEN "neg" ELSE "zero" ENDConditional expression
CAST(price AS DECIMAL(10,2))Cast to data type
CONVERT(name USING utf8mb4)Convert character set (MySQL)
CONCAT(first, " ", last)Concatenate strings
CONCAT_WS(", ", city, state)Concatenate with separator, skips NULLs
UPPER(name) / LOWER(name)Change case
TRIM(name) / LTRIM / RTRIMRemove whitespace
LENGTH(name)String length in bytes
CHAR_LENGTH(name)String length in characters (multi-byte safe)
SUBSTRING(str, 1, 3)Extract substring (1-indexed)
REPLACE(str, "old", "new")Replace substring
NOW()Current datetime
CURDATE() / CURTIME()Current date / current time
DATE_FORMAT(dt, "%Y-%m-%d")Format datetime as string
DATE_ADD(dt, INTERVAL 7 DAY)Add interval to date
DATEDIFF(a, b)Difference in days between two dates
YEAR(dt) / MONTH(dt) / DAY(dt)Extract date parts
ROUND(3.567, 2)Round to 2 decimal places
FLOOR(3.9) / CEIL(3.1)Round down / round up
ABS(n)Absolute value
MOD(n, m)Modulo - remainder of n / m