SQL Cheat Sheet

Free SQL cheat sheet: SELECT, WHERE, JOINs, aggregate functions, and data modification commands in one quick reference.

Querying Data

Concept	Syntax	Example
Select all	`SELECT * FROM table`	`SELECT * FROM users;`
Select specific	`SELECT col1, col2 FROM table`	`SELECT name, email FROM users;`
Filter	`WHERE condition`	`SELECT * FROM users WHERE age > 21;`
Sort	`ORDER BY col ASC\|DESC`	`SELECT * FROM products ORDER BY price DESC;`
Limit	`LIMIT n`	`SELECT * FROM users LIMIT 10;`

Joins

Concept	Syntax	Example
Inner join	`JOIN t2 ON t1.id = t2.fk`	`SELECT * FROM orders JOIN users ON orders.user_id = users.id;`
Left join	`LEFT JOIN t2 ON ...`	`SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;`

Aggregation

Concept	Syntax	Example
Count	`COUNT(*)`	`SELECT COUNT(*) FROM users;`
Group	`GROUP BY col`	`SELECT dept, COUNT(*) FROM emp GROUP BY dept;`
Filter groups	`HAVING condition`	`... GROUP BY dept HAVING COUNT(*) > 5;`

Modifying Data

Concept	Syntax	Example
Insert	`INSERT INTO t (cols) VALUES (vals)`	`INSERT INTO users (name) VALUES ('Alice');`
Update	`UPDATE t SET col = val WHERE ...`	`UPDATE users SET name = 'Bob' WHERE id = 1;`
Delete	`DELETE FROM t WHERE ...`	`DELETE FROM users WHERE id = 1;`
Insert multiple rows	`INSERT INTO t (cols) VALUES (...), (...)`	`INSERT INTO users (name) VALUES ('Al'), ('Bo'), ('Cy');`
Insert from select	`INSERT INTO t SELECT ...`	`INSERT INTO archive_users SELECT * FROM users WHERE active = false;`
Upsert	`INSERT ... ON CONFLICT DO UPDATE`	`INSERT INTO users (id, name) VALUES (1, 'Al') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;`
Returning rows	`INSERT/UPDATE/DELETE ... RETURNING cols`	`INSERT INTO users (name) VALUES ('Al') RETURNING id;`
Delete all rows	`TRUNCATE TABLE t`	`TRUNCATE TABLE logs;`

Filtering & Operators

Concept	Syntax	Example
AND / OR / NOT	`WHERE a AND (b OR c)`	`SELECT * FROM users WHERE age > 21 AND (city = 'NYC' OR city = 'LA');`
Range	`WHERE col BETWEEN x AND y`	`SELECT * FROM products WHERE price BETWEEN 10 AND 50;`
List match	`WHERE col IN (...)`	`SELECT * FROM users WHERE country IN ('US', 'CA', 'MX');`
Pattern match	`WHERE col LIKE 'a%'`	`SELECT * FROM users WHERE email LIKE '%@gmail.com';`
Case-insensitive match	`WHERE col ILIKE 'a%'`	`SELECT * FROM users WHERE name ILIKE 'al%';`
Distinct values	`SELECT DISTINCT col`	`SELECT DISTINCT country FROM users;`
Pagination	`LIMIT n OFFSET m`	`SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 20;`

More Joins

Concept	Syntax	Example
Right join	`RIGHT JOIN t2 ON ...`	`SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id;`
Full outer join	`FULL OUTER JOIN t2 ON ...`	`SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;`
Cross join	`CROSS JOIN t2`	`SELECT * FROM sizes CROSS JOIN colors;`
Self join	`JOIN same table with aliases`	`SELECT e.name, m.name AS manager FROM emp e JOIN emp m ON e.manager_id = m.id;`
Multiple joins	`JOIN ... JOIN ...`	`SELECT * FROM orders o JOIN users u ON o.user_id = u.id JOIN items i ON o.item_id = i.id;`

Aggregation (more)

Concept	Syntax	Example
Sum	`SUM(col)`	`SELECT SUM(total) FROM orders;`
Average	`AVG(col)`	`SELECT AVG(price) FROM products;`
Min / Max	`MIN(col), MAX(col)`	`SELECT MIN(price), MAX(price) FROM products;`
Count distinct	`COUNT(DISTINCT col)`	`SELECT COUNT(DISTINCT country) FROM users;`
Group by multiple	`GROUP BY a, b`	`SELECT country, city, COUNT(*) FROM users GROUP BY country, city;`
String aggregate	`STRING_AGG(col, sep)`	`SELECT dept, STRING_AGG(name, ', ') FROM emp GROUP BY dept;`

Subqueries & CTEs

Concept	Syntax	Example
Scalar subquery	`WHERE col > (SELECT ...)`	`SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);`
IN subquery	`WHERE col IN (SELECT ...)`	`SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);`
EXISTS	`WHERE EXISTS (SELECT ...)`	`SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);`
CTE	`WITH name AS (SELECT ...)`	`WITH big AS (SELECT * FROM orders WHERE total > 100) SELECT * FROM big;`
Recursive CTE	`WITH RECURSIVE name AS (...)`	`WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM nums WHERE n < 5) SELECT * FROM nums;`

Set Operations

