Dealing with Null Output

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:

 Query with no matching rows (1 of 8)

 SELECT creator

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.

 The above documentation is copied from https://db2-sql-cookbook.org/