The AVG, MIN, MAX, and SUM functions almost always return a null value when there are no matching rows (see No Rows Match for exceptions). One can use the COALESCE function, or a CASE expression, to convert the null value into a suitable substitute. Both methodologies are illustrated below:
Convert null output (from AVG) to zero
SELECT COUNT(*) AS c1
, AVG(salary) AS a1
, COALESCE(AVG(salary),0) AS a2
, CASE
WHEN AVG(salary) IS NULL THEN 0
ELSE AVG(salary)
END AS a3
FROM staff
WHERE id < 10;
ANSWER
|
C1 |
A1 |
A2 |
A3 |
|
0 |
null |
0 |
0 |
No Rows Match
How many rows are returned by a query when no rows match
the provided predicates? The answer is that sometimes you get none, and
sometimes you get one:
FROM sysibm.systables
WHERE creator = 'ZZZ';
ANSWER: no row
Query with no matching rows (2 of 8)
SELECT MAX(creator)
FROM sysibm.systables
WHERE creator = 'ZZZ';
ANSWER: Null
Query with no matching rows (3 of 8)
SELECT MAX(creator)
FROM sysibm.systables
WHERE creator = 'ZZZ'
HAVING MAX(creator) IS NOT NULL;
ANSWER: no row
Query with no matching rows (4 of 8)
SELECT MAX(creator)
FROM sysibm.systables
WHERE creator = 'ZZZ'
HAVING MAX(creator) = 'ZZZ';
ANSWER: no row
Query with no matching rows (5 of 8)
SELECT MAX(creator)
FROM sysibm.systables
WHERE creator = 'ZZZ'
GROUP BY creator;
ANSWER: no row
Query with no matching rows (6 of 8)
SELECT creator
FROM sysibm.systables
WHERE creator = 'ZZZ'
GROUP BY creator;
ANSWER: no row
Query with no matching rows (7 of 8)
SELECT COUNT(*)
FROM sysibm.systables
WHERE creator = 'ZZZ'
GROUP BY creator;
ANSWER: no row
Query with no matching rows (8 of 8)
SELECT COUNT(*)
FROM sysibm.systables
WHERE creator = 'ZZZ';
ANSWER: 0
There is a pattern to the above, and it goes thus:
- When
there is no column function (e.g. MAX, COUNT) in the SELECT then, if there
are no matching rows, no row is returned.
- If
there is a column function in the SELECT, but nothing else, then the query
will always return a row - with zero if the function is a COUNT, and null
if it is something else.
- If
there is a column function in the SELECT, and also a HAVING phrase in the
query, a row will only be returned if the HAVING predicate is true.
- If
there is a column function in the SELECT, and also a GROUP BY phrase in
the query, a row will only be returned if there was one that matched.
