HAVING for Group Filtering — Filter groups using HAVING

Historical Context & Motivation

The relational model of data, proposed by E. F. Codd at IBM in 1970, introduced a mathematically rigorous foundation for organizing and querying structured data. As relational database management systems matured throughout the late 1970s and 1980s, practitioners discovered that simple row-level filtering with WHERE was insufficient for answering questions about groups of rows—questions like "which departments have more than five employees?" or "which products sold more than 1000 units last quarter?" These questions require first aggregating data and then applying a filter to the aggregated results, a two-phase operation that WHERE alone cannot express because WHERE evaluates before any grouping occurs.

The fundamental question that motivated the HAVING clause is deceptively simple: how do you filter rows based on properties that only exist after aggregation? Since WHERE is evaluated before GROUP BY in the logical query processing pipeline, it has no access to aggregate values like COUNT, SUM, or AVG. The HAVING clause was introduced specifically to bridge this gap, operating after groups are formed and aggregate functions are computed.

Core Principles & Definitions

Understanding HAVING requires a firm grasp of SQL's logical query processing order. Although a SQL engine's physical execution plan may reorder operations for optimization, the logical evaluation sequence determines what data each clause can reference. The HAVING clause occupies a critical position in this sequence—after grouping and aggregation but before the final projection in SELECT. This positioning is what gives HAVING its unique power: it can reference aggregate functions that WHERE cannot.

Visual Explanation — SQL Logical Processing Pipeline

The pipeline diagram above illustrates a critical invariant: each clause can only reference data that has been produced by preceding stages. Since WHERE (step 2) executes before GROUP BY (step 3), it sees only raw, ungrouped rows and has no concept of aggregate values. Attempting to write WHERE COUNT(*) > 5 is a semantic error in every SQL dialect—the COUNT aggregate simply does not exist at that stage. The HAVING clause was designed precisely to fill this gap, providing a filter checkpoint after groups and their aggregates have been materialized.

How HAVING Works — Syntax and Semantics

The HAVING clause follows a straightforward syntactic pattern but carries deep semantic implications related to the relational algebra's selection operator (σ) applied to grouped relations. Let us examine the canonical syntax and its formal underpinnings.

Evaluation Semantics Step by Step

1. Step 1: The engine reads rows from the FROM source and applies WHERE to eliminate rows that do not satisfy the row-level predicate.
2. Step 2: GROUP BY partitions the surviving rows into groups—one group per distinct combination of grouping columns.
3. Step 3: Aggregate functions (COUNT, SUM, AVG, etc.) are computed for each group independently.
4. Step 4: HAVING evaluates its predicate against each group's aggregate values. Groups that return FALSE or NULL are discarded.
5. Step 5: SELECT projects the requested columns and aggregate expressions from the surviving groups.

Data Flow — From Raw Rows to Filtered Groups

To solidify understanding, let us trace a concrete data set through each stage of the pipeline. Consider a table called orders with columns customer_id, amount, and order_date. We want to find customers whose total spending exceeds $500.

A few observations from the trace above deserve emphasis. First, HAVING operates at the group level, not the row level—when customer B's group is eliminated, all rows belonging to B are gone, not selectively. Second, the aggregate expression in HAVING (SUM(amount)) does not need to appear in the SELECT list, although most engines support it in both places. Third, had we added a WHERE clause (e.g., WHERE order_date >= '2024-01-01'), it would have filtered individual rows before GROUP BY even formed the groups, potentially changing which customers survive the HAVING threshold.

Worked Example — Finding High-Volume Departments

Suppose we have an employees table with columns emp_id, department, salary, and hire_date. We want to find departments with more than 3 employees where the average salary exceeds $70,000, but only considering employees hired after 2020.

WHERE vs. HAVING — Comparison and Best Practices

The most common source of confusion for SQL learners is knowing when to use WHERE versus HAVING. While both are filter mechanisms, they operate at fundamentally different stages of query processing and serve distinct purposes. The table below provides a systematic comparison across several dimensions.

Connection to Advanced SQL Features

HAVING is a foundational clause, but modern SQL provides several advanced features that interact with or extend its capabilities. Understanding how HAVING relates to window functions, CTEs (Common Table Expressions), and subqueries is essential for writing idiomatic, performant queries in production systems.

A particularly powerful pattern is the correlated subquery inside HAVING. For example, to find departments whose average salary exceeds the company-wide average, you can write: HAVING AVG(salary) > (SELECT AVG(salary) FROM employees). The subquery executes once and provides a scalar benchmark, while HAVING applies that benchmark group by group. As you advance into courses on query optimization, you will encounter how the query planner handles such patterns—often by materializing the subquery result once and reusing it across all group evaluations.

Practice Problems

The following problems use a database schema with three tables: students(student_id, name, major), enrollments(enrollment_id, student_id, course_id, grade), and courses(course_id, title, department, credits). Grades are stored as numeric values (0.0 to 4.0).

Summary — HAVING for Group Filtering