NULL and three-valued logic

NULL means there is no value

The old archive holograms have gaps: a cell fails to glow, leaving a patch of darkness.

At first, it looks like an error. Was the value left out? Should there be a zero? An empty string?

But the Archive is honest. When it doesn't know the answer, it doesn't invent one.

NULL in SQL means there is no value.

Not "the value equals zero".
Not "the value is an empty string".
Not "an error occurred".
It means exactly this: the value is missing or unknown.

For example, here is a users table:

SELECT id, name, city
FROM users;

The result:

At first glance, rows 2 and 4 look alike: neither Boris nor Gleb seems to have a city. But SQL treats them as two different situations.

Boris has NULL in city — his city is unknown or was never entered.

Gleb may have an empty string '' in city — that is still a value, just a string containing zero characters.

That's an important difference.

NULL  -- there is no value
''    -- there is a value, but it's empty text
0     -- there is a value, and it's the number zero

NULL is not a value. It's a special marker: the value is missing.

QUERY: Respect the gaps in memory. A database that honestly says "I don't know" is more reliable than one that confidently lies.

NULL, zero, and an empty string are different things

Beginners often think of NULL as "empty" and then wonder why their queries behave strangely.

Let's work through a simple example.

Say the products table has a discount_percent column:

What does each row mean?

The first product has a 10% discount.

The second product has a 0% discount. That means the discount is known to be zero.

The third product's discount is NULL. This means we don't know the discount: it may not have been specified, calculated, or loaded yet.

In other words:

discount_percent = 0

and

discount_percent IS NULL

represent two different states.

The same goes for text.

city = ''

means the city is recorded as an empty string.

city IS NULL

means the cell has no value at all.

In real databases this matters. An empty string might come from a form where someone deleted the text and submitted a blank field. A NULL might mean the field was never completed, the data hasn't arrived from another service yet, or the value is genuinely unknown.

Why = NULL doesn't work

Now for the biggest pitfall.

It might seem that you could find users with no city by writing this:

SELECT id, name, city
FROM users
WHERE city = NULL;

But a query like that won't find the rows with NULL.

The reason is that NULL isn't an ordinary value. You can't compare it with = the way you compare a number or a string.

Here's roughly how SQL evaluates it:

city = 'Москва'

SQL can evaluate this. If city holds 'Москва', the answer is TRUE. If it holds 'Казань', the answer is FALSE.

Now consider this:

city = NULL

SQL can't return TRUE or FALSE, because NULL means "the value is unknown".

If the city is unknown, can we say it equals NULL? No.
Can we say it doesn't equal NULL? Also no.

SQL's answer is UNKNOWN.

In other words, SQL doesn't know.

SQL conditions can produce three logical results, not just two:

TRUE
FALSE

There's a third:

UNKNOWN

This is called three-valued logic.

WHERE keeps only TRUE results

Here's the key point.

WHERE keeps a row only when the condition evaluates to TRUE.

If the condition evaluates to FALSE, the row is discarded.

If the condition evaluates to UNKNOWN, the row is discarded too.

So the rule for WHERE is:

That is exactly why this query:

SELECT id, name, city
FROM users
WHERE city = NULL;

doesn't return users whose city is missing.

For a row where city is NULL, the expression:

city = NULL

evaluates to UNKNOWN, not TRUE.

And WHERE keeps only TRUE rows.

How to check for NULL properly

NULL has its own dedicated operators:

column IS NULL

and

column IS NOT NULL

To find users whose city is missing:

SELECT id, name, city
FROM users
WHERE city IS NULL;

To find users whose city is present:

SELECT id, name, city
FROM users
WHERE city IS NOT NULL;

Remember the main rule:

-- wrong
WHERE city = NULL

-- wrong
WHERE city <> NULL

-- right
WHERE city IS NULL

-- right
WHERE city IS NOT NULL

IS NULL doesn't compare a value with NULL. It asks a different question:

Is the value in this cell missing?

And IS NOT NULL asks:

Does this cell contain a value?

Why <> can surprise you too

Another common mistake involves the <> operator.

Say you need to find every user who isn't from Moscow.

You might write:

SELECT id, name, city
FROM users
WHERE city <> 'Москва';

You might expect the query to return:

• users from Kazan
• users from other cities
• users whose city is missing

But the rows with NULL won't be included.

Why?

For a row where the city is 'Казань':

city <> 'Москва'

yields TRUE.

For a row where the city is 'Москва':

city <> 'Москва'

yields FALSE.

For a row with NULL:

city <> 'Москва'

yields UNKNOWN.

And WHERE keeps only TRUE rows.

So the row with the missing city is filtered out.

To include users whose city is missing, you have to add that condition explicitly:

SELECT id, name, city
FROM users
WHERE city <> 'Москва'
   OR city IS NULL;

Now the logic reads like this:

Show me everyone whose city isn't Moscow, plus everyone whose city is missing.

PostgreSQL also has a handy operator:

WHERE city IS DISTINCT FROM 'Москва'

