TRAINING | DAY 4

Order of Execution, NULL Filters, Aggregation, GROUP BY & HAVING

How a query really runs, how to filter unknown values, and how to summarise data with WHERE versus HAVING.

1. The Crux in 60 Seconds

Know the run order

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then TOP. Aliases from SELECT therefore work in ORDER BY but not in WHERE or HAVING.

NULL needs IS NULL

city = NULL returns nothing, because unknown cannot be compared, even with itself. Use IS NULL / IS NOT NULL.

Aggregate with GROUP BY

COUNT, SUM, MAX, MIN, AVG summarise rows; GROUP BY gives one row per group, like an Excel pivot table. Aggregates ignore NULLs.

WHERE vs HAVING

WHERE filters rows before grouping; HAVING filters grouped (aggregated) results. Row-level conditions belong in WHERE.

2. Foundational Concepts & Comparisons

WHERE (row-level filter)

Checks each row independently, ignoring neighbouring rows
Runs right after FROM, so it can only use real table columns
Cannot use aggregates or SELECT aliases
Cheaper: shrinks the data before costly aggregation

HAVING (aggregate-level filter)

Works on groups after GROUP BY has formed them
Can use aggregates such as SUM(sales), and grouping columns
Runs before SELECT, so SELECT aliases fail here too
A group comes out complete or not at all, never partially

Assignment review: "customer name neither starts with A nor ends with N"

The instructor admitted the wording was confusing. The intent: exclude names that start with A and end with N (a name such as "Ankit" is therefore kept, because it does not end with N).

WHERE customer_name NOT LIKE 'a%n'

One pattern, one negation: starts with a, anything in the middle, ends with n. Fastest option.

WHERE NOT (customer_name LIKE 'a%'
       AND customer_name LIKE '%n')

Negate a bracketed group of conditions. Same result, but three operations.

The positive match returned 179 rows under either form, and the negated version returned 9,815. Two separate NOT LIKE conditions are a different test: read the whole condition as one unit, not word by word. NOT IN does not suit this: IN / NOT IN compare exact values, so patterns need LIKE.

Pattern matching limits

LIKE only filters; it never modifies data. Removing hyphens from phone numbers needs a REPLACE function (covered with functions)
ESCAPE: the instructor believes several characters can be listed (for example % and _) and promised to test and confirm

Warm-up note

Keywords (WHERE, AND, NOT LIKE) show in colour in the editor
Where a student asked about LIKE on a text column such as phone numbers, the same rule held: use it to search, not to change

3. Architectural Mechanics & Workflows: Order of Execution

Think like the database: the order you write a query is not the order it runs. Written: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (TOP sits in the SELECT line). Wrong keyword order gives "Incorrect syntax near GROUP BY".

1. FROMFind the table

→

2. WHEREFilter rows

→

3. GROUP BYForm groups

→

4. HAVINGFilter groups

→

5. SELECTPick / derive columns

→

6. ORDER BYSort

→

7. TOPKeep n rows

Top 5 furniture orders by sales

SELECT TOP 5 * FROM orders
WHERE category = 'furniture'
ORDER BY sales DESC;

Filter first, select the columns, sort, and only then cut to five rows. TOP looks as if it runs together with the column list, but it runs last. In databases that write LIMIT 5 at the end of the query this is easier to see; TOP and LIMIT are the same thing. If unsure about TOP, remove it, run the query and take the first rows of that result.

How the instructor proved the order

Test	Result	What it proves
Create `profit / sales AS ratio` in SELECT, then `ORDER BY ratio`	Runs fine	SELECT runs before ORDER BY
`WHERE ratio > 0`	Error: invalid column name "ratio"	WHERE runs before SELECT; WHERE sees only table columns
Aggregate query plus `TOP 5`	Still one row	TOP counts rows after aggregation, so it is last
ORDER BY with the expression itself instead of the alias	Allowed	Alias is optional in ORDER BY

Aliases (AS)

AS names a derived column, e.g. a ratio or SUM(sales) AS total_sales; the name becomes the column header
Usable in ORDER BY (it runs after SELECT)
Not usable in WHERE or HAVING
Aside: some modern databases silently rewrite the query (as a subquery) so an alias in WHERE appears to work. Fundamentally it is not allowed

Student doubts settled

