SQL Cheatsheet

Select specific columns from a table

SELECT id, name, email FROM users;

Filter rows with a WHERE condition

SELECT * FROM users WHERE age > 18 AND active = true;

Return unique values only — removes duplicate rows

SELECT DISTINCT country FROM customers;

Paginate results — skip 20 rows, return next 10

SELECT * FROM products LIMIT 20 OFFSET 40;

Sort results by a column (ASC or DESC)

SELECT * FROM posts ORDER BY published_at DESC, title ASC;

Filter rows where column matches any value in a list

SELECT * FROM orders WHERE status IN ('pending', 'shipped');

Filter rows where column value falls within a range (inclusive)

SELECT * FROM products WHERE price BETWEEN 10.00 AND 50.00;

Pattern matching — % is wildcard, _ matches one character

SELECT * FROM users WHERE email LIKE '%@gmail.com';

Check for NULL values — never use = NULL

SELECT * FROM orders WHERE shipped_at IS NULL;

Combine multiple conditions with AND / OR

SELECT * FROM users WHERE email IS NOT NULL AND status = 'active';

Conditional expression — inline if/else for column values

SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM students;

Return first non-NULL value — great for fallback values

SELECT COALESCE(display_name, username, 'Anonymous') AS name FROM users;

Return NULL if col equals the second argument — prevents division by zero

SELECT revenue / NULLIF(expenses, 0) AS ratio FROM financials;

Group rows and filter groups with HAVING (like WHERE but for aggregates)

SELECT category, COUNT(*) AS cnt FROM products GROUP BY category HAVING COUNT(*) > 5;

Return only rows where the join condition matches in both tables

SELECT o.id, u.name FROM orders o INNER JOIN users u ON u.id = o.user_id;

Return all rows from the left table; NULLs where no match in right

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;

Return all rows from the right table; NULLs where no match in left

SELECT u.name, o.id FROM orders o RIGHT JOIN users u ON u.id = o.user_id;

Return all rows from both tables; NULLs where no match on either side

SELECT u.name, o.total FROM users u FULL OUTER JOIN orders o ON o.user_id = u.id;

Cartesian product — every row from table A combined with every row from table B

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

Join a table to itself — useful for hierarchical data like org charts

SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Add extra filter conditions directly in the ON clause (vs WHERE) to control which rows are included before joining

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

Chain multiple JOINs in one query to combine three or more tables

SELECT u.name, p.title, c.body FROM users u JOIN posts p ON p.user_id = u.id JOIN comments c ON c.post_id = p.id;

Count rows: all rows, non-NULL rows, or distinct non-NULL values

SELECT COUNT(*) AS total, COUNT(email) AS with_email, COUNT(DISTINCT country) AS countries FROM users;

Common aggregate functions — sum, average, minimum, maximum

SELECT SUM(total) AS revenue, AVG(total) AS avg_order, MIN(created_at) AS first_order FROM orders;

Group rows by one or more columns — required when using aggregate functions alongside non-aggregate columns

SELECT country, city, COUNT(*) AS users FROM customers GROUP BY country, city;

Filter aggregated groups — used after GROUP BY, like WHERE but for aggregates

SELECT user_id, SUM(amount) AS total FROM payments GROUP BY user_id HAVING SUM(amount) > 1000;

Generate subtotals and a grand total row automatically

SELECT region, product, SUM(sales) FROM sales_data GROUP BY region, product WITH ROLLUP;

Concatenate values from multiple rows into a single string

SELECT user_id, STRING_AGG(tag, ', ') AS tags FROM user_tags GROUP BY user_id;

Use a subquery result as a filter list — IN subquery

SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items WHERE quantity > 10);

EXISTS returns true if the subquery returns any row — often faster than IN for large sets

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');

Return rows where no matching record exists in the subquery

SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

A subquery that references the outer query — runs once per outer row

SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);

A subquery returning a single value used as a column expression

SELECT id, name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;

Use a subquery as an inline derived table in the FROM clause

SELECT dept, avg_sal FROM (SELECT dept_id AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) AS dept_avg WHERE avg_sal > 60000;

Assign a unique sequential integer to each row within a partition

SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank FROM employees;

Like ROW_NUMBER but ties get the same rank, with gaps after (1, 1, 3)

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

