The HAVING Clause

Definition: The HAVING clause is used to filter the results of a GROUP BY query. While the WHERE clause filters individual rows before they are grouped, HAVING filters the summarized results after the grouping has occurred.

Why: Beginner SQL lessons typically introduce HAVING immediately after grouping because it solves a common problem: you cannot use aggregate functions (like COUNT or SUM) inside a WHERE clause. HAVING is the essential tool for asking questions about groups, such as "Which cities have more than 10 students?"

Syntax

The HAVING clause always follows the GROUP BY clause. It specifies a condition that the aggregated value must meet to be included in the final output.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING condition;

Example: Filtering Grouped Results

To find only the cities where there is more than one student registered, you would apply a HAVING filter to the count:

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city
HAVING COUNT(*) > 1;

Key Differences: WHERE vs. HAVING

Key Notes

• Logical Order: Think of the query flow: 1. Pick the rows (WHERE), 2. Group them (GROUP BY), 3. Filter the groups (HAVING).
• Combined Power: You can use both in one query. For example, "Find all cities with more than 5 students (HAVING), but only count students who are over 18 (WHERE)."
• Performance: Whenever possible, use WHERE to filter out rows first. This reduces the amount of data the database has to group, making the query faster.