04 — DML and the SELECT Pipeline: FROM, WHERE, GROUP BY, HAVING, ORDER BY

A pile of clauses I'd copy-paste and shuffle until they worked was my SELECT-building method. Writing them down collapsed the mess into one model: a query flows through a fixed logical pipeline — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and each clause is just a stage that shapes the rows passing through. [1][2][3]

The framing that clicked is that the order I write a query differs from the order the engine evaluates it. I write SELECT first, but the engine reads FROM first. Once I knew the evaluation order, every clause had an obvious job: FROM picks the source, WHERE filters rows before grouping, GROUP BY buckets them, HAVING filters the buckets, and ORDER BY sorts the final output.

DML — the data side

DML (Data Manipulation Language) is the subset that operates on data within an existing structure: SELECT, INSERT, UPDATE, DELETE [1][2]. Where DDL shapes the stage, DML is the actors. I covered INSERT/UPDATE/DELETE in the CRUD notes; here the focus is the SELECT pipeline itself, because the same clause ordering governs every reporting query I write.

FROM — name the source

FROM declares where the data comes from — a single table, several joined tables, or a subquery [3]. Without it, the engine has nothing to read. It's the first stage evaluated, even though I write it second.

SELECT name, total FROM orders;

WHERE — filter rows, before grouping

WHERE keeps individual rows that satisfy a condition and discards the rest [4]:

SELECT name, total FROM orders WHERE total > 50;

The key point: WHERE runs before any grouping, so it can't reference aggregate functions. WHERE SUM(total) > 50 is illegal — that's HAVING's job. WHERE is for filtering raw rows; aggregates don't exist yet at this stage.

GROUP BY — bucket the rows

GROUP BY collapses rows that share the same value in the named column(s) into one bucket per distinct value [5][6]. Pair it with an aggregate (COUNT, SUM, AVG) and each bucket produces one summary row:

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

This is how I turn row-level data into per-customer, per-month, per-anything summaries. The non-aggregated columns in the SELECT must appear in the GROUP BY — otherwise the engine doesn't know which value to show for the bucket.

HAVING — filter the buckets

HAVING is WHERE for grouped results [7][8]. It runs after GROUP BY, so it can reference aggregates — the one thing WHERE cannot do:

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) > 1000;

The division of labor is the whole point: WHERE filters raw rows cheaply, early; HAVING filters computed groups after the aggregation work is done. Reaching for HAVING when WHERE would do means computing aggregates over rows I was going to throw away anyway.

ORDER BY — sort the output

ORDER BY sorts the final result by one or more columns, ascending by default, DESC for descending [9]:

ORDER BY spent DESC, customer_id ASC

It's the last stage, operating on the already-projected result set, which is why it can reference column aliases defined in the SELECT — by the time ORDER BY runs, those aliases exist.

How I use this

The habit I keep from this pipeline is writing queries in evaluation order, not keyword order. I draft FROM and WHERE first (what rows am I working with?), then GROUP BY/HAVING (how do I summarize and filter the summary?), and only then the SELECT list (which columns and aggregates do I show?). Drafting in that order surfaces mistakes early — a WHERE clause referencing an aggregate is immediately obvious as illegal, and a missing column in GROUP BY shows up before I've built a result on top of it.

References

[1] Satori Cyber, "What is DML?," satoricyber.com, 2024. [Online]. Available: https://satoricyber.com/glossary/dml-data-manipulation-language

[2] Wikipedia, "Data manipulation language," en.wikipedia.org, 2024. [Online]. Available: https://en.wikipedia.org/wiki/Data_manipulation_language

[3] AppMaster, "Difference Between DDL and DML," appmaster.io, 2024. [Online]. Available: https://appmaster.io/blog/difference-between-ddl-and-dml

[4] W3Schools, "WHERE Clause," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/sql_where.asp

[5] Programiz, "SQL GROUP BY," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/group-by

[6] Programiz, "SQL HAVING Clause," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/having

[7] Atlassian, "Efficient column updates in SQL," atlassian.com, 2024. [Online]. Available: https://www.atlassian.com/data/sql/how-to-update-a-column-based-on-a-filter-of-another-column

[8] YouTube, "How to write basic SQL — FROM clause," 2023. [Online]. Available: https://www.youtube.com/watch?v=YfTDBA45PHk

[9] YouTube, "SQL ORDER BY Sorting Clause," 2023. [Online]. Available: https://www.youtube.com/watch?v=h_HHTNjAgS8