SQL Quick Reference Notes
A practical guide to joins, aggregation, window functions, indexing and query optimization
1. SELECT Statement Basics
Every SQL query follows a logical order of execution that differs from its written order: FROM -> WHERE ->
GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. Understanding this order helps explain why
you cannot use a SELECT alias inside a WHERE clause but can use it in ORDER BY.
Example: SELECT department, COUNT(*) AS emp_count FROM employees WHERE salary > 30000
GROUP BY department HAVING COUNT(*) > 5 ORDER BY emp_count DESC LIMIT 10;
2. Joins
Join Type Returns
INNER JOIN Only matching rows in both tables
LEFT JOIN All rows from left + matched rows from right (NULL if none)
RIGHT JOIN All rows from right + matched rows from left (NULL if none)
FULL OUTER JOIN All rows from both tables, NULL where no match
CROSS JOIN Cartesian product of both tables
SELF JOIN Table joined with itself (e.g. employee-manager)
Example: SELECT o.order_id, [Link] FROM orders o LEFT JOIN customers c ON o.customer_id = [Link]; —
this returns every order even if the customer record is missing.
3. Aggregation Clauses
Clause Purpose
GROUP BY Groups rows sharing a column value for aggregation
HAVING Filters groups after aggregation (WHERE filters before)
ORDER BY Sorts final result set
LIMIT / OFFSET Restricts number of rows returned / pagination
DISTINCT Removes duplicate rows from result
4. Window Functions
Function Purpose
ROW_NUMBER() Unique sequential number per row in partition
RANK() Rank with gaps for ties
DENSE_RANK() Rank without gaps for ties
LAG(col, n) Value from n rows before current row
LEAD(col, n) Value from n rows after current row
SUM()/AVG() OVER() Running or partitioned aggregate
NTILE(n) Divides rows into n roughly equal buckets
FIRST_VALUE()/LAST_VALUE()First/last value in the window frame
Example: SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER
BY salary DESC) AS dept_rank FROM employees; — this ranks employees by salary within each
department without collapsing rows like GROUP BY would.
5. Subqueries and CTEs
• Scalar subquery — returns a single value, usable anywhere an expression is expected
• Correlated subquery — references the outer query, re-evaluated per row
• Common Table Expression (CTE) — WITH temp_table AS (SELECT ...) SELECT * FROM temp_table;
improves readability for multi-step queries
• Recursive CTE — used for hierarchical data like org charts or category trees
6. Indexing Basics
• Indexes speed up SELECT queries but slow down INSERT/UPDATE/DELETE since the index must
also be updated
• Clustered index — determines physical row order on disk (one per table)
• Non-clustered index — separate structure pointing to row locations, can have many per table
• Composite index — index on multiple columns; column order matters for which queries benefit
• Covering index — includes all columns needed by a query, avoiding a lookup to the base table
• Use EXPLAIN / EXPLAIN ANALYZE to check whether a query plan actually uses an index
7. Query Optimization Tips
• Select only needed columns instead of SELECT *
• Filter early with WHERE before JOIN where possible to reduce row counts
• Avoid applying functions to indexed columns in WHERE clauses (e.g. WHERE YEAR(date_col) = 2024
prevents index use)
• Use EXISTS instead of IN for large subqueries — it can short-circuit on first match
• Batch large INSERT/UPDATE operations instead of row-by-row execution
• Watch for implicit type conversions which silently disable index usage
8. Data Types & Constraints
• Common types: INT, BIGINT, DECIMAL/NUMERIC (exact), FLOAT/DOUBLE (approximate),
VARCHAR(n), TEXT, DATE, TIMESTAMP, BOOLEAN
• PRIMARY KEY — uniquely identifies each row, implicitly NOT NULL and indexed
• FOREIGN KEY — enforces referential integrity between tables
• UNIQUE — ensures no duplicate values in a column
• CHECK — enforces a condition on column values (e.g. CHECK (salary > 0))
9. Transactions & ACID
• Atomicity — all statements in a transaction succeed or none do
• Consistency — database moves between valid states only
• Isolation — concurrent transactions do not interfere with each other
• Durability — committed changes survive crashes/power loss
• Key commands: BEGIN TRANSACTION, COMMIT, ROLLBACK
10. Practice Query Checklist
• Write a query to find the second-highest salary in each department
• Find duplicate rows in a table using GROUP BY and HAVING
• Write a query using a window function to compute a running total
• Find customers who placed no orders using a LEFT JOIN with NULL check
• Write a recursive CTE to list all employees under a given manager