Like RANK but no gaps after ties (1, 1, 2)

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;

Access a previous row's value without a self-join — useful for period-over-period comparisons

SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue FROM daily_sales;

Access a next row's value — useful for looking ahead in time series

SELECT date, revenue, LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue FROM daily_sales;

Running aggregate within a partition — cumulative sum, average, etc.

SELECT user_id, date, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY date) AS cumulative FROM payments;

Return the first value in the window frame

SELECT id, FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS first_hire_salary FROM employees;

Divide rows into N equal buckets — useful for quartiles, percentiles

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

Running total from the first row to the current row using explicit frame

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales;

Insert a single new row into a table

INSERT INTO products (name, price, category) VALUES ('Widget', 9.99, 'tools');

Insert rows from a SELECT query — bulk insert from another table

INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2023-01-01';

Update existing rows matching a condition — always include WHERE unless updating all rows

UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'premium';

Delete rows matching a condition — omit WHERE to delete all rows

DELETE FROM sessions WHERE expires_at < NOW();

UPSERT — insert or update on conflict (PostgreSQL). Use ON DUPLICATE KEY UPDATE in MySQL.

INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Remove all rows instantly — faster than DELETE, cannot be rolled back in most DBs

TRUNCATE TABLE temp_import_data;

Create a new table with typed columns, constraints, and defaults

CREATE TABLE posts (id SERIAL PRIMARY KEY, title TEXT NOT NULL, body TEXT, user_id INT REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT NOW());

Add a new column to an existing table

ALTER TABLE products ADD COLUMN discount_pct DECIMAL(5,2) DEFAULT 0;

Remove a column from a table — irreversible, data is lost

ALTER TABLE users DROP COLUMN legacy_token;

Change a column's data type — may require a USING expression for type conversion

ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);

Drop a table only if it exists — IF EXISTS prevents errors in scripts

DROP TABLE IF EXISTS staging_imports;

Rename a column in place without changing data

ALTER TABLE users RENAME COLUMN created TO created_at;

Create an index to speed up queries filtering or sorting on a column

CREATE INDEX idx_orders_user_id ON orders(user_id);

Create an index that also enforces uniqueness on the column(s)

CREATE UNIQUE INDEX idx_products_sku ON products(sku);

Partial index — only indexes rows matching the WHERE condition (smaller and faster)

CREATE INDEX idx_pending_orders ON orders(user_id) WHERE status = 'pending';

Remove an index — use CONCURRENTLY in production to avoid locking

DROP INDEX CONCURRENTLY idx_old_search;

Show the query execution plan with actual timings — essential for query optimization

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

Start a transaction block — changes are not committed until COMMIT

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Persist all changes made in the current transaction

BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
COMMIT;

Undo all changes in the current transaction — use on error

BEGIN;
DELETE FROM users WHERE id = 5;
ROLLBACK; -- nothing was deleted

Create a named checkpoint within a transaction to roll back to partially

BEGIN;
INSERT INTO orders ... ;
SAVEPOINT after_order;
INSERT INTO payments ... ;
ROLLBACK TO SAVEPOINT after_order; -- undo payment, keep order

Control isolation level: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- run queries here
COMMIT;

Page 3 of 20-per-page results — OFFSET = (page - 1) * page_size

SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 40;

Get the top N rows per group — e.g., top 3 products per category

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

Identify duplicate values in a column across rows

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1;

Detect missing IDs or values in a sequence using a generate_series or self-join

SELECT s.id AS missing FROM generate_series(1, 1000) AS s(id) LEFT JOIN orders o ON o.id = s.id WHERE o.id IS NULL;

Transpose rows into columns using conditional aggregation

SELECT user_id,
  SUM(CASE WHEN month = 1 THEN amount END) AS jan,
  SUM(CASE WHEN month = 2 THEN amount END) AS feb
FROM sales GROUP BY user_id;

Recursive CTE — traverse hierarchical data like org trees or category paths

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

Extract JSON values in PostgreSQL — ->> returns text, -> returns JSON

SELECT id, data->>'name' AS name, data->'address'->>'city' AS city FROM profiles;

Truncate a timestamp to a time unit — useful for time-series aggregations

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS signups FROM users GROUP BY 1 ORDER BY 1;