Master SQL HAVING: Filter Groups Like a Pro

Introduction

In SQL, the HAVING clause is used together with GROUP BY to specify conditions on aggregated groups, complementing the WHERE clause which filters rows before grouping.

Sample Table and Data

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(50) NOT NULL COMMENT '班级名',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表';
INSERT INTO tbl_student_class(sno, cno, cname) VALUES
('20190607001','0607','影视7班'),
('20190607002','0607','影视7班'),
('20190608003','0608','影视8班'),
('20190608004','0608','影视8班'),
('20190609005','0609','影视9班'),
('20190609006','0609','影视9班'),
('20190609007','0609','影视9班');

Basic HAVING Example

To find classes with exactly three students:

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

Without HAVING, all groups would be returned.

HAVING Elements

The HAVING clause can only use three kinds of elements: constants, aggregate functions, and aggregate keys (the columns used in GROUP BY).

Example of an invalid HAVING condition using a non‑aggregated column:

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

This fails with Unknown column 'cname' in 'having clause' because cname is not an aggregate key after grouping.

Using HAVING Without GROUP BY

HAVING can be applied to the whole table when no GROUP BY is present:

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

Finding Missing IDs

After deleting some rows, the following query detects missing IDs:

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

Calculating Mode

To find the most frequent salary (mode) in a salary table:

SELECT salary, COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM tbl_student_salary GROUP BY salary);

Alternatively, using MAX on the count:

SELECT salary, COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT(*) >= (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary) TMP);

Calculating Median

The median can be obtained with a non‑equi self‑join:

SELECT AVG(DISTINCT salary) FROM (
  SELECT T1.salary FROM tbl_student_salary T1, tbl_student_salary T2
  GROUP BY T1.salary
  HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*)/2
     AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*)/2
) TMP;

Querying Groups Without NULL Values

To find departments where every student has submitted a report:

SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = COUNT(submit_date);

Using a CASE expression yields the same result:

SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = SUM(CASE WHEN submit_date IS NOT NULL THEN 1 ELSE 0 END);

Placement of Aggregate Keys

Although an aggregate key can appear in either WHERE or HAVING, best practice is to place it in WHERE for clearer semantics and better performance, because filtering rows before aggregation reduces the amount of data that needs to be sorted and grouped.

Summary

Understanding the set‑theoretic foundation of SQL, the three elements allowed in HAVING, and the correct order of clause execution (WHERE → GROUP BY → HAVING) helps write efficient and semantically clear queries.