SQL Fundamentals
SELECT, WHERE, GROUP BY, ORDER BY, and subqueries — the core query vocabulary every storage system eventually speaks.
Overview
Three operations cover the vast majority of real queries: filtering rows down to the ones you want, aggregating them into a summary, and sorting the result. In SQL that's `WHERE`, `GROUP BY`, and `ORDER BY`, built around a `SELECT`. This vocabulary shows up far beyond traditional SQL databases too — most NoSQL and analytics engines that offer any kind of query language end up reimplementing some version of filter, aggregate, sort, because those three operations are what querying actually means.
Why It Exists
Every one of these clauses exists to answer a different kind of question. `WHERE` answers 'which rows.' `GROUP BY` (paired with aggregate functions like `SUM` or `COUNT`) answers 'summarized how.' `ORDER BY` answers 'in what order.' A subquery — a `SELECT` nested inside another statement — exists for the case where the value or set of rows you need to filter or compare against isn't a literal, it's itself the result of a query. Learning these as separate, composable tools is what lets you build up a precise question piece by piece instead of memorizing whole queries by rote.
Real World Example
Building 'the top 5 best-selling products last month' one clause at a time: start from the `order_items` table, filter down to last month with `WHERE order_date >= '2026-07-01'`, group the remaining rows by product with `GROUP BY product_id`, compute each group's total with `SUM(quantity)`, sort the groups highest-first with `ORDER BY total DESC`, and keep only the top five with `LIMIT 5`. Each clause answers exactly one question in that sentence — which rows, grouped how, summed as what, sorted which way, how many.
Example Data
order_items — raw rows, before WHERE/GROUP BY/ORDER BY run
| id | product | quantity | order_date |
|---|---|---|---|
| 1 | Laptop | 1 | 2026-07-03 |
| 2 | Mouse | 3 | 2026-07-05 |
| 3 | Laptop | 1 | 2026-07-18 |
| 4 | Keyboard | 2 | 2026-07-22 |
Result — after GROUP BY product_id, SUM(quantity), ORDER BY total DESC, LIMIT 5
| product | total_quantity |
|---|---|
| Mouse | 3 |
| Laptop | 2 |
| Keyboard | 2 |
How It Works
SQL is written in one order but a query engine executes it in a different, fixed logical order: `FROM`/`JOIN` first (find the source rows), then `WHERE` (filter individual rows), then `GROUP BY` (collapse the remaining rows into groups), then `HAVING` (filter the groups themselves, using aggregate results — this is why `HAVING` exists as a separate clause from `WHERE`, which runs before grouping happens and can't see an aggregate yet), then `SELECT` (compute the final output columns), then `ORDER BY` (sort the result), then `LIMIT` (cut it down to a page size). A subquery is just a full `SELECT` used inside another query, wherever a value, list, or table would normally go — most commonly inside a `WHERE` clause, to filter against a computed set of values.
Diagram
The logical execution order — not the order you write the clauses in
FROM / JOIN
find the source rows
WHERE
filter individual rows
GROUP BY
collapse rows into groups
HAVING
filter the groups
SELECT
compute output columns
ORDER BY
sort the result
LIMIT
cut down to a page size
Common Mistakes
Assuming SQL executes top-to-bottom in the order it's written, starting with SELECT
Why: This is the single most common source of confusion for beginners — it's why you can't reference a column alias defined in SELECT from inside WHERE, and why HAVING seems to duplicate WHERE until you understand it runs at a different stage.
Fix: Learn the real logical execution order — FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — and reason about queries in that order, not reading order.
Using WHERE to filter on an aggregate value, or HAVING to filter individual rows
Why: WHERE runs before grouping happens, so it has no access to aggregate results like SUM or COUNT — using it that way is a straight error. Using HAVING for a plain row filter works but is needlessly expensive, since it filters after grouping instead of before.
Fix: Filter individual rows with WHERE, before grouping. Filter aggregated group results with HAVING, after grouping.
Writing a correlated subquery that re-runs once per outer row on a large table
Why: A subquery that depends on each outer row's value can silently turn what looks like a simple query into one that scales quadratically with table size.
Fix: Check the actual query plan with EXPLAIN, and consider rewriting a correlated subquery as a JOIN or a window function, which the next chapters build toward.
Interview Questions
What's the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping happens, so it can't reference an aggregate like SUM or COUNT. HAVING filters groups after GROUP BY has run, so it can reference aggregate results.
Why can't you reference a SELECT column alias inside a WHERE clause in the same query?
Because of the logical execution order: WHERE runs before SELECT computes its output columns, so at the point WHERE executes, that alias doesn't exist yet. Some engines allow it as a convenience extension, but relying on it isn't portable, and understanding why clarifies the real execution order.
How would you diagnose a query that's slow because of a correlated subquery?
Run EXPLAIN (or EXPLAIN ANALYZE) and look for the subquery being executed once per outer row rather than once overall — that shows up as a nested-loop-style plan whose cost scales with the outer row count. The fix is usually to rewrite the correlated subquery as a JOIN against a pre-aggregated subquery, or as a window function, so the engine can compute it once instead of repeatedly.
Production Best Practices
Do
✓Reason about a query in its real logical execution order, not the order it's written.
✓Use WHERE for row-level filters and HAVING only for filters on aggregated results.
✓Check EXPLAIN before assuming a subquery-heavy query will scale.
Don't
✗Don't assume SELECT runs first just because it's written first.
✗Don't use HAVING for a plain row filter that WHERE could handle before grouping.
✗Don't leave a correlated subquery unchecked on a table that's expected to grow.
Comparison
| Filters | Runs Relative to Grouping | Can Use Aggregates? | |
|---|---|---|---|
| WHERE | Individual rows | Before | No |
| HAVING | Groups | After | Yes |
| ORDER BY | N/A — sorts the result | After SELECT | Yes |