It handles NULL explicitly and always returns either TRUE or FALSE.

For example:

NULL IS DISTINCT FROM 'Москва'

returns TRUE.

And:

NULL IS NOT DISTINCT FROM NULL

returns TRUE.

For now, keep this basic rule in mind: when you need to work with a missing value, use IS NULL and IS NOT NULL.

Careful with NOT, AND, and OR

NULL can be especially surprising in complex conditions.

Look at this condition:

WHERE NOT (city = 'Москва')

At first glance, it looks equivalent to:

WHERE city <> 'Москва'

For ordinary values, the two expressions are equivalent.

But if city is NULL, the expression:

city = 'Москва'

yields UNKNOWN.

Here's the important part:

NOT UNKNOWN

yields UNKNOWN as well.

Not TRUE.

So a row with NULL is still filtered out.

An example:

SELECT id, name, city
FROM users
WHERE NOT (city = 'Москва');

Rows with a missing city are still filtered out.

To include them, you have to account for that case explicitly:

SELECT id, name, city
FROM users
WHERE city <> 'Москва'
   OR city IS NULL;

This rule is worth remembering:

NOT does not turn UNKNOWN into TRUE.

In SQL, UNKNOWN remains unknown even when negated.

COALESCE: a fallback for missing values

Sometimes a NULL in the data is perfectly normal, but showing it directly in a report can be confusing.

For example:

SELECT id, name, city
FROM users;

The result:

Within the database, NULL has a precise meaning. In an interface or report, however, it's better to display a clear label:

city not specified

That's what COALESCE is for.

COALESCE returns the first argument that isn't NULL.

SELECT COALESCE(NULL, NULL, 'fallback value');

The result:

Let's apply it to the table:

SELECT
    id,
    name,
    COALESCE(city, 'city not specified') AS city_for_report
FROM users;

The result:

Important: COALESCE doesn't change the data in the table.

It changes only what the query returns.

Boris still has NULL stored in the table. The query simply displays a reader-friendly label in its place.

It's like placing a note in an empty display case: the case is still empty, but now anyone looking at it knows why.

COALESCE can pick from several options

COALESCE can take as many arguments as you need.

It checks the arguments from left to right and returns the first one that isn't NULL.

For example, a table may store several contact methods for each user:

We want to show the best available contact method: Telegram if present, then email, then phone. If none is available, we'll show the text "no contact".

SELECT
    id,
    name,
    COALESCE(telegram, email, phone, 'no contact') AS best_contact
FROM users;

The result:

The logic goes like this:

take telegram
if telegram is NULL — take email
if email is NULL — take phone
if phone is NULL — take 'no contact'

This is a very common pattern in reports, exports, and interfaces.

One important technical detail: the arguments of COALESCE must have compatible data types.

This is fine:

COALESCE(city, 'city not specified')

because both arguments are text.

But this one may raise an error:

COALESCE(discount_percent, 'no discount')

If discount_percent is a number and 'no discount' is text, the database may not be able to resolve a single result type.

In that case, explicitly convert the number to text first:

COALESCE(discount_percent::text, 'no discount')

NULL in SELECT and NULL in WHERE behave differently

It's important to distinguish between two situations.

In SELECT you can see NULL in the result:

SELECT id, name, city
FROM users;

Here NULL simply appears in an output cell. Different SQL editors may show it in different ways: as NULL, as an empty cell, or as a special marker.

In WHERE, on the other hand, NULL affects the filtering:

SELECT id, name, city
FROM users
WHERE city <> 'Москва';

Here the row with NULL may be filtered out because the condition evaluates to UNKNOWN.

So SELECT shows the data, while WHERE determines whether a row is included.

For WHERE, what matters isn't how the cell is displayed, but whether the condition evaluates to TRUE.

Common mistakes with NULL

Mistake 1. Looking for NULL with =

WHERE city = NULL

Don't write it this way. This condition won't find the rows with NULL.

The right way:

WHERE city IS NULL

Mistake 2. Looking for non-NULL values with <> NULL

WHERE city <> NULL

Don't write it this way either. This condition won't find rows that contain a value.

The right way:

WHERE city IS NOT NULL

Mistake 3. Assuming that <> includes NULL

WHERE city <> 'Москва'

This query will find rows where the city is definitely not Moscow. But it won't return the rows where the city is unknown.

If you need both cities other than Moscow and missing cities:

WHERE city <> 'Москва'
   OR city IS NULL

Mistake 4. Confusing an empty string with NULL

WHERE city = ''

This looks for an empty string, not for NULL.

To find both cases:

WHERE city = ''
   OR city IS NULL

With messy data you sometimes have to check for both.

Mistake 5. Using COALESCE and assuming the data has changed

SELECT COALESCE(city, 'city not specified') AS city
FROM users;

This query changes only how the result is displayed.

It doesn't write 'city not specified' into the table.

To change the stored data, you need UPDATE, but that's a separate operation with different consequences.

QUERY: Absence tells you something too. The trick is not to mistake it for a value.