NULL behavior in aggregate functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What happens to NULL values when using the COUNT(column) aggregate function?

COUNT(column) ignores NULL values and counts only non-NULL entries in that column.

beginner

How do aggregate functions like SUM, AVG, MIN, and MAX treat NULL values?

They ignore NULL values and perform calculations only on non-NULL values.

intermediate

What is the result of SUM() or AVG() if all values in the column are NULL?

The result is NULL because there are no non-NULL values to calculate.

beginner

Does COUNT(*) ignore NULL values?

No, COUNT(*) counts all rows, including those with NULL values in any column.

intermediate

Why is it important to understand NULL behavior in aggregate functions?

Because NULLs can affect the results of queries, leading to unexpected counts or calculations if not handled properly.

Which aggregate function counts all rows, including those with NULL values?

A COUNT(column)

B AVG(column)

C SUM(column)

D COUNT(*)

COUNT(*) counts every row regardless of NULLs, while COUNT(column) ignores NULLs.

If a column has values (10, NULL, 20), what does SUM(column) return?

A 30

B NULL

C 10

D 20

SUM ignores NULL and adds 10 + 20 = 30.

What does AVG(column) return if all values are NULL?

A 0

B Error

C NULL

D 1

AVG returns NULL when no non-NULL values exist.

Which aggregate function ignores NULL values when counting?

A COUNT(column)

B COUNT(NULL)

C COUNT(1)

D COUNT(*)

COUNT(column) counts only non-NULL values.

MIN(column) with values (NULL, 5, 3) returns:

A NULL

B 3

C 5

D Error

MIN ignores NULL and returns the smallest non-NULL value.

Explain how NULL values affect the results of COUNT(column) and COUNT(*).

Think about whether NULLs are included or excluded in each count.

Describe what happens when aggregate functions like SUM or AVG operate on columns with NULL values.

Consider how NULLs affect the calculation and final output.