NULL and three-valued logic — Retrieving and Filtering Data

Objective

By the end of this lesson you can find and exclude missing values with IS NULL and IS NOT NULL, predict how NULL behaves in comparisons, AND, OR and NOT, keep NULL rows in 'not equal' filters with IS DISTINCT FROM, and replace NULL for display with COALESCE.

Why it matters: Every real table has missing values: customers without an email, employees with no termination date, refunds not processed yet. Most silent filtering errors in SQL come from forgetting that a comparison with NULL is neither true nor false.

Dataset: Wadi Market online store. Examples run in the editor on the right.

NULL is a marker meaning no value is known here. It is not zero, not an empty string '', and not the word 'NULL'. A customer with a NULL email may have no email, or may have one we never recorded: SQL can't tell, so it treats the value as unknown.

That has a big consequence. Is an unknown email equal to 'a@example.com'? Unknown. Is it different from it? Also unknown. So every ordinary comparison with NULL (=, <>, <, >, LIKE, IN) produces a third truth value, UNKNOWN, instead of TRUE or FALSE. Even NULL = NULL is UNKNOWN: two unknown values aren't known to be equal.

And the rule that matters most: WHERE keeps a row only when the condition is TRUE. FALSE and UNKNOWN are both dropped.

x	y	x = y	x <> y
'Riyadh'	'Riyadh'	TRUE	FALSE
'Riyadh'	'Jeddah'	FALSE	TRUE
'Riyadh'	NULL	UNKNOWN	UNKNOWN
NULL	NULL	UNKNOWN	UNKNOWN

x = y

x <> y

'Riyadh'

TRUE

FALSE

'Riyadh'

'Jeddah'

FALSE

TRUE

'Riyadh'

NULL

UNKNOWN

NULL

UNKNOWN

Any comparison with NULL is UNKNOWN, not true or false. WHERE keeps only rows where the condition is TRUE, so UNKNOWN rows disappear. That's why city = NULL never matches anything: write city IS NULL.

Explore the truth tables. Any comparison with NULL gives UNKNOWN; AND, OR and NOT then combine TRUE, FALSE and UNKNOWN.

Finding missing values: IS NULL

Because email = NULL is always UNKNOWN, it returns no rows, ever, without an error. SQL has a dedicated test that always answers TRUE or FALSE:

column IS NULL is TRUE when the value is missing.
column IS NOT NULL is TRUE when a value is present.

WHERE email IS NULL          -- missing
WHERE email IS NOT NULL      -- present
WHERE email = NULL           -- always UNKNOWN: returns nothing

Never write = NULL or <> NULL.

ExampleCustomers with no city

SELECT customer_id, first_name, last_name, city, region
FROM customers
WHERE city IS NULL;

A handful of customers never gave their city. Their region is NULL too, because region is derived from the city. Replace IS NULL with = NULL and the query returns zero rows, with no error to warn you.

Three-valued logic in AND, OR and NOT

When UNKNOWN meets AND and OR, think of it as could be either:

FALSE AND UNKNOWN is FALSE (one false side is enough), but TRUE AND UNKNOWN is UNKNOWN.
TRUE OR UNKNOWN is TRUE (one true side is enough), but FALSE OR UNKNOWN is UNKNOWN.
NOT UNKNOWN is still UNKNOWN.

The last line explains a surprise: a filter and its opposite don't add up to the whole table. WHERE coupon_code = 'WELCOME10' and WHERE NOT (coupon_code = 'WELCOME10') together miss every order with no coupon, because for those rows both conditions are UNKNOWN.

ExampleThe not-equal trap

SELECT order_id, ordered_at, coupon_code
FROM orders
WHERE ordered_at >= '2025-12-24'
  AND coupon_code <> 'WELCOME10';

You might expect every recent order except the WELCOME10 ones. Instead you get only a handful of rows: orders that used some other coupon. Most orders have no coupon at all, and NULL <> 'WELCOME10' is UNKNOWN, so they were all dropped.

IS DISTINCT FROM: a NULL-safe comparison

To say different from, treating NULL as an ordinary value, PostgreSQL has IS DISTINCT FROM. It is never UNKNOWN: NULL IS DISTINCT FROM 'WELCOME10' is TRUE, and NULL IS DISTINCT FROM NULL is FALSE. Its partner IS NOT DISTINCT FROM is a NULL-safe =. The longer equivalent works in every database: (coupon_code <> 'WELCOME10' OR coupon_code IS NULL). Note the brackets: without them, the OR would escape the date condition.

ExampleEvery recent order except WELCOME10

SELECT order_id, ordered_at, coupon_code
FROM orders
WHERE ordered_at >= '2025-12-24'
  AND coupon_code IS DISTINCT FROM 'WELCOME10';

Now the orders with no coupon are included, so the list is many times longer than the previous one. Only orders that actually used WELCOME10 are excluded.

ExampleCurrent employees at Masar Holding

SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE termination_date IS NULL
  AND hire_date >= '2025-06-01';

Masar Holding HR

In HR data, a NULL termination_date means the person still works here. That's a NULL with a business meaning rather than a data-entry gap. The result lists employees hired since June 2025 who haven't left.

A first look at COALESCE

For display, you often want a placeholder instead of NULL. COALESCE(a, b, …) returns the first argument that is not NULL: COALESCE(city, 'Unknown') shows 'Unknown' when city is missing. It's also the fix for arithmetic that a NULL would otherwise wipe out (price - COALESCE(discount, 0)). The cleaning track covers COALESCE and NULLIF in depth.

ExamplePlaceholders for missing contact details

SELECT customer_id, first_name,
       COALESCE(city, 'Unknown')            AS city,
       COALESCE(email, phone, 'no contact') AS best_contact
FROM customers
WHERE city IS NULL OR email IS NULL;

Only customers missing a city or an email are listed. COALESCE fills the gaps in the output: 'Unknown' for a missing city, and the phone number when the email is missing (or 'no contact' if both are). The stored data stays NULL.

Quick check

A row has region = NULL. Which condition is TRUE for it?

Now practise

4 exercises on real data, checked in your browser (practice).