"Is the whole WHERE evaluated, then rows go to the output?" Yes: the full condition is checked per row, qualifying rows move on
"Is it TOP then ORDER BY?" No: ORDER BY first, TOP afterwards
The question about HAVING's slot in the order was parked until HAVING was taught (see page 4)

Why this matters

In advanced questions, most confusing errors ("invalid column name", unexpected results) come from using a column or alias before the step that creates it. Keep the seven-step order in mind and ask: has this name been created yet at this point?

3. Mechanics (continued): Filtering NULL

Setting up test data

UPDATE orders SET city = NULL
WHERE order_id IN (id1, id2);

"2 rows affected" (a single order ID can span several rows). NULL is written without quotes. Setting NULL is an assignment, not a comparison, so it works.

The right filter

WHERE city = NULL      -- no rows
WHERE city IS NULL     -- the 2 rows
WHERE city IS NOT NULL -- all the rest

Write IS NOT NULL, not "NOT IS NULL". Combine with other conditions as usual.

What NULL is

Unknown. Not blank, not zero, not an empty string; a space or '' is a real value
Analogy: a CSV row with nothing between two commas where the city should be; or the optional middle name on a form
Applies to every data type: string, integer, date
Interview answer given: "NULL is unknown"

Not comparable

Not equal, less, or greater than anything
Analogy: like infinity, which is not equal to infinity either
NULL = NULL qualifies no rows
Matters again with joins and in many interview questions

3. Mechanics (continued): Aggregation and GROUP BY

Until now filters worked on individual rows. Aggregation looks at many rows together and produces summary rows.

SELECT COUNT(*) AS cnt, SUM(sales) AS total_sales,
       MAX(sales) AS max_sales, MIN(profit),
       AVG(profit) AS average_profit
FROM orders;                       -- one row for the whole table

COUNT(*) gives the number of rows. SUM and AVG work on numbers; MAX and MIN also work on text and dates; COUNT works on everything
Check: SELECT TOP 1 * FROM orders ORDER BY sales DESC shows the same top value as MAX(sales)
WHERE can precede aggregation; TOP after an aggregate is pointless (one row)

GROUP BY

SELECT region, COUNT(*), SUM(sales),
       MAX(sales), MIN(sales), AVG(profit)
FROM orders
GROUP BY region;

All rows with the same region collapse into one row; one output row per region (four regions)
Same idea as an Excel pivot table with region in rows and sales as the value; the figures matched
Group by two columns (region, category): one row per combination, 12 in the data
GROUP BY on its own gives distinct values; adding DISTINCT beside it is pointless
Not a filter: nothing is removed, rows are merged

Golden rule (common interview question)

SELECT region, category, SUM(sales)
FROM orders GROUP BY region;   -- ERROR

Error: column category is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. A region holds several categories, so the database cannot pick one.

Rule: with GROUP BY, SELECT may contain only grouping columns or aggregated columns. Fix: add category to GROUP BY, or aggregate it (e.g. MAX(order_date) works, plain order_date does not).

3. Mechanics (continued): WHERE, HAVING and COUNT

WHERE before GROUP BY

SELECT region, SUM(sales) AS total_sales
FROM orders
WHERE profit > 50
GROUP BY region
ORDER BY total_sales DESC;

Rows with profit of 50 or less are dropped first, so the sums are smaller than without the filter. With TOP 2 the result was West and East; removing TOP shows all four regions.

HAVING on an aggregate

SELECT subcategory, SUM(sales) AS total_sales
FROM orders
GROUP BY subcategory
HAVING SUM(sales) > 100000
ORDER BY total_sales DESC;

10 subcategories qualified. With WHERE profit > 50 added, only 7 did. Using the alias total_sales in HAVING gives "invalid column name": HAVING runs before SELECT.

Do not use HAVING for plain columns

HAVING subcategory = '...' works but wastes effort: it groups everything, then filters. WHERE subcategory = '...' filters first, and aggregation is the costly step. Use HAVING only when the condition involves an aggregate.

With no WHERE at all, the grouping runs over every row of the table.

Which functions work where

MAX / MIN on text sort alphabetically: MAX(category) gave "T" for Technology
SUM on text: error "data type varchar is invalid for sum operator"
Whatever works in SELECT can be used in HAVING, including MAX(order_date)
The HAVING expression need not appear in SELECT

