--- title: "04 — DML and the SELECT Pipeline: FROM, WHERE, GROUP BY, HAVING, ORDER BY" uid: dml-organizing-queries tags: ["sql", "order-by", "roadmap:sql", "from", "dml", "where", "having", "group-by"] excerpt: "A query flows through a fixed logical pipeline — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — and each clause is a stage that shapes the rows passing through." date: 2026-08-13T03:27:38+0000 source: https://www.aveshina.my.id/en/blog/dml-organizing-queries --- 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. ```figure ``` ## 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](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](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](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](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](https://www.programiz.com/sql/group-by) [6] Programiz, "SQL HAVING Clause," programiz.com, 2024. [Online]. Available: [https://www.programiz.com/sql/having](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](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](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](https://www.youtube.com/watch?v=h_HHTNjAgS8) ```quiz Q: In what order does the database engine logically evaluate a SELECT query? - SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY correct: 1 explain: The engine reads FROM first, filters rows with WHERE, buckets with GROUP BY, filters groups with HAVING, projects the SELECT list, then sorts with ORDER BY — regardless of the order you write. Q: Why can't WHERE reference aggregate functions like SUM(total)? - Because aggregates are too slow for WHERE - Because WHERE runs before grouping, so aggregates don't exist yet correct: 1 explain: WHERE filters raw rows before GROUP BY. Aggregates are computed during grouping, so filtering on them is HAVING's job. Q: You want only customers whose total paid spend exceeds 1000. Which clause expresses that? - WHERE SUM(total) > 1000 - HAVING SUM(total) > 1000 correct: 1 explain: HAVING runs after GROUP BY and can reference aggregates. WHERE cannot. Q: Every non-aggregated column in the SELECT list must also appear in… - ORDER BY - GROUP BY correct: 1 explain: When grouping, the engine needs to know which value to show for each bucket, so any non-aggregated selected column must be a grouping column. Q: ORDER BY can reference column aliases defined in the SELECT because… - aliases are just comments - ORDER BY runs after the SELECT list is projected, so the aliases already exist correct: 1 explain: ORDER BY is the last stage, evaluated after the SELECT projection, so aliases defined there are available to it. ```