HAVING CLAUSE AND WHERE

This lesson on HAVING CLAUSE AND WHERE is hands-on and example-driven. After this lesson, you will be able to distinguish between the WHERE and HAVING clauses based on their execution timing and purpose in SQL queries. You will construct efficient queries that use WHERE for initial row filtering and HAVING for subsequent filtering of aggregated group results.

What You'll Be Able To Do

• Explain the execution order of WHERE, GROUP BY, and HAVING in a SQL query.
• Construct a query that uses the WHERE clause to filter raw data before aggregation.
• Write a HAVING clause to filter groups based on the result of an aggregate function.
• Identify the correct clause to use when filtering based on non-aggregated columns versus aggregated results.
• Combine WHERE and HAVING clauses effectively in a single, multi-stage filtering query.

Detailed Concept Walkthrough

1. Filtering Rows Before Grouping

WHERE filters individual rows based on specified conditions before any grouping or aggregation occurs. It acts early in the query execution process, reducing the dataset size immediately.

• Mechanism: Evaluates conditions on a row-by-row basis, retaining only those rows where the condition is true; this initial filtering occurs immediately after the data is retrieved from the specified table.
• Under the Hood: Executes immediately after the FROM clause and before GROUP BY in the standard SQL query processing order; because aggregate functions are not yet calculated at this stage, attempting to use them here will result in a syntax error.
• Syntax Rule: Cannot contain aggregate functions like AVG(), SUM(), or COUNT(); it is designed exclusively for filtering based on the values present in the raw, non-aggregated columns of the table.

SELECT 
    occupation, 
    salary 
FROM 
    employees 
WHERE 
    occupation LIKE '%manager%' 
    AND salary > 50000;

Key Takeaway: Use WHERE for filtering raw, individual data points before aggregation begins.

2. Filtering Groups After Aggregation

HAVING filters the results of the GROUP BY clause, applying conditions to the aggregated output rather than individual rows. It is the only way to filter based on aggregate function results.

• Mechanism: Filters the result set after the data has been grouped and aggregate functions (like AVG or SUM) have been calculated for each group; it applies conditions to the summary statistics generated by the aggregation process.
• Execution Flow: Executes after the GROUP BY clause but before ORDER BY; it operates on the summarized data set where each row represents a distinct group defined by the grouping columns.
• Best Practice: Always use HAVING when your filter condition explicitly references an aggregate function result, such as finding groups where the average salary exceeds a threshold, as this is its sole purpose and capability.

SELECT 
    occupation, 
    AVG(age) AS avg_age 
FROM 
    employees 
GROUP BY 
    occupation 
HAVING 
    AVG(age) > 40;

Key Takeaway: HAVING is mandatory for filtering based on the results of aggregate functions applied to groups.

3. Multi-Stage Filtering Strategy

Using both clauses allows for highly efficient, two-stage filtering: first, cleaning the raw data (WHERE), and second, filtering the summarized results (HAVING). This optimizes performance by reducing the data volume early.

• Execution Order: The WHERE clause runs first, discarding unwanted individual rows based on raw column values; the remaining rows are then grouped (GROUP BY), and finally, the HAVING clause filters the resulting groups based on their calculated aggregate values.
• Performance Benefit: Filtering with WHERE first minimizes the number of rows that the database engine must process during the computationally intensive GROUP BY and aggregation steps, leading to significantly faster query execution times.
• Syntax Rule: In a single query that uses both, WHERE must always precede GROUP BY, and HAVING must always follow GROUP BY; violating this sequence will cause the query to fail due to incorrect execution flow.

SELECT
    occupation,
    AVG(salary) AS avg_sal
FROM
    table
WHERE
    occupation LIKE '%manager%' -- Stage 1: Filter raw rows
GROUP BY
    occupation
HAVING
    AVG(salary) > 75000; -- Stage 2: Filter aggregated groups

Key Takeaway: Use WHERE to prune rows before grouping, and HAVING to filter the resulting groups based on aggregates.

Topics Covered in HAVING CLAUSE AND WHERE

1. WHERE Clause Basics (0:10 - 0:24) — The WHERE clause is introduced as a tool for filtering data at the row level.
2. HAVING Clause Introduction (0:57 - 1:29) — HAVING is explained as the necessary tool for filtering results based on aggregate functions after grouping.
3. Combined Filtering (1:30 - 3:00) — A detailed example demonstrates how to use WHERE and HAVING together in a single query.
4. WHERE Execution (2:17 - 2:34) — The instructor clarifies that WHERE runs before aggregation, preventing the use of aggregate functions.
5. HAVING Execution (2:39 - 3:12) — The execution timing of HAVING is reinforced, showing it operates on the grouped output.

SQL Cheat Sheet

• WHERE column = value — Filters individual rows before grouping

  WHERE occupation LIKE '%manager%'
  

• HAVING Aggregate() > value — Filters groups based on aggregate results

  HAVING AVG(age) > 40
  

• GROUP BY column — Required prerequisite for using HAVING

  GROUP BY occupation
  

• AVG() — Calculates the average value for a group

  HAVING AVG(salary) > 75000
  

Comparison Table

Common Pitfalls

• Mistake: Trying to use AVG() inside the WHERE clause. Avoid: Move any filter condition using an aggregate function to HAVING.
• Mistake: Using HAVING to filter non-grouped columns. Avoid: Use WHERE for filtering raw columns, even if grouping later.
• Mistake: Placing HAVING before GROUP BY in the query. Avoid: GROUP BY must always immediately precede the HAVING clause.

FAQs

• Why can't I use WHERE with AVG()? WHERE executes before the data is grouped, so the average (AVG) has not yet been calculated and is unavailable for filtering.
• If I don't use GROUP BY, can I still use HAVING? No, HAVING requires grouping, even if the entire result set is treated as a single group implicitly by the database engine.
• Does the order of WHERE and HAVING matter? Yes, WHERE must always come first to filter rows, followed by GROUP BY, and then HAVING to filter the resulting groups.