Worked puzzle: HAVING vs WHERE on dates

Mini data: chairs has two orders (sales 100 and 200, both 2019). Bookcases has two orders (sales 300 on an older date and 400 on a date in 2020). Cutoff: a date early in 2020.

Query	Output	Why
`GROUP BY subcategory HAVING MAX(order_date) > cutoff`	Bookcases, 700	Groups first; the whole bookcases group passes because its max date qualifies; sum covers both rows
`WHERE order_date > cutoff` then group	Bookcases, 400	Row-level: only the single late row survives

Most students first answered 400 in the chat poll; 700 is correct for HAVING. A group is either returned in full or not at all.

COUNT variants and NULL behaviour (test results)

Expression	Result
`COUNT(*)`, `COUNT(category)`	All rows (9,994 by the instructor's figures)
`COUNT(DISTINCT category)`	3 (distinct categories)
`COUNT(1)`, `COUNT(0)`, `COUNT(100)`, any constant	Same as COUNT(*): counts the constant once per row; many tutorials use COUNT(1) as a habit
`COUNT(city)` with 2 NULL cities	Two fewer than COUNT(*); COUNT(column) skips NULLs
`AVG(sales)` for east: 100, NULL, 200	150 (300 / 2), not 100
`SUM(sales) / COUNT(region)` on the same data	100 (300 / 3), since COUNT(region) counts 3 rows
`COUNT(DISTINCT region)`	1, so the division gives 300
Zero instead of NULL	Counted as a real value; average becomes 100

5. Best Practices & Golden Rules

Pitfall: = NULL

It never matches. Use IS NULL or IS NOT NULL.

Pitfall: loose column with GROUP BY

Every SELECT column must be grouped or aggregated; otherwise the query errors.

Rule: filter early

Put row-level conditions in WHERE, not HAVING; aggregation is costly, so shrink the data first.

Rule: ask which kind of filter

Row-level (a category, a date) means WHERE. A condition on a sum, average, min or max means HAVING.

Rule: aggregates skip NULLs

SUM, MAX, MIN, AVG and COUNT(column) ignore NULL; AVG divides by the non-NULL count.

Rule: TOP last

When confused, drop TOP, run the query and read off the first rows.

Trainer Q&A recap

Can NOT IN do pattern matching? No; it compares exact values.
Can ORDER BY use the expression instead of the alias? Yes.
Does GROUP BY filter? No; it merges rows.
Must the grouped column appear in SELECT? No, but the output then makes no sense.
Must a HAVING aggregate be in SELECT? No, like WHERE conditions.
Why NULL is not blank or zero? It means unknown (interview-style question).
Does having a group apply to partial data? No: groups are complete or excluded.

6. Numbers & Metrics to Remember

179Rows where name starts with a and ends with n

9,815Rows returned by the negated condition

2NULL cities created for the NULL tests

150AVG of 100, NULL and 200

7. Mini Glossary

Alias:: a name given to a column or expression with AS.
Aggregate function:: COUNT, SUM, MAX, MIN or AVG over many rows.
GROUP BY:: merges rows with equal values into one group row.
HAVING:: filter applied to aggregated groups.

IS NULL:: the correct test for unknown values.
LIKE pattern:: text search where % stands for any characters.
Row-level filter:: a WHERE condition checked row by row.
Distinct count:: COUNT(DISTINCT col) counts unique non-NULL values.

8. Quick Self-Test

Write a filter that excludes names starting with a and ending with n, and say why it is faster than negating two conditions.
State the seven-step execution order and one test that proves SELECT runs before ORDER BY.
Scenario: WHERE city = NULL returns nothing. Why, and what do you write?
Scenario: SELECT region, category, SUM(sales) ... GROUP BY region. What happens and why?
Scenario: you need subcategories with total sales above 100,000 only among rows with profit above 50. Which condition goes in WHERE and which in HAVING?
Bookcases hold 300 (older) and 400 (2020) sales. What do HAVING MAX(order_date) and WHERE on order_date each return?
What does AVG return for 100, NULL, 200, and why does SUM/COUNT(region) differ?
Compare COUNT(*), COUNT(1), COUNT(city) and COUNT(DISTINCT category).