SQL Cheatsheet
Query Execution Order (conceptual)
FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET
This is why you can’t reference a SELECT alias in WHERE, but you often can in ORDER BY — WHERE is evaluated before SELECT builds the output columns.
Basic Queries
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;
SELECT DISTINCT category FROM orders;
SELECT column1 AS alias1 FROM table_name;
SELECT * FROM orders
WHERE status = 'completed'
AND amount > 100
AND created_at >= '2026-01-01';
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;
SELECT * FROM orders WHERE customer_email LIKE '%@gmail.com';
SELECT * FROM orders WHERE notes IS NULL;
SELECT * FROM orders WHERE notes IS NOT NULL;
SELECT * FROM orders ORDER BY created_at DESC, amount ASC;
SELECT * FROM orders LIMIT 10 OFFSET 20; -- pagination
Joins
-- INNER JOIN: only matching rows in both tables
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- LEFT JOIN: all rows from left, matched rows from right (NULLs if no match)
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;
-- RIGHT JOIN: all rows from right, matched rows from left
SELECT o.id, c.name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;
-- FULL OUTER JOIN: all rows from both sides
SELECT o.id, c.name
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.id;
-- CROSS JOIN: cartesian product (every row x every row)
SELECT * FROM sizes CROSS JOIN colors;
-- SELF JOIN: table joined to itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Filtering for "no match" (anti-join pattern)
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL; -- customers with no orders
Aggregation
SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders;
SELECT category, COUNT(*) AS n, SUM(amount) AS total
FROM orders
GROUP BY category;
SELECT category, COUNT(*) AS n
FROM orders
GROUP BY category
HAVING COUNT(*) > 10; -- filter on aggregated result (WHERE can't do this)
SELECT COUNT(DISTINCT customer_id) FROM orders;
-- Multiple grouping levels
SELECT category, sub_category, SUM(amount)
FROM orders
GROUP BY category, sub_category;
-- ROLLUP / CUBE (subtotals + grand total)
SELECT category, sub_category, SUM(amount)
FROM orders
GROUP BY ROLLUP(category, sub_category);
Subqueries & CTEs
-- Subquery in WHERE
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'DE');
-- Correlated subquery
SELECT * FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id);
-- Subquery in FROM
SELECT category, avg_amount
FROM (
SELECT category, AVG(amount) AS avg_amount
FROM orders
GROUP BY category
) t
WHERE avg_amount > 100;
-- Common Table Expression (CTE) — same result, more readable
WITH category_avg AS (
SELECT category, AVG(amount) AS avg_amount
FROM orders
GROUP BY category
)
SELECT * FROM category_avg WHERE avg_amount > 100;
-- Multiple CTEs, chained
WITH filtered AS (
SELECT * FROM orders WHERE status = 'completed'
),
by_customer AS (
SELECT customer_id, SUM(amount) AS total
FROM filtered
GROUP BY customer_id
)
SELECT * FROM by_customer WHERE total > 1000;
-- Recursive CTE (e.g., org hierarchy traversal)
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL -- anchor: top of hierarchy
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;
Window Functions
-- Ranking
SELECT id, category, amount,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rn,
RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rnk, -- ties get same rank, gaps after
DENSE_RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS dense_rnk -- ties get same rank, no gaps
FROM orders;
-- Running totals / moving aggregates
SELECT id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total,
AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM orders;
-- Lag / Lead
SELECT id, order_date, amount,
LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders;
-- First/last value in a window
SELECT category, amount,
FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY order_date) AS first_order_amount,
LAST_VALUE(amount) OVER (PARTITION BY category ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_amount
FROM orders;
-- NTILE — split into N buckets
SELECT id, amount, NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;
Window frame clauses: ROWS BETWEEN n PRECEDING AND CURRENT ROW, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (whole partition).
Set Operations
SELECT id FROM table_a
UNION -- combines + removes duplicates
SELECT id FROM table_b;
SELECT id FROM table_a
UNION ALL -- combines, keeps duplicates (faster, no dedup step)
SELECT id FROM table_b;
SELECT id FROM table_a
INTERSECT -- rows in both
SELECT id FROM table_b;
SELECT id FROM table_a
EXCEPT -- rows in A but not in B (MINUS in Oracle)
SELECT id FROM table_b;
Conditional Logic
SELECT
id,
CASE
WHEN amount > 1000 THEN 'high'
WHEN amount > 100 THEN 'medium'
ELSE 'low'
END AS amount_tier
FROM orders;
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name FROM users;
SELECT NULLIF(amount, 0) AS amount_or_null FROM orders; -- returns NULL if amount = 0
SELECT IFNULL(amount, 0) FROM orders; -- MySQL/BigQuery shorthand for COALESCE(x, default)
String Functions
SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT first_name || ' ' || last_name FROM users; -- ANSI/Postgres concatenation operator
SELECT UPPER(name), LOWER(name), TRIM(name) FROM users;
SELECT LENGTH(name) FROM users;
SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;
SELECT REPLACE(name, 'old', 'new') FROM users;
SELECT SPLIT_PART(email, '@', 2) FROM users; -- Postgres/Snowflake
SELECT name LIKE '%smith%' FROM users; -- case-sensitive pattern match
SELECT name ILIKE '%smith%' FROM users; -- case-insensitive (Postgres)
SELECT REGEXP_REPLACE(name, '[0-9]+', '') FROM users;
Date/Time Functions
SELECT CURRENT_DATE, CURRENT_TIMESTAMP;
SELECT DATE_TRUNC('month', order_date) FROM orders; -- Postgres/Snowflake
SELECT EXTRACT(YEAR FROM order_date) FROM orders;
SELECT order_date + INTERVAL '7 days' FROM orders;
SELECT DATEDIFF(day, start_date, end_date) FROM orders; -- Snowflake/SQL Server style
SELECT DATE_ADD(order_date, INTERVAL 1 MONTH) FROM orders; -- MySQL
SELECT TO_CHAR(order_date, 'YYYY-MM') FROM orders; -- Postgres/Oracle formatting
SELECT FORMAT_DATE('%Y-%m', order_date) FROM orders; -- BigQuery
Data Definition (DDL)
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10, 2) DEFAULT 0,
status VARCHAR(20) CHECK (status IN ('pending', 'completed', 'cancelled')),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders DROP COLUMN notes;
ALTER TABLE orders RENAME COLUMN amount TO total_amount;
ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_orders_ref ON orders(reference_number);
DROP TABLE orders;
TRUNCATE TABLE orders; -- fast full delete, resets identity, minimal logging
Data Manipulation (DML)
INSERT INTO orders (customer_id, amount, status) VALUES (1, 99.99, 'pending');
INSERT INTO orders (customer_id, amount) SELECT customer_id, amount FROM staging_orders;
UPDATE orders SET status = 'completed' WHERE id = 5;
UPDATE orders SET amount = amount * 1.1 WHERE category = 'premium';
DELETE FROM orders WHERE status = 'cancelled';
-- Upsert (dialect-specific)
INSERT INTO orders (id, amount) VALUES (1, 100)
ON CONFLICT (id) DO UPDATE SET amount = EXCLUDED.amount; -- Postgres
MERGE INTO orders t USING staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount); -- ANSI standard, most warehouses
Views & Materialized Views
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY 1;
REFRESH MATERIALIZED VIEW monthly_sales; -- must be manually/scheduled refreshed
Transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK; on error
-- Savepoints for partial rollback
BEGIN;
SAVEPOINT before_risky_op;
...
ROLLBACK TO SAVEPOINT before_risky_op;
COMMIT;
Indexes & Performance
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5; -- Postgres: actual execution stats
-- Index types
CREATE INDEX idx_btree ON orders(customer_id); -- default, good for equality & range
CREATE INDEX idx_partial ON orders(customer_id) WHERE status = 'pending'; -- partial index
CREATE INDEX idx_composite ON orders(customer_id, order_date); -- composite — order matters for usage
General tips:
- Index columns used in
WHERE,JOIN, andORDER BY— but every index adds write overhead, so don’t over-index. - Composite index column order matters: an index on
(a, b)helps queries filtering onaalone ora AND b, but notbalone. - Avoid functions on indexed columns in
WHERE(WHERE YEAR(date) = 2026) — it prevents index usage; rewrite as a range (WHERE date >= '2026-01-01' AND date < '2027-01-01'). SELECT *fetches unnecessary columns — select only what’s needed, especially in wide tables.- Prefer
EXISTSoverINwith large subqueries in many engines — the optimizer can short-circuit as soon as one match is found.
-- EXISTS vs IN
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
Common Gotchas
NULLis never equal to anything, including itself — useIS NULL/IS NOT NULL, never= NULL.COUNT(column)skips nulls;COUNT(*)counts all rows.- Integer division truncates in many engines (
5 / 2 = 2) — cast one operand to a decimal/float to avoid this. GROUP BYrequires every non-aggregatedSELECTcolumn to appear in theGROUP BYclause (strict in Postgres/most engines; MySQL historically more lenient).HAVINGfilters after aggregation;WHEREfilters before — usingWHEREto filter an aggregate value is a common mistake.- Be careful with
UNIONvsUNION ALLperformance —UNIONalways dedups, which requires a sort/hash step.
SQL Partitioning and Sharding Strategies
For hyper-scale databases holding hundreds of millions of records, partitioning split single massive tables into smaller, manageable logical chunks.
1. Range Partitioning
Splits records based on a continuous range of values, typically dates or sequenced IDs.
CREATE TABLE sales (
id INT,
order_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);
-- Define partition tables (Postgres syntax)
CREATE TABLE sales_2025 PARTITION OF sales
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE sales_2026 PARTITION OF sales
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
2. List Partitioning
Splits records explicitly based on a set of categorical string/enum values.
CREATE TABLE customers (
id INT,
name VARCHAR(100),
region VARCHAR(50)
) PARTITION BY LIST (region);
CREATE TABLE customers_americas PARTITION OF customers
FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE customers_europe PARTITION OF customers
FOR VALUES IN ('UK', 'DE', 'FR', 'IT');
3. Hash Partitioning
Distributes records evenly using a modulus and remainder hashing strategy over a primary key. Good for preventing database “hotspots” in high-concurrency systems.
CREATE TABLE web_logs (
log_id INT,
url VARCHAR(255),
ip_address VARCHAR(45)
) PARTITION BY HASH (log_id);
CREATE TABLE web_logs_part1 PARTITION OF web_logs
FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE web_logs_part2 PARTITION OF web_logs
FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE web_logs_part3 PARTITION OF web_logs
FOR VALUES WITH (MODULUS 3, REMAINDER 2);