Does COUNT(*) ignore rows containing nulls?

No. It counts input rows; COUNT of a particular column counts non-null values of that expression.

Does a null prove the value is zero?

No. Zero is an ordinary numeric value with a different meaning.

Can the reason for a null be recovered from the marker alone?

Not generally. Additional context or explicit status information is needed.

AI Glossary / Data engineering and architecture / Advanced

Null Value

Definition

A null value in SQL is a marker representing missing or unknown information rather than an ordinary value such as zero or a text string. Its interpretation depends on the application and the operation that produced it. A null may reflect unrecorded information or another documented absence. It does not, by itself, explain why the information is missing or prove that the corresponding real-world fact does not exist.

What it means

A recorded duration of zero is a known numeric value. A null duration means that the database does not supply an ordinary duration value in that position. Treating them alike can change a report's meaning. PostgreSQL documents special comparison rules for null and separate predicates for testing it. In an original learning-content catalog, a missing duration could mean that measurement is pending. An AI assistant should preserve that distinction when explaining an average or selecting resources by duration, instead of silently interpreting every missing measurement as zero minutes.

How it works

Ordinary SQL comparisons involving null generally yield unknown rather than true or false. For a scalar expression, IS NULL tests for the null marker; writing an ordinary equality comparison to NULL is not the appropriate test. Aggregate functions also differ: PostgreSQL COUNT(*) counts rows, COUNT(column) counts non-null inputs, and AVG(column) averages non-null inputs. These rules affect both the result and its denominator. An application can substitute a default where appropriate, but that choice changes interpretation and must be justified by the intended measure rather than used merely to hide blank displays.

Three practical examples

A learning dashboard reports both the average recorded resource duration and how many resources have a recorded duration.
An ingestion check distinguishes a genuine zero measurement from a missing measurement before preparing analytical data.
An AI query assistant explains why counting rows gives a different total from counting non-null values in an optional field.

Think of it this way

Imagine three weighing slips: one says 10 units, one says 20 units and one has not yet been filled in. An empty slip is not a measurement of zero. If you average the completed slips, you must describe the result as an average of the available measurements. The analogy highlights the difference between a known value and missing evidence; the proper treatment depends on what the report is intended to measure.

Worked example

Consider an original fictional table of four resources with duration_minutes values 10, 20, 0 and NULL. Assume the first three are valid measurements and the fourth has not been measured. COUNT(*) returns four rows, while COUNT(duration_minutes) returns three. SUM(duration_minutes) returns 30, and AVG(duration_minutes) returns 30/3 = 10 minutes. The recorded zero participates in the count and average. If the null is first replaced with zero, the resulting average is 30/4 = 7.5 minutes, which describes a different assumption. It is not the observed average of the three measured resources. A scalar duration_minutes IS NULL test identifies the unmeasured record. A filter for duration_minutes greater than 15 returns only the 20-minute record; the unknown duration is not established as greater than 15. The example assumes ordinary SQL null behavior and no additional filters.

Illustrative case study

In a fictional AI learning dashboard, a summary reports an average resource duration without explaining that some measurements are missing. A developer replaces nulls with zero to make every row numeric, and the displayed average falls. Reviewers reproduce the difference with the four-resource example and determine that the intended measure is the average of recorded durations. They restore that calculation and display its coverage separately. They also create a distinct report for resources awaiting measurement. The scenario illustrates documenting the population behind a calculation. It does not establish that ignoring missing observations always gives an unbiased estimate of the full catalog's duration distribution.

Common misconceptions and mistakes

NULL is not the text string 'NULL', and it is not automatically an empty string or the number zero. Another mistake is assuming every aggregate ignores nulls identically; function behavior must be checked. Ordinary comparison of two nulls does not prove the unknown values are equal. A null in an outer-join result can also arise because no matching row was found, rather than because the source contained that field as null. Finally, replacing missing values can be appropriate for a defined purpose, but it should not erase uncertainty without explanation.

Business use

For an AI reporting feature, define the meaning of missing values for each measure and show the relevant coverage. In the fictional duration report, distinguish total resources, measured resources and resources awaiting measurement. Check filters, joins and calculations with explicit null examples before relying on a generated query. If a default or imputation is used, document the assumption and retain the ability to identify affected records. This helps users understand whether a number summarizes observed values, estimates missing ones or applies a business convention. Those are different claims even when they share the same display format.

Check your understanding

How many toy resources have recorded durations?

Answer: Three.

What is their average?

Answer: Ten minutes.

Why does substituting zero produce 7.5?

Answer: It adds a fourth numeric input to the denominator while keeping the sum at 30.

Is the recorded zero excluded from the original average?

Answer: No.

Why should the report show measurement coverage?

Answer: The average does not directly describe an observed duration for the unmeasured resource.

Related terms

Missing data
Outer Join
three-valued logic
IS NULL
aggregate function
imputation

Learn next

Create a five-row table containing ordinary values, a genuine zero and a null. Predict row count, non-null count, sum and average before running a query. Then explain how replacing the null changes the question being answered, and which missingness information should remain visible.

Sources

PostgreSQL documentation, Comparison Functions and Operators: https://www.postgresql.org/docs/current/functions-comparison.html PostgreSQL documentation, Aggregate Functions: https://www.postgresql.org/docs/current/functions-aggregate.html The duration arithmetic, weighing-slip analogy and AI-dashboard scenario are original educational illustrations. The calculation describes observed values and does not claim that missing measurements are representative of the full population.

Previous: Null result Next: Nullspace