HAVING Without GROUP BY: The SQL Misconception That Limits Your Queries

HAVING Basics Recap

HAVING is commonly used with GROUP BY to filter groups after aggregation, while WHERE filters rows before grouping. For example, finding classes with exactly three students:

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

The HAVING clause can only reference constants, aggregate functions, and grouping keys (aggregation keys). Referencing a non-aggregated column like cname in HAVING raises Unknown column 'cname' in 'having clause' because the grouped result set lacks that column.

HAVING Without GROUP BY

1. Detecting Missing Sequence Numbers

When no GROUP BY is present, the entire table becomes a single group. After deleting IDs 2, 5, and 6 from tbl_student_class, the following query checks for gaps:

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

A more robust version handles empty tables:

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

2. Finding the Mode (Most Frequent Value)

Using tbl_student_salary (10 rows), the average salary (149,000) is skewed by an outlier (李小龙 at 1,000,000). The mode better represents the typical salary. Two approaches:

Using ALL predicate:

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

Using MAX (avoids ALL issues with NULL/empty sets):

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);

Result: salary 50,000 and 70,000 each appear three times (both are modes).

3. Calculating the Median

For 10 rows, the median is the average of the 5th and 6th ordered values (50,000 and 60,000 → 55,000). The query uses a non-equi self-join in HAVING to split the set into upper and lower halves that share the middle element(s):

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;

The >= COUNT(*)/2 (with equality) ensures the two halves overlap on the middle element(s), making the formula work for both odd and even counts.

4. Finding Groups With No NULLs

In tbl_student_submit_log, find departments where every student submitted (submit_date not NULL). Simple WHERE submit_date IS NOT NULL incorrectly includes 文学院 (which has one NULL). Correct approach: group by dept and compare total rows to non-NULL count:

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

COUNT(*)

includes NULLs; COUNT(column) excludes them. A more explicit version uses CASE:

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);

WHERE vs HAVING for Aggregation Keys

Conditions on grouping keys (e.g., cno = '0609') can appear in either WHERE or HAVING with identical results, but WHERE is preferred for two reasons:

Semantic clarity: WHERE filters rows; HAVING filters groups. Mixing them blurs the distinction.

Performance: WHERE reduces rows before sorting/aggregation, lowering sort volume. Indexes on WHERE columns further accelerate filtering. HAVING applies after grouping, so more rows are sorted.

Summary

SQL is rooted in set theory; thinking in sets unlocks its power.

HAVING clause elements: constants, aggregate functions, aggregation keys.

WHERE filters rows (before grouping); HAVING filters groups (after grouping). Push conditions to WHERE whenever possible for better performance.