The SQL cheatsheet and SQL reference guide for developers and data practitioners. Covers SQL tutorial topics from beginner SELECT queries to advanced SQL joins, window functions, CTEs, and query optimization — with real SQL examples for PostgreSQL and MySQL. Useful as a daily SQL reference and as SQL interview questions prep.
I kept having to look the same things up — window function syntax, which JOIN does what, how NULL behaves in aggregations. So I wrote them all down in one place. This is the SQL reference I wish existed when I was learning, and the one I still open when my brain blanks on DENSE_RANK vs RANK at 11pm.
Every SQL example here runs against real-looking data. No SELECT foo FROM bar.
| # | Section | What's in it |
|---|---|---|
| 1 | 🔰 SQL Basics | SELECT, WHERE, ORDER BY, LIMIT, aliases |
| 2 | 📊 Filtering & Aggregation | GROUP BY, HAVING, COUNT/SUM/AVG, DISTINCT |
| 3 | 🔗 SQL JOINs | INNER, LEFT, RIGHT, FULL OUTER, SELF, multi-table |
| 4 | 🪆 Subqueries & CTEs | Correlated, WITH, recursive, EXISTS vs IN |
| 5 | 🪟 SQL Window Functions | ROW_NUMBER, RANK, LAG/LEAD, running totals |
| 6 | 🔤 String Functions | CONCAT, SUBSTRING, TRIM, REPLACE, LIKE |
| 7 | 📅 Date & Time | Arithmetic, formatting, common date ranges |
| 8 | ⬜ NULL Handling | COALESCE, NULLIF, NULL traps in JOINs and aggs |
| 9 | ⚡ Query Optimization & Performance | Indexes, EXPLAIN, slow query patterns, N+1 |
| 10 | 🗂 Schema Design | Normalization, data types, keys, naming |
| 11 | 📋 SQL Quick Reference Card | 30 keywords, one line each |
Pull every column:
SELECT * FROM employees;Pull specific columns:
SELECT first_name, last_name, salary
FROM employees;Computed columns:
SELECT
first_name,
last_name,
salary * 12 AS annual_salary
FROM employees;-- Equality and inequality
SELECT * FROM orders WHERE status = 'shipped';
SELECT * FROM orders WHERE status != 'cancelled';
-- Comparison
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE stock <= 0;
-- Range (inclusive on both ends)
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- List membership
SELECT * FROM employees
WHERE department IN ('Engineering', 'Design', 'Product');
-- Negated list
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Legal');
-- Pattern matching
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE sku LIKE 'WIDGET-%';
SELECT * FROM customers WHERE name LIKE '_ohn%'; -- one wildcard char, then 'ohn...'
-- NULL checks — never use = NULL, always IS NULL
SELECT * FROM employees WHERE manager_id IS NULL; -- top-level managers
SELECT * FROM employees WHERE manager_id IS NOT NULL; -- everyone elseLIKE wildcards:
%— zero or more characters_— exactly one character
-- Single column, ascending (default)
SELECT * FROM products ORDER BY price;
-- Descending
SELECT * FROM products ORDER BY price DESC;
-- Multiple columns: primary sort, then tiebreaker
SELECT * FROM employees
ORDER BY department ASC, salary DESC;
-- Sort by column position (works but hurts readability)
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC; -- 3 = salary-- First 10 rows
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;
-- Rows 11–20 (page 2 if page size = 10)
SELECT * FROM products ORDER BY created_at DESC LIMIT 10 OFFSET 10;
-- PostgreSQL alternative syntax
SELECT * FROM products
ORDER BY created_at DESC
FETCH FIRST 10 ROWS ONLY;Always pair LIMIT with ORDER BY. Without ORDER BY, the database returns rows in whatever order it feels like — which changes between runs.
-- Column alias
SELECT
first_name || ' ' || last_name AS full_name,
salary * 12 AS annual_salary
FROM employees;
-- Table alias (required when joining the same table twice)
SELECT e.first_name, m.first_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.id;
-- AS keyword is optional — both work
SELECT salary * 12 annual_salary FROM employees;
SELECT salary * 12 AS annual_salary FROM employees;-- Sales by department
SELECT
department,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary,
SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;Every column in SELECT that isn't inside an aggregate function must appear in GROUP BY. This is the rule that trips people up constantly.
-- This fails: first_name is not in GROUP BY and not aggregated
SELECT department, first_name, COUNT(*)
FROM employees
GROUP BY department; -- ERROR
-- This works
SELECT department, COUNT(*)
FROM employees
GROUP BY department;WHERE filters rows before grouping. HAVING filters groups after aggregation.
-- Departments with more than 10 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
-- Can't use WHERE here:
-- WHERE COUNT(*) > 10 -- ERROR: can't use aggregate in WHERE
-- Both together: filter rows first, then filter groups
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active' -- filter out inactive employees first
GROUP BY department
HAVING AVG(salary) > 80000; -- then filter groupsSELECT
COUNT(*) AS total_rows,
COUNT(manager_id) AS rows_with_manager, -- NULLs excluded
COUNT(DISTINCT department) AS unique_departments,
SUM(salary) AS total_payroll,
AVG(salary) AS avg_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees;COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. This difference matters.
-- Unique values in a column
SELECT DISTINCT department FROM employees;
-- Unique combinations
SELECT DISTINCT department, job_title FROM employees;
-- With COUNT
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE created_at >= '2024-01-01';SELECT
c.name AS customer,
COUNT(o.id) AS order_count,
SUM(o.total) AS revenue,
AVG(o.total) AS avg_order_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
AND o.created_at >= '2024-01-01'
GROUP BY c.id, c.name
HAVING COUNT(o.id) >= 3 -- at least 3 orders
ORDER BY revenue DESC
LIMIT 5;SQL joins are the most important concept to get right. A wrong JOIN either drops rows you needed or multiplies rows you didn't expect. This SQL cheatsheet section covers every JOIN type with real examples — a common topic in SQL interview questions.
Table A Table B
+----+ +----+
| 1 | | 1 |
| 2 | | 3 |
| 3 | | 4 |
+----+ +----+
INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN
A ∩ B All A + match B Match A + All B A ∪ B
+----+ +----+ +----+ +----+
| 1 | | 1 | | 1 | | 1 |
| 3 | | 2 |NULL | 3 | | 2 |NULL
+----+ | 3 | | 4 |NULL | 3 |
+----+ +----+ | 4 |NULL
+----+
SELECT
o.id AS order_id,
c.name AS customer,
o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;
-- or just: JOIN customers c ON ...Rows from orders with no matching customer are dropped. Rows from customers with no orders are dropped. Use INNER JOIN when you only care about rows that exist in both tables.
-- Every customer, even those with no orders
SELECT
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;A customer with no orders will have order_count = 0 and revenue = 0 rather than disappearing from the result. This is one of the most used JOINs in reporting.
Finding rows that don't match (anti-join pattern):
-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;This works because unmatched LEFT JOIN rows have NULL in all right-table columns.
RIGHT JOIN is just LEFT JOIN with the tables flipped. Most people rewrite it as LEFT JOIN for readability.
-- Equivalent queries:
SELECT c.name, o.total
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;
-- Same result, cleaner to read:
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;-- All employees and all departments, matched where possible
SELECT
e.name AS employee,
d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.department_id;Unmatched rows on either side get NULLs on the other side's columns. MySQL doesn't support FULL OUTER JOIN — simulate it with LEFT JOIN UNION RIGHT JOIN.
-- Employee and their manager's name (same table, twice)
SELECT
e.first_name AS employee,
COALESCE(m.first_name, 'No manager') AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;SELECT
o.id AS order_id,
c.name AS customer,
p.name AS product,
oi.quantity,
oi.unit_price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped'
ORDER BY o.id, p.name;1. Joining on the wrong column
-- Wrong: joins on name instead of id
JOIN departments d ON d.name = e.department_name
-- Right: join on stable primary/foreign keys
JOIN departments d ON d.id = e.department_id2. Forgetting that INNER JOIN drops unmatched rows
-- If some orders have no customer (bad data), this drops those orders
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- If you want to see all orders regardless:
LEFT JOIN customers c ON c.id = o.customer_id3. Row multiplication from one-to-many
-- orders has 100 rows; each order has ~3 items in order_items
-- Result: ~300 rows, not 100
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id;
-- This is correct behavior, but surprises people who forget about it4. Filtering on a LEFT JOIN's right-table column in WHERE
-- This accidentally turns a LEFT JOIN into an INNER JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'; -- eliminates rows where o.status IS NULL
-- Fix: move the filter into the JOIN condition
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'completed'Runs once, result is used by the outer query:
-- Products priced above average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Orders placed by customers in California
SELECT *
FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE state = 'CA'
);References the outer query — runs once per row. Slow on large tables:
-- Each employee's salary vs their department average
SELECT
first_name,
salary,
(
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department -- references outer query
) AS dept_avg
FROM employees e1;This works but hits the subquery N times (once per employee). Rewrite with a window function or CTE when performance matters.
CTEs give a subquery a name. They don't run faster by default, but they make complex queries readable.
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
),
ranked_months AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue
)
SELECT
month,
revenue,
revenue - prev_revenue AS change,
ROUND(
(revenue - prev_revenue) / prev_revenue * 100, 1
) AS pct_change
FROM ranked_months
ORDER BY month;Use CTEs when:
- A subquery appears more than once
- A subquery is nested more than two levels deep
- You want to name intermediate steps for clarity
Walk a tree or hierarchy:
-- All subordinates of employee id=1, any depth
WITH RECURSIVE subordinates AS (
-- Base case: the starting employee
SELECT id, first_name, manager_id, 0 AS depth
FROM employees
WHERE id = 1
UNION ALL
-- Recursive case: employees who report to someone already in the set
SELECT e.id, e.first_name, e.manager_id, s.depth + 1
FROM employees e
JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates ORDER BY depth, first_name;Add a depth limit (WHERE depth < 10) if your data could have cycles.
-- IN: check if value is in a list
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
-- EXISTS: check if at least one matching row exists (stops at first match)
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.total > 1000
);EXISTS short-circuits — it stops as soon as it finds one match. For large subqueries, EXISTS is usually faster. IN with a large list (10,000+ values) can be slow. NOT IN with NULLs in the subquery returns no rows — always use NOT EXISTS instead.
-- This returns nothing if ANY order has a NULL customer_id:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders); -- dangerous
-- This is correct:
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);SQL window functions operate on a set of rows related to the current row, without collapsing them into one row the way GROUP BY does. Mastering window functions — ROW_NUMBER, RANK, LAG, LEAD, running totals — pays off quickly in both SQL interviews and real-world query optimization.
SELECT
employee,
department,
salary,
-- These all work on the full result set or a defined partition
ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;-- Salary ROW_NUMBER RANK DENSE_RANK
-- 90000 1 1 1
-- 80000 2 2 2
-- 80000 3 2 2 <- tie: RANK skips 3, DENSE_RANK doesn't
-- 70000 4 4 3
SELECT
first_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;ROW_NUMBER— unique, no tiesRANK— ties get the same number, next rank is skippedDENSE_RANK— ties get the same number, next rank is not skipped
-- Top 3 earners per department
WITH ranked AS (
SELECT
first_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;This pattern comes up constantly in reporting.
Look at the previous or next row's value:
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_sales
ORDER BY month;The second argument is the offset (default 1). Third argument is the default if there's no previous/next row.
LAG(revenue, 1, 0) OVER (ORDER BY month) -- returns 0 if no previous rowSELECT
first_name,
department,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department ORDER BY salary DESC
) AS dept_max_salary
FROM employees;LAST_VALUE requires an explicit frame because the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — it doesn't look ahead:
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_min_salarySELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;Frame options:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW— from the start to current row (running total)ROWS BETWEEN 6 PRECEDING AND CURRENT ROW— last 7 rows (7-day moving average)ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING— entire partition
-- CONCAT (works everywhere)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- || operator (PostgreSQL, SQLite)
SELECT first_name || ' ' || last_name AS full_name FROM employees;
-- CONCAT_WS: concatenate with separator, skips NULLs
SELECT CONCAT_WS(', ', city, state, country) AS location FROM addresses;-- SUBSTRING(string, start_position, length)
-- Positions are 1-indexed
SELECT SUBSTRING('Hello World', 1, 5); -- 'Hello'
SELECT SUBSTRING('Hello World', 7); -- 'World' (to end)
SELECT SUBSTRING(phone, 1, 3) AS area_code FROM customers;-- Remove whitespace
SELECT TRIM(' hello world '); -- 'hello world'
SELECT LTRIM(' hello'); -- 'hello'
SELECT RTRIM('hello '); -- 'hello'
-- Remove specific characters
SELECT TRIM(BOTH ',' FROM ',,,hello,,,'); -- 'hello' (PostgreSQL)SELECT UPPER(email) FROM customers;
SELECT LOWER(email) FROM customers;
-- Case-insensitive search without an index:
SELECT * FROM customers WHERE LOWER(email) = LOWER('Test@Example.com');
-- Better: store emails lowercased, then use a normal = comparisonSELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
-- Scrub phone number formatting
SELECT REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '') AS clean_phone
FROM customers;SELECT LENGTH('hello'); -- 5 (bytes in MySQL, chars in PostgreSQL)
SELECT CHAR_LENGTH('hello'); -- 5 (always characters)
-- Difference only matters for multibyte character sets (emoji, CJK, etc.)
-- Find suspiciously short or long values
SELECT * FROM products WHERE CHAR_LENGTH(description) < 10;-- Ends with domain
WHERE email LIKE '%@company.com'
-- Starts with prefix
WHERE sku LIKE 'WIDGET-%'
-- Contains substring
WHERE description LIKE '%refund%'
-- Fixed-length pattern: 3 chars, dash, 4 chars (e.g., phone area code)
WHERE code LIKE '___-____'
-- Escape a literal % or _
WHERE description LIKE '100\% organic' ESCAPE '\'For case-insensitive LIKE in PostgreSQL, use ILIKE:
WHERE name ILIKE '%john%'Standard SQL has no split function. Workarounds:
-- PostgreSQL: split_part
SELECT split_part('2024-01-15', '-', 1) AS year; -- '2024'
SELECT split_part('2024-01-15', '-', 2) AS month; -- '01'
-- MySQL: SUBSTRING_INDEX
SELECT SUBSTRING_INDEX('2024-01-15', '-', 1) AS year; -- '2024'
SELECT SUBSTRING_INDEX('2024-01-15', '-', -1) AS day; -- '15'
-- Extract before a delimiter (portable)
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM customers;Date functions are the most dialect-specific part of SQL. The logic is the same; the function names differ.
| What | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| Current date | CURRENT_DATE |
CURDATE() |
DATE('now') |
| Current time | CURRENT_TIME |
CURTIME() |
TIME('now') |
| Current datetime | NOW() |
NOW() |
DATETIME('now') |
| Current timestamp | CURRENT_TIMESTAMP |
NOW() |
DATETIME('now') |
-- PostgreSQL
SELECT NOW() + INTERVAL '30 days';
SELECT NOW() - INTERVAL '1 year';
SELECT created_at + INTERVAL '7 days' AS expires_at FROM trials;
-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365
-- SQLite
SELECT DATE('now', '+30 days');
SELECT DATE('now', '-1 year');
SELECT JULIANDAY('2024-12-31') - JULIANDAY('2024-01-01'); -- 365.0-- PostgreSQL / standard SQL
SELECT
EXTRACT(YEAR FROM created_at) AS year,
EXTRACT(MONTH FROM created_at) AS month,
EXTRACT(DOW FROM created_at) AS day_of_week -- 0=Sunday
FROM orders;
-- DATE_TRUNC: truncate to start of period
SELECT DATE_TRUNC('month', created_at) AS month_start FROM orders;
SELECT DATE_TRUNC('week', created_at) AS week_start FROM orders;
-- MySQL
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM orders;-- PostgreSQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') AS date;
SELECT TO_CHAR(created_at, 'Month DD, YYYY') AS pretty_date;
SELECT TO_CHAR(created_at, 'HH24:MI:SS') AS time;
-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date;
SELECT DATE_FORMAT(created_at, '%M %d, %Y') AS pretty_date;-- Last 30 days
WHERE created_at >= NOW() - INTERVAL '30 days'
-- This calendar month (PostgreSQL)
WHERE created_at >= DATE_TRUNC('month', NOW())
AND created_at < DATE_TRUNC('month', NOW()) + INTERVAL '1 month'
-- This calendar month (MySQL)
WHERE YEAR(created_at) = YEAR(NOW())
AND MONTH(created_at) = MONTH(NOW())
-- Year to date
WHERE created_at >= DATE_TRUNC('year', NOW()) -- PostgreSQL
WHERE YEAR(created_at) = YEAR(NOW()) -- MySQL
-- Yesterday
WHERE DATE(created_at) = CURRENT_DATE - INTERVAL '1 day' -- PostgreSQL
WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) -- MySQL
-- Specific quarter
WHERE created_at >= '2024-07-01' AND created_at < '2024-10-01' -- Q3 2024Always use half-open intervals for date ranges (>= start, < end). This avoids double-counting rows at midnight and works correctly regardless of whether the column is DATE or TIMESTAMP.
NULL means "unknown" or "absent". It is not zero, not an empty string, and not false. This causes real bugs if you forget it.
-- Always use IS NULL, never = NULL
SELECT * FROM employees WHERE manager_id IS NULL; -- works
SELECT * FROM employees WHERE manager_id = NULL; -- always returns no rows
-- Combined condition
SELECT * FROM products
WHERE discontinued_at IS NULL OR discontinued_at > NOW();-- Use a fallback value
SELECT COALESCE(phone, mobile, 'No contact') AS contact FROM customers;
-- Replace NULL with 0 in math
SELECT SUM(COALESCE(discount, 0)) FROM orders;
-- Full name with optional middle name
SELECT COALESCE(first_name || ' ' || middle_name || ' ' || last_name,
first_name || ' ' || last_name) AS full_name
FROM employees;-- Avoid division by zero
SELECT total / NULLIF(quantity, 0) AS unit_price FROM order_items;
-- Returns NULL instead of crashing when quantity = 0
-- Treat empty string as NULL
SELECT NULLIF(TRIM(phone), '') AS clean_phone FROM customers;IFNULL(value, fallback) -- MySQL, SQLite
ISNULL(value, fallback) -- SQL Server
NVL(value, fallback) -- Oracle
COALESCE(value, fallback) -- Standard SQL, works everywhereUse COALESCE when you care about portability.
Aggregate functions ignore NULLs. This changes your results silently:
-- employees: 5 rows, salaries: [50000, 60000, NULL, 70000, NULL]
SELECT
COUNT(*) AS total_rows, -- 5
COUNT(salary) AS non_null, -- 3 (NULLs excluded)
SUM(salary) AS total, -- 180000 (NULLs treated as 0 in SUM)
AVG(salary) AS average -- 60000 (180000 / 3, not / 5)
FROM employees;
-- AVG is 60000, not 36000. The NULLs are excluded from both numerator AND denominator.If you want NULLs counted as 0 in the average:
SELECT AVG(COALESCE(salary, 0)) FROM employees; -- 36000Neither is wrong — they answer different questions. Know which question you're asking.
-- If customer_id is NULL in orders, the row won't match anything
-- and will be dropped from INNER JOIN results
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- orders with NULL customer_id disappear silently
-- Check for this:
SELECT COUNT(*) FROM orders WHERE customer_id IS NULL;Also: NOT IN with NULLs returns zero rows (because x NOT IN (1, NULL) is always UNKNOWN):
-- If orders has a single row with NULL customer_id, this returns nothing:
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- Use NOT EXISTS instead:
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Query optimization is what separates a good SQL practitioner from a great one. This section covers indexes, EXPLAIN, and the slow query patterns that appear most in PostgreSQL and MySQL production systems — and frequently in SQL interview questions.
An index is a separate data structure (usually a B-tree) that the database maintains alongside your table. It stores column values in sorted order with pointers back to the actual rows.
Without an index, a WHERE customer_id = 42 on a million-row table reads every row — a full table scan, O(n).
With an index on customer_id, the database binary-searches the B-tree — O(log n). On a million rows that's roughly 20 comparisons instead of 1,000,000.
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at); -- composite index
CREATE UNIQUE INDEX idx_users_email ON users(email);- Columns in WHERE clauses with high cardinality (many distinct values)
- Columns used in JOIN conditions
- Columns in ORDER BY when you're reading a small fraction of the table
- Covering index: index includes all columns the query needs, so the DB never touches the table
- Tables with very few rows — a full scan is faster
- Columns with low cardinality (e.g., a boolean
is_active— index rarely helps) - Applying functions to indexed columns in WHERE:
WHERE YEAR(created_at) = 2024 -- can't use index on created_at WHERE created_at >= '2024-01-01' -- can use index
- Leading wildcard in LIKE:
WHERE name LIKE '%smith' -- full table scan WHERE name LIKE 'smith%' -- can use index
- Write-heavy tables — every INSERT/UPDATE/DELETE must also update all indexes
-- See the query plan (doesn't run the query)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Run the query and show actual timings (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Things to look for:
Seq Scan/ALL— full table scan, often needs an indexIndex Scan— using an index, goodIndex Only Scan— covering index, best caseNested Loopwith large row estimates — investigate- High
rowsestimate vs actual — stale statistics, runANALYZE
The N+1 problem: you query for 100 orders, then loop and query for each order's customer individually — 101 queries instead of 1.
-- N+1 (pseudo-code in application layer):
-- SELECT * FROM orders; -- 1 query
-- for each order:
-- SELECT * FROM customers WHERE id = order.customer_id -- N queries
-- Fix: join once
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id;1. SELECT * on a wide table
-- Pulling 50 columns when you need 3
SELECT * FROM events; -- slow
SELECT user_id, event, ts FROM events; -- fast2. Missing index on a JOIN column
-- If order_items.order_id has no index, this joins badly
SELECT * FROM orders o JOIN order_items oi ON oi.order_id = o.id;
CREATE INDEX idx_order_items_order_id ON order_items(order_id);3. OFFSET pagination on large tables
-- OFFSET 50000 means the DB reads and discards 50000 rows
SELECT * FROM events ORDER BY id LIMIT 10 OFFSET 50000;
-- Keyset (cursor) pagination: remember the last seen id
SELECT * FROM events WHERE id > 50100 ORDER BY id LIMIT 10;4. Correlated subquery in SELECT
-- Runs once per row — 1M rows = 1M subquery executions
SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS cnt
FROM customers c;
-- Rewrite with a JOIN
SELECT c.name, COUNT(o.id) AS cnt
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;5. Implicit type conversion
-- customer_id is INT, '42' is VARCHAR — the index may not be used
WHERE customer_id = '42'
-- Use the correct type
WHERE customer_id = 42First Normal Form (1NF): Each column holds one value. No repeating groups.
-- Bad: multiple values in one column
CREATE TABLE orders (
id INT,
products VARCHAR(500) -- 'Widget,Gadget,Doohickey'
);
-- Good: separate table for line items
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT
);Second Normal Form (2NF): 1NF + every non-key column depends on the whole primary key (matters for composite keys).
-- Bad: product_name depends only on product_id, not the full composite key
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name VARCHAR(200), -- depends only on product_id
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- Good: product_name lives in products tableThird Normal Form (3NF): 2NF + no non-key column depends on another non-key column.
-- Bad: city depends on zip_code, not on id
CREATE TABLE customers (
id INT PRIMARY KEY,
zip_code CHAR(5),
city VARCHAR(100) -- determined by zip_code, not id
);
-- Good: separate zip_codes table, or accept the denormalization3NF is the right default for transactional systems. Denormalize intentionally for reporting/analytics when joins become a bottleneck.
| Use case | Good choice | Bad choice |
|---|---|---|
| Primary key | INT or BIGINT |
VARCHAR |
| Money | DECIMAL(10,2) or NUMERIC |
FLOAT / DOUBLE — rounding errors |
| True/false | BOOLEAN or TINYINT(1) |
CHAR(1) with 'Y'/'N' |
| Short text | VARCHAR(n) |
CHAR(n) unless fixed-width |
| Long text | TEXT |
VARCHAR(10000) |
| Datetime with timezone | TIMESTAMPTZ (PG) |
DATETIME without tz |
| UUID / GUID | UUID (PG) or CHAR(36) |
VARCHAR(255) |
| JSON data | JSONB (PG) or JSON |
TEXT (loses validation) |
| Enum / status | VARCHAR + check constraint |
DB ENUM type (hard to alter) |
Never store money as FLOAT. 0.1 + 0.2 in floating point is 0.30000000000000004. Use DECIMAL or store cents as INT.
CREATE TABLE customers (
id SERIAL PRIMARY KEY, -- auto-increment integer
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
status VARCHAR(20) NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'inactive', 'banned'))
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);ON DELETE options for foreign keys:
RESTRICT/NO ACTION— prevent deletion of parent if children exist (safest default)CASCADE— delete children automatically when parent is deleted (use carefully)SET NULL— set the FK column to NULL when parent is deleted
Consistency matters more than which convention you pick. Pick one and stick to it.
-- Recommended defaults:
-- Tables: plural snake_case orders, order_items, customers
-- Columns: singular snake_case customer_id, created_at, unit_price
-- Primary key: id (or table_name_id if you prefer explicit)
-- Foreign key: referenced_table_id customer_id, product_id
-- Indexes: idx_table_column(s) idx_orders_customer_id
-- Booleans: is_/has_/can_ is_active, has_paid, can_refund
-- Timestamps: _at suffix created_at, updated_at, deleted_atAvoid reserved words as column names: date, name, order, user, value, type, key. They work if quoted but cause headaches everywhere.
| Keyword | What it does |
|---|---|
SELECT |
Choose which columns to return |
FROM |
Choose the source table |
WHERE |
Filter rows before grouping |
GROUP BY |
Collapse rows into groups |
HAVING |
Filter groups after aggregation |
ORDER BY |
Sort the result |
LIMIT |
Cap the number of rows returned |
OFFSET |
Skip the first N rows |
JOIN |
Combine rows from two tables (INNER is default) |
LEFT JOIN |
All rows from left table, NULLs for no match on right |
RIGHT JOIN |
All rows from right table, NULLs for no match on left |
FULL OUTER JOIN |
All rows from both tables |
ON |
Specify the join condition |
AS |
Rename a column or table in output |
DISTINCT |
Remove duplicate rows |
COUNT |
Count rows (NULLs excluded unless COUNT(*)) |
SUM |
Total of numeric column |
AVG |
Mean of numeric column |
MIN / MAX |
Smallest / largest value |
COALESCE |
First non-NULL value in a list |
NULLIF |
Return NULL if two values are equal |
CASE |
Conditional logic inside a query |
IN |
Match any value in a list |
BETWEEN |
Range check (inclusive) |
LIKE |
Pattern match with % and _ |
EXISTS |
True if subquery returns at least one row |
WITH |
Define a named CTE before the main query |
UNION |
Combine results, remove duplicates |
UNION ALL |
Combine results, keep duplicates (faster) |
EXPLAIN |
Show the query execution plan |
Not in the main sections but used constantly:
-- Simple CASE
SELECT
order_id,
CASE status
WHEN 'pending' THEN 'Waiting'
WHEN 'shipped' THEN 'On the way'
WHEN 'delivered' THEN 'Done'
ELSE 'Unknown'
END AS status_label
FROM orders;
-- Searched CASE (more flexible)
SELECT
first_name,
salary,
CASE
WHEN salary >= 120000 THEN 'Senior'
WHEN salary >= 80000 THEN 'Mid'
WHEN salary >= 50000 THEN 'Junior'
ELSE 'Intern'
END AS level
FROM employees;
-- CASE in aggregation — count by condition without GROUP BY
SELECT
COUNT(*) FILTER (WHERE status = 'completed') AS completed, -- PostgreSQL
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;
-- Portable version:
SELECT
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;-- Combine customers and suppliers into one contact list
SELECT name, email, 'customer' AS type FROM customers
UNION ALL
SELECT name, email, 'supplier' AS type FROM suppliers
ORDER BY name;
-- UNION removes duplicates (slower — has to sort/hash to deduplicate)
-- UNION ALL keeps everything (faster — use when you know there are no duplicates)
-- Rules:
-- Same number of columns in each SELECT
-- Columns in matching positions must have compatible types
-- ORDER BY applies to the final combined resultBEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- If anything went wrong:
ROLLBACK;
-- If everything is good:
COMMIT;Either both updates happen or neither does. That's the point.
-- Savepoints: partial rollback within a transaction
BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT before_items;
INSERT INTO order_items (...) VALUES (...); -- if this fails:
ROLLBACK TO before_items; -- undo just the items
-- order still exists, try again
COMMIT;Found an error? Have a query pattern that deserves a spot here? See CONTRIBUTING.md.
Pull requests are welcome. The bar is: would a working developer reach for this in the middle of writing real code? If yes, it belongs here.
Maintained by Arda Kocadoru.