SQL Reference

How to Use the SQL Reference

Navigate via tabs: Basics, JOINs, Aggregates, Subqueries, Window Functions, CTEs, Indexes, Interview Q&A. Click a JOIN type in the visualiser to see the diagram and example query. Use the search to find specific patterns.

SQL Reference Formula

Example Calculation

The Order SQL Actually Runs In

You write a query starting with SELECT, but the database does not execute it in that order. Understanding the real sequence explains most beginner errors — like why you cannot reference a column alias in WHERE, or why HAVING and WHERE behave differently. The logical execution order is: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, and finally LIMIT.

Because SELECT runs near the end, an alias you create there does not exist yet when WHERE is evaluated — that is why filtering on a computed alias forces you to repeat the expression or wrap the query in a subquery. Keep this order in your head and SQL stops feeling arbitrary.

JOINs: The Part Everyone Trips On

A JOIN combines rows from two tables based on a related column. The type of join decides what happens to rows that have no match on the other side.

• INNER JOIN: keeps only rows that match in both tables. The default when you write JOIN.
• LEFT JOIN: keeps every row from the left table; unmatched right-side columns come back NULL. The most common way to ask "all X, and their Y if any".
• RIGHT JOIN: the mirror of LEFT — rarely needed, since you can usually swap the table order and use LEFT.
• FULL OUTER JOIN: keeps unmatched rows from both sides.
• CROSS JOIN: every combination of rows. Useful for generating grids, dangerous by accident.

A classic bug: filtering a LEFT JOIN's right-hand table in the WHERE clause silently turns it into an INNER JOIN, because NULL fails the condition. Put that filter in the ON clause instead to preserve the unmatched rows.

Aggregation: GROUP BY, WHERE vs HAVING

Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one summary value, and GROUP BY decides the buckets. The rule that confuses people is when to filter with WHERE versus HAVING: WHERE filters individual rows before they are grouped, while HAVING filters the groups after aggregation. To count only completed orders per customer, filter status in WHERE; to keep only customers with more than ten orders, filter the COUNT in HAVING. Using one where the other belongs is one of the most common SQL mistakes in interviews.

Window Functions: Ranking Without Collapsing Rows

Window functions are the feature that separates intermediate SQL from beginner SQL. Unlike GROUP BY, they compute across a set of rows but keep every individual row in the output. ROW_NUMBER, RANK, and DENSE_RANK assign positions within a partition; running totals, moving averages, and "compare each row to the previous one" (LAG/LEAD) all become one-liners.

The pattern is OVER (PARTITION BY ... ORDER BY ...): PARTITION BY resets the calculation per group, ORDER BY defines the sequence within it. Reaching for a window function instead of a self-join or correlated subquery is often the cleaner, faster solution to "top N per category" style problems.

Habits That Keep Queries Fast and Correct

• Select only the columns you need — SELECT * pulls unnecessary data and breaks when schemas change.
• Filter as early as possible (in WHERE or the ON clause) so fewer rows flow downstream.
• Make sure the columns you JOIN and filter on are indexed; an unindexed join on a large table is the usual cause of a slow query.
• Be explicit about NULL — remember that NULL = NULL is not true; use IS NULL.
• Test aggregations against a small known dataset before trusting them on production data.

Frequently Asked Questions