SQL Queries Cheat Sheet
Quick reference for writing efficient SQL: SELECT, JOINs, aggregation, window functions, CTEs, and query performance basics.
SQL is the standard language for querying relational databases. The core clause order — SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT — is fixed; getting it right is the difference between clear and broken queries.
Clause Order
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC
LIMIT 10;
| Clause | Runs conceptually | Notes |
|---|---|---|
FROM / JOIN | 1 | Build the row set |
WHERE | 2 | Row filter — no aggregates allowed |
GROUP BY | 3 | Forms groups |
HAVING | 4 | Group filter — aggregates allowed |
SELECT | 5 | Picks/aliases columns |
ORDER BY | 6 | Sorts output |
LIMIT / OFFSET | 7 | Trims output |
JOIN Types
-- INNER: only matching rows
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- LEFT: all employees, departments where matched
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- RIGHT: all departments, employees where matched
SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;
-- FULL: everything on both sides
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
Use ON for the join condition and WHERE for filtering — moving a filter into WHERE with an outer join can silently turn it into an inner join.
Aggregates & GROUP BY
| Function | Purpose | Example |
|---|---|---|
COUNT(*) | Count rows | COUNT(*) AS total |
COUNT(col) | Count non-null values | COUNT(email) |
SUM(col) | Sum values | SUM(amount) |
AVG(col) | Mean value | AVG(price) |
MIN / MAX | Extremes | MAX(hire_date) |
STRING_AGG(col, ', ') | Concatenate values (PostgreSQL) | STRING_AGG(name, ', ') |
GROUP_CONCAT | Concatenate values (MySQL) | GROUP_CONCAT(name) |
SELECT department,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY headcount DESC;
CTEs (WITH)
CTEs name a query so later clauses can reference it — cleaner than nested subqueries and repeatable.
WITH recent_hires AS (
SELECT id, name, dept_id
FROM employees
WHERE hire_date >= '2024-01-01'
),
dept_stats AS (
SELECT dept_id, COUNT(*) AS headcount
FROM recent_hires
GROUP BY dept_id
)
SELECT d.name, ds.headcount
FROM dept_stats ds
JOIN departments d ON d.id = ds.dept_id;
Window Functions
Window functions compute over a set of rows (the window) without collapsing them — every input row stays in the output.
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;
| Function | Purpose |
|---|---|
ROW_NUMBER() OVER (...) | 1..N within the partition |
RANK() | Rank with gaps for ties |
DENSE_RANK() | Rank without gaps |
LAG(col) / LEAD(col) | Previous / next row value |
SUM() OVER (ORDER BY ...) | Running total |
Performance Basics
| Technique | What it helps |
|---|---|
Index the WHERE/JOIN/ORDER BY columns | Full scans avoided |
Avoid SELECT * | Less I/O |
Avoid functions on indexed columns (WHERE YEAR(d) = 2024) | Breaks index use; use ranges instead |
EXPLAIN ANALYZE | See the real plan and cost |
Composite index on (a, b) for WHERE a = ? AND b = ? | Leftmost-prefix rule |
LIKE '%text' | Leading wildcard kills index use |
Common Pitfalls
[!WARNING]
WHEREruns beforeGROUP BY— you cannot reference an aggregate alias inWHERE; useHAVINGfor group filters and repeat or nest the aggregate.
[!TIP] With
LEFT JOIN, put the outer-table filter in theONclause (ON ... AND t.flag = true), notWHERE, or the join silently becomes inner.