Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices

The HAVING clause is typically paired with GROUP BY to filter aggregated groups, whereas the WHERE clause filters individual rows before aggregation.

Basic Usage

For a table tbl_student_class , the query below returns classes that have exactly three students:

SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;

HAVING can only contain constants, aggregate functions, and aggregate keys (the columns used in GROUP BY ).

Common Errors

Using a non‑aggregated column such as cname in HAVING causes an error because the column is not part of the grouped result:

SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cname = '影视9班';

Result: [Err] 1054 - Unknown column 'cname' in 'having clause' .

HAVING Without GROUP BY

HAVING can be used on the whole table as a single group. Example to detect missing IDs:

SELECT '存在缺失的编号' AS gap FROM tbl_student_class HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;

A more robust version returns a descriptive message for empty tables, missing IDs, or a continuous sequence:

SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
    WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的编号'
    ELSE '连续' END AS gap FROM tbl_student_class;

Advanced Scenarios

Finding the Mode – Using ALL to select the salary that appears most frequently:

Calculating the Median – A self‑join with non‑equi conditions inside HAVING:

Filtering Groups with All Non‑NULL Values – Identify departments where every student has submitted a report:

Best Practices

Conditions on aggregate keys should be placed in WHERE whenever possible, because:

It makes the query semantics clearer— WHERE filters rows, HAVING filters groups.

It can improve performance by reducing the number of rows that need to be grouped and sorted, and it allows the use of indexes on the filtered columns.

Conclusion

SQL is fundamentally based on set theory; understanding HAVING helps shift thinking from row‑by‑row to set‑based operations.

The three elements of a HAVING clause are constants, aggregate functions, and aggregate keys.

Use WHERE for row‑level conditions and reserve HAVING for true group‑level filters to write clearer and faster SQL.