Working with NULL in queries

Consider:

The result is:

PostgreSQL knows the first value:

but it does not know the second value.

It therefore cannot determine the sum.

The same happens with ordinary arithmetic such as:

This does not mean that every PostgreSQL function must always return NULL when given a NULL.

Different functions can define their own behavior.

The important point is that ordinary arithmetic cannot determine a result when one of the required values is unknown.

Sometimes an application wants to substitute another value when a column is NULL.

PostgreSQL provides:

COALESCE returns the first argument that is not NULL.

For example:

returns:

And:

returns:

Conceptually:

If every argument is NULL:

the result is:

Suppose an application displays a shipping date:

You should see:

If shipped_at contains a timestamp, PostgreSQL returns that value as text.

If it is NULL, PostgreSQL returns:

This id range was chosen because it spans the point where orders stop having a shipped_at value.

COALESCE is also common with aggregates.

Consider:

No rows match.

SUM() therefore returns:

If an API needs:

instead, we can write:

Now the result is:

PostgreSQL also provides:

NULLIF(a, b) returns NULL when the two values compare equal.

Otherwise, it returns the first value.

For example:

returns:

while:

returns:

A useful mental model is:

One useful application of NULLIF is avoiding division by zero.

This would fail:

with:

But:

turns the denominator into:

so the result becomes:

instead of a division-by-zero error.

A more realistic shape is an average calculated by dividing a total by a count, where the count can be zero:

You should see:

The west region has no orders. Without NULLIF, that row would divide by zero and the whole query would fail.

Whether returning NULL is the correct application behavior depends on what that calculation represents, but the pattern is useful to recognize.

Aggregate functions such as SUM(), AVG(), MIN(), MAX(), and COUNT() reduce many rows to a single summary value. The Aggregation section covers them in full, but their treatment of NULL belongs here.

Most common aggregates ignore NULL input values.

Consider:

Then:

The NULL value does not participate in those calculations.

COUNT() has an especially important distinction.

Our orders table contains 100000 rows.

Run:

You should see:

COUNT(*) counts rows.

So it returns:

COUNT(shipped_at) counts only non-NULL values in shipped_at.

So it returns:

The missing 15000 are the processing, cancelled, and pending orders, which have not shipped yet.

A useful mental model is:

There is another important aggregate behavior.

Run:

The result is:

Now run:

The result is:

When no rows are available:

This is one reason COALESCE is often used around aggregate results.

NOT IN checks that a value differs from every entry in a list. That check runs into three-valued logic as soon as the list can contain NULL.

Consider:

You might expect 2 to be returned because:

But PostgreSQL also has to consider:

and that comparison is unknown.

The expression therefore does not become definitively TRUE.

The query returns no row.

Conceptually:

Remove the NULL from the list and the row comes back:

This is why NOT IN can produce surprising results when its values may contain NULL.

For relationship queries such as finding customers that have no matching orders, NOT EXISTS often expresses the requirement more directly:

You should see:

This does not mean NOT IN should never be used.

The important point is to know whether its input can contain NULL.

You also saw NULL while learning constraints.

By default, PostgreSQL considers NULL values distinct when enforcing a UNIQUE constraint.

For example:

Now insert:

Both rows are allowed:

The unique constraint does not treat the two NULL values as duplicates by default.

But two equal non-NULL values are rejected:

PostgreSQL answers with:

Both rows are rejected, because a single statement either succeeds completely or has no effect.

PostgreSQL also supports:

For example:

Now the unique constraint treats multiple NULL values as duplicates.

The first row is accepted:

But a second one is not:

Only one row can have:

under that constraint.

This gives you control over what NULL should mean for a particular uniqueness rule.

Remove the demonstration tables:

PostgreSQL B-tree indexes can contain NULL values.

That means an index on a nullable column can potentially help PostgreSQL find rows for queries such as:

Whether PostgreSQL actually chooses the index still depends on factors such as:

As with any index, the presence of an index does not guarantee that PostgreSQL will use it.

B-tree indexes can also order NULL values.

By default, for an ascending B-tree ordering PostgreSQL places NULL values after non-NULL values:

This corresponds to:

The opposite ordering can place NULLs first.

PostgreSQL also allows explicit control with:

and:

For example:

You should see:

Change the clause to:

and the four NULL rows move to the top:

So nullable columns do not automatically prevent B-tree indexes from participating in ordering.

When working with NULL, remember the different tools solve different problems:

And remember that NULL also affects:

The important point is:

`NULL` is not simply another value. Its meaning as missing or unknown information affects how queries, calculations, constraints, and comparisons behave.