SQL: grouping, aggregates and quantified subqueries
Goals and prerequisite
Compute grouped results, distinguish row filters from group filters, and implement existence and “for all” without NULL traps. Prerequisite: SQL bags, joins and three-valued logic. Queries use the standard core; all identifier columns used for correlations below are non-null. Output order is unspecified unless ORDER BY is written.
1. Aggregates do not all count the same things
COUNT() counts rows, including rows containing NULL. COUNT(x) counts non-null x values. COUNT(DISTINCT x) counts distinct non-null x values. SUM(x), AVG(x), MIN(x), MAX(x) ignore NULL inputs. For x values 10,10,NULL,30: COUNT()=4, COUNT(x)=3, COUNT(DISTINCT x)=2, SUM(x)=50 and AVG(x)=50/3. The average denominator is not four. Types affect numeric display and arithmetic, but not the logical denominator here.
On empty input with no GROUP BY, aggregate SELECT still produces one group: COUNT(*) returns 0 while SUM(x), AVG(x), MIN(x), MAX(x) return NULL. In contrast, GROUP BY dept over no rows produces no groups and no result rows. Do not turn an empty-input SUM into zero unless the query explicitly uses a replacement such as COALESCE(SUM(x),0).
2. WHERE versus HAVING
Let Sale(dept,amount) contain (A,10),(A,20),(A,NULL),(B,5),(B,30),(C,NULL). Query:
SELECT dept,COUNT(*) AS n,SUM(amount) AS total FROM Sale WHERE amount>=10 GROUP BY dept HAVING SUM(amount)>=30;
WHERE retains (A,10),(A,20),(B,30); the NULL rows are UNKNOWN and 5 is false. Group A has n=2,total=30; B has n=1,total=30. Both pass HAVING, so these are the result rows. WHERE filters individual rows before aggregation; HAVING filters groups after aggregation. An aggregate of the current query group cannot be placed directly in that same query block's WHERE.
For portable unambiguous grouped queries, each selected expression should be a grouping expression or an aggregate. Some systems allow additional functionally dependent columns under specific rules; do not assume permissive behavior without a declared dialect. Grouping places NULL department markers into a common group.
3. Counting across an outer join
Student(sid)={1,2,3}; Enrol(eid,sid)={(11,1),(12,1),(13,2)}, with eid a non-null primary key. After LEFT JOIN on sid, student 3 has one null-extended row. Group by s.sid: COUNT(*) gives 2,1,1 for students 1,2,3; COUNT(e.eid) gives 2,1,0. To count actual enrollments, count the non-null right key, not the preserved row. Additional one-to-many joins can multiply rows and inflate aggregates; aggregate at the intended grain or deduplicate the intended identifier explicitly.
4. EXISTS and correlated subqueries
SELECT s.sid FROM Student s WHERE EXISTS (SELECT 1 FROM Enrol e WHERE e.sid=s.sid);
EXISTS is TRUE if its subquery returns at least one row, regardless of the selected value; SELECT NULL would also suffice. The reference to s.sid makes this subquery correlated. With the preceding tables, results are 1 and 2 once each, assuming Student.sid is a key. A JOIN projecting sid without DISTINCT would return 1 twice. NOT EXISTS returns student 3.
Scalar subqueries are different: they provide one column and at most one row; zero rows produce NULL, while more than one row causes a cardinality error in the core SQL model. EXISTS does not have that at-most-one-row restriction.
5. IN, NOT IN, ANY and ALL
For a non-null x, IN is true when an equal value exists. If there is no equal value and the subquery includes NULL, IN is UNKNOWN; NOT IN is then also UNKNOWN. Example: 5 NOT IN (2,NULL) is UNKNOWN and is rejected by WHERE. 2 NOT IN (2,NULL) is FALSE. A NULL in the list does not make every result UNKNOWN when a decisive equality exists.
NOT EXISTS (SELECT 1 FROM R WHERE R.y=x) instead asks whether any equality comparison is TRUE. For x=5 and R.y={2,NULL}, it is TRUE. This is why NOT IN and NOT EXISTS are not generally interchangeable with nullable data. If the intended query also excludes NULL outer x, add x IS NOT NULL explicitly.
x > ANY(subquery) means at least one comparison is TRUE; x > ALL(subquery) means all comparisons are TRUE. For non-null values {2,8}, 5>ANY is TRUE and 5>ALL is FALSE. On an empty subquery, ANY is FALSE and ALL is TRUE, even when x is NULL, since there are no comparisons. With NULL comparisons and no decisive TRUE for ANY or FALSE for ALL, UNKNOWN can result. A single FALSE makes ALL false; a single TRUE makes ANY true.
6. “For every” with double NOT EXISTS
For Student(sid), Required(cid), and Enrol(sid,cid), all correlation keys non-null, students taking every required course are:
SELECT s.sid FROM Student s WHERE NOT EXISTS (SELECT 1 FROM Required r WHERE NOT EXISTS (SELECT 1 FROM Enrol e WHERE e.sid=s.sid AND e.cid=r.cid));
The inner NOT EXISTS detects a missing enrollment for one required course. The outer NOT EXISTS rejects students with any such missing course. With Required={A,B}, Enrol={(1,A),(1,B),(2,A)}, and Student={1,2,3}, only 1 passes. Empty Required makes all three pass. Repeated Enrol rows do not change existence, unlike naive count comparisons. Counting distinct matched requirements can be another approach, but the candidate domain, duplicates and empty requirements must all be handled.
Pitfalls
- COUNT(*) counts null-extended rows; COUNT(non-null right key) counts matches.
- AVG ignores NULL inputs in both numerator and denominator.
- A correlated query is logically evaluated per outer candidate, but the optimizer may implement it differently.
- NOT IN can be poisoned by NULL when no equality match is found.
- Universal conditions on an empty requirement set are true over the stated candidate domain.
Original practice: six single-correct MCQs
These original teaching questions are not previous-year questions. Choose exactly one answer.
P01. For x values 4,NULL,4,12, what are COUNT(*) and COUNT(x)?
A. (4,4) B. (4,3) C. (3,2) D. (3,3)
Answer: B
A: COUNT(x) ignores the NULL.
B: There are four rows and three non-null x values.
C: COUNT(*) does not discard NULL or duplicates.
D: COUNT(*) counts all four rows.
P02. For x values 10,NULL,20, AVG(x) is:
A. 10 B. 15 C. 30 D. NULL
Answer: B
A: This incorrectly divides by three.
B: (10+20)/2=15 because NULL is ignored.
C: Thirty is the SUM, not average.
D: Non-null inputs exist, so the average is defined.
P03. Which clause filters groups based on their aggregate values?
A. WHERE in the same query block B. HAVING C. FROM D. ORDER BY
Answer: B
A: WHERE filters rows before this grouping.
B: HAVING can test aggregate results for each group.
C: FROM constructs input rows.
D: ORDER BY sorts results rather than filtering groups.
P04. What is 5 NOT IN (2,NULL) under SQL three-valued logic?
A. TRUE B. FALSE C. UNKNOWN D. Syntax error
Answer: C
A: The NULL prevents proving all comparisons unequal.
B: No equal non-null value establishes a false NOT IN.
C: 5=2 is false and 5=NULL is unknown; negating IN remains unknown.
D: NULL is allowed in the list.
P05. For an empty subquery, x>ALL(subquery) is:
A. TRUE B. FALSE C. UNKNOWN for every x D. A cardinality error
Answer: A
A: There is no comparison violating the universal condition.
B: This is the empty-ANY result, not ALL.
C: No NULL comparison is performed when there are zero rows.
D: ALL accepts any subquery row count.
P06. A student has no matching enrollment in a LEFT JOIN; enrollment eid is non-null in real rows. Per-student COUNT(*) and COUNT(eid) are:
A. (0,0) B. (1,1) C. (1,0) D. (0,1)
Answer: C
A: The outer join preserves one null-extended row.
B: The extended eid is NULL, so COUNT(eid) is zero.
C: One preserved row exists, but no non-null enrollment key.
D: COUNT(*) cannot be zero while counting a non-null eid in that group.
Sources and scope
Aligned to the 2026 course. This lesson covers the stated slice, not the entire Databases syllabus. All numerical examples and questions are original. No claim of 2027 completeness.
Sign in to keep your progress. Sign in