Concept	Syntax	Example
Union	`q1 UNION q2`	`SELECT name FROM a UNION SELECT name FROM b;`
Union all	`q1 UNION ALL q2`	`SELECT id FROM a UNION ALL SELECT id FROM b;`
Intersect	`q1 INTERSECT q2`	`SELECT id FROM a INTERSECT SELECT id FROM b;`
Except	`q1 EXCEPT q2`	`SELECT id FROM all_users EXCEPT SELECT id FROM banned;`

Window Functions

Concept	Syntax	Example
Row number	`ROW_NUMBER() OVER (ORDER BY col)`	`SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM players;`
Rank	`RANK() OVER (...)`	`SELECT name, RANK() OVER (ORDER BY score DESC) FROM players;`
Dense rank	`DENSE_RANK() OVER (...)`	`SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) FROM players;`
Partition	`OVER (PARTITION BY col ORDER BY col)`	`SELECT dept, name, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) FROM emp;`
Lag / Lead	`LAG(col) / LEAD(col) OVER (...)`	`SELECT day, sales, LAG(sales) OVER (ORDER BY day) AS prev FROM daily;`
Running total	`SUM(col) OVER (ORDER BY col)`	`SELECT day, SUM(sales) OVER (ORDER BY day) AS running FROM daily;`

CASE & NULL Handling

Concept	Syntax	Example
CASE expression	`CASE WHEN cond THEN x ELSE y END`	`SELECT name, CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS group FROM users;`
Coalesce	`COALESCE(a, b, ...)`	`SELECT COALESCE(nickname, name, 'Anon') FROM users;`
Null check	`WHERE col IS NULL / IS NOT NULL`	`SELECT * FROM users WHERE deleted_at IS NULL;`
Nullif	`NULLIF(a, b)`	`SELECT NULLIF(total, 0) FROM orders;`

String Functions

Concept	Syntax	Example
Concatenate	`a \|\| b or CONCAT(a, b)`	`SELECT first_name \|\| ' ' \|\| last_name AS full FROM users;`
Length	`LENGTH(str)`	`SELECT LENGTH(name) FROM users;`
Upper / Lower	`UPPER(str), LOWER(str)`	`SELECT UPPER(name) FROM users;`
Substring	`SUBSTRING(str FROM x FOR n)`	`SELECT SUBSTRING(phone FROM 1 FOR 3) FROM users;`
Replace	`REPLACE(str, from, to)`	`SELECT REPLACE(email, '@old.com', '@new.com') FROM users;`
Trim	`TRIM(str)`	`SELECT TRIM(' hi ');`

Date & Time

Concept	Syntax	Example
Current timestamp	`NOW() / CURRENT_DATE`	`SELECT NOW(), CURRENT_DATE;`
Extract part	`EXTRACT(part FROM ts)`	`SELECT EXTRACT(YEAR FROM created_at) FROM orders;`
Truncate date	`DATE_TRUNC('unit', ts)`	`SELECT DATE_TRUNC('month', created_at) FROM orders;`
Date math	`ts + INTERVAL 'n unit'`	`SELECT NOW() - INTERVAL '7 days';`
Age between dates	`AGE(a, b)`	`SELECT AGE(NOW(), birth_date) FROM users;`

Tables & Schema

Concept	Syntax	Example
Create table	`CREATE TABLE t (col type, ...)`	`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);`
Add column	`ALTER TABLE t ADD COLUMN col type`	`ALTER TABLE users ADD COLUMN age INT DEFAULT 0;`
Drop column	`ALTER TABLE t DROP COLUMN col`	`ALTER TABLE users DROP COLUMN age;`
Rename table	`ALTER TABLE t RENAME TO new`	`ALTER TABLE users RENAME TO members;`
Drop table	`DROP TABLE [IF EXISTS] t`	`DROP TABLE IF EXISTS temp_data;`
Foreign key	`REFERENCES other(col)`	`CREATE TABLE orders (id SERIAL, user_id INT REFERENCES users(id));`
Create index	`CREATE INDEX name ON t (col)`	`CREATE INDEX idx_email ON users (email);`

Data Types

Concept	Syntax	Example
Integer	`INT / BIGINT / SERIAL`	`id SERIAL PRIMARY KEY`
Decimal	`NUMERIC(p, s) / DECIMAL`	`price NUMERIC(10, 2)`
Text	`VARCHAR(n) / TEXT`	`name VARCHAR(100)`
Boolean	`BOOLEAN`	`active BOOLEAN DEFAULT true`
Date / Time	`DATE / TIMESTAMP / TIMESTAMPTZ`	`created_at TIMESTAMPTZ DEFAULT NOW()`
JSON	`JSON / JSONB`	`metadata JSONB`

Views & Transactions

Concept	Syntax	Example
Create view	`CREATE VIEW name AS SELECT ...`	`CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;`
Drop view	`DROP VIEW name`	`DROP VIEW active_users;`
Begin transaction	`BEGIN`	`BEGIN;`
Commit	`COMMIT`	`UPDATE accounts SET bal = bal - 100 WHERE id = 1; COMMIT;`
Rollback	`ROLLBACK`	`ROLLBACK;`
Savepoint	`SAVEPOINT name`	`SAVEPOINT before_update;`