HAVING Clause

Overview

The HAVING clause filters aggregated groups after they have been processed by a GROUP BY clause.

Learning Objectives

• Distinguish between row-level filters (WHERE) and group-level filters (HAVING).
• Write conditional checks on aggregate expressions.
• Combine WHERE, GROUP BY, and HAVING in a single query.

Detailed Concept Explanation

• WHERE: Filters rows before any grouping or aggregation takes place.
• HAVING: Filters groups after rows are grouped and aggregated.

Because WHERE runs before grouping, it cannot reference aggregate values (e.g., WHERE COUNT(*) > 5 triggers a compilation error). To filter groups based on aggregate results, you must use HAVING.

Code Examples

SQL

-- INCORRECT: Cannot filter aggregate in WHERE
SELECT dept, SUM(salary) 
FROM employees 
WHERE SUM(salary) > 500000 
GROUP BY dept;

-- CORRECT: Filter group aggregates using HAVING
SELECT dept, SUM(salary) AS total_payroll
FROM employees 
GROUP BY dept 
HAVING SUM(salary) > 500000;

-- Combined pipeline filter
SELECT country, COUNT(*) AS customer_count
FROM customers
WHERE status = 'Active' -- 1. Filter rows
GROUP BY country        -- 2. Group rows
HAVING COUNT(*) > 10;   -- 3. Filter groups

Best Practices

• Filter Early: Use WHERE as much as possible to filter out rows before grouping them. This reduces the volume of data the database has to process and group, making queries much faster.

Interview Perspective

Interactive Challenges

Summary

HAVING filters aggregated groups after grouping is completed. Use WHERE for row filters and HAVING for group filters.