SQL course · Module 2: Querying one table · Lesson 6 of 40
Quick answer: In SQL, NULL means a value is missing or unknown. It is not zero and not an empty string. You cannot find it with = NULL, because any comparison with NULL gives “unknown”, and WHERE keeps only rows where the condition is true. Use IS NULL and IS NOT NULL instead, replace NULL with a default using COALESCE(email, 'no email'), and never use NOT IN with a list that can contain NULL: it returns no rows.
In this lesson you will learn:
- what NULL means and how to make it visible in psql
- why
= NULLfinds nothing, and howIS NULLandIS DISTINCT FROMwork - three-valued logic: true, false and unknown
- the
NOT INtrap - how to use
COALESCE,NULLIFandCOUNT(column)
Before you start: Lesson 5, SQL WHERE Clause: Filter Rows with AND, OR, IN, BETWEEN and LIKE.
We ran every example on PostgreSQL 16.15 with the shop database from Lesson 3. The NULL rules shown here are described the same way in the current PostgreSQL 18 manual, so they work the same on PostgreSQL 17 and 18.
Table of Contents
What NULL means
Lesson 1 introduced NULL as an empty value. Two customers in shop have one: Emma Clarke has no email, and Hira Siddiqui has no city.
SELECT id, name, email, city FROM customers WHERE id IN (4, 5, 7) ORDER BY id;
id | name | email | city
----+---------------+---------------------------+---------
4 | bilal ahmed | bilal.ahmed@example.com | Karachi
5 | Emma Clarke | | London
7 | Hira Siddiqui | hira.siddiqui@example.com |
(3 rows)
By default psql prints nothing for NULL, “which can easily be mistaken for an empty string” (PostgreSQL docs: psql). The psql command \pset null shows a marker instead. It is a psql setting, not SQL, and it lasts until you quit psql.
shop=# \pset null '(null)'
Null display is "(null)".
shop=# SELECT id, name, email, city FROM customers WHERE id IN (4, 5, 7) ORDER BY id;
id | name | email | city
----+---------------+---------------------------+---------
4 | bilal ahmed | bilal.ahmed@example.com | Karachi
5 | Emma Clarke | (null) | London
7 | Hira Siddiqui | hira.siddiqui@example.com | (null)
(3 rows)
Now the missing values are clear. We keep this setting on for the rest of the lesson.
Why = NULL finds nothing
To find the customer without an email, a beginner writes:
SELECT id, name FROM customers WHERE email = NULL;
id | name
----+------
(0 rows)
No error, and no rows. To see why, compare NULL with something directly:
SELECT NULL = NULL AS null_equals_null, 5 = NULL AS five_equals_null, NULL <> 'Lahore' AS null_not_lahore;
null_equals_null | five_equals_null | null_not_lahore
------------------+------------------+-----------------
(null) | (null) | (null)
(1 row)
Every answer is NULL. The PostgreSQL docs explain: “Do not write expression = NULL because NULL is not ‘equal to’ NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)” (PostgreSQL docs: Comparison Operators). Is an unknown email equal to another unknown email? Nobody knows, so the answer is unknown.
IS NULL and IS NOT NULL
IS NULL is the correct test. It always answers true or false, never unknown.
SELECT id, name, email FROM customers WHERE email IS NULL;
id | name | email
----+-------------+--------
5 | Emma Clarke | (null)
(1 row)
SELECT id, name, city FROM customers WHERE city IS NOT NULL ORDER BY id;
id | name | city
----+--------------+---------
1 | Ayesha Malik | Lahore
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
10 | Omar Haddad | Lahore
(9 rows)
Nine customers have a city; Hira is the only one without.
Three-valued logic: true, false and unknown
“SQL uses a three-valued logic system with true, false, and null, which represents ‘unknown'” (PostgreSQL docs: Logical Operators). You can see all three values by putting a condition in the select list:
SELECT id, name, city, city <> 'Lahore' AS not_lahore FROM customers ORDER BY id;
id | name | city | not_lahore
----+---------------+---------+------------
1 | Ayesha Malik | Lahore | f
2 | Lucas Martin | Toronto | t
3 | Sofia Rossi | Milan | t
4 | bilal ahmed | Karachi | t
5 | Emma Clarke | London | t
6 | Daniel Weber | Berlin | t
7 | Hira Siddiqui | (null) | (null)
8 | NOAH WILSON | Chicago | t
9 | Chloe Dubois | Paris | t
10 | Omar Haddad | Lahore | f
(10 rows)
t is true and f is false. For Hira the answer is unknown. WHERE keeps only the rows where the condition is true, so it drops both the false rows and the unknown row. This is the surprise from Lesson 5:
SELECT id, name, city FROM customers WHERE city <> 'Lahore' ORDER BY id;
id | name | city
----+--------------+---------
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
(7 rows)
If Hira should be in the list, say so:
SELECT id, name, city FROM customers WHERE city <> 'Lahore' OR city IS NULL ORDER BY id;
id | name | city
----+---------------+---------
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
7 | Hira Siddiqui | (null)
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
(8 rows)
Standard SQL has a shorter way, and PostgreSQL supports it. IS DISTINCT FROM means “not equal, treating null as a comparable value” (PostgreSQL docs: Comparison Operators):
SELECT id, name, city FROM customers WHERE city IS DISTINCT FROM 'Lahore' ORDER BY id;
id | name | city
----+---------------+---------
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
7 | Hira Siddiqui | (null)
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
(8 rows)
AND, OR and NOT with unknown
SELECT NULL AND false AS null_and_false, NULL AND true AS null_and_true,
NULL OR true AS null_or_true, NULL OR false AS null_or_false, NOT NULL AS not_null;
null_and_false | null_and_true | null_or_true | null_or_false | not_null
----------------+---------------+--------------+---------------+----------
f | (null) | t | (null) | (null)
(1 row)
Think of NULL as “could be true or false”. NULL AND false is false whatever the unknown value is, and NULL OR true is true whatever it is. In every other case the answer stays unknown, and NOT unknown is still unknown. These match the truth tables in the PostgreSQL docs.
The NOT IN trap
NOT IN works as expected with a normal list:
SELECT id, name, city FROM customers WHERE city NOT IN ('Lahore', 'Karachi') ORDER BY id;
id | name | city
----+--------------+---------
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
(6 rows)
Add one NULL to the list, and every row disappears:
SELECT id, name, city FROM customers WHERE city NOT IN ('Lahore', 'Karachi', NULL) ORDER BY id;
id | name | city
----+------+------
(0 rows)
city NOT IN (a, b, NULL) means city <> a AND city <> b AND city <> NULL. The last part is always unknown, so the whole condition is never true. The docs warn that the result is then “null, not true as one might naively expect” (PostgreSQL docs: Row and Array Comparisons).
Nobody types NULL into a list on purpose. It happens when the list comes from a query. Here we look for customers who live outside the cities where our Pakistani customers live. The inner SELECT in brackets is a subquery; Lesson 20 explains them.
SELECT id, city FROM customers WHERE country = 'Pakistan' ORDER BY id;
id | city
----+---------
1 | Lahore
4 | Karachi
7 | (null)
10 | Lahore
(4 rows)
SELECT id, name, city FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE country = 'Pakistan')
ORDER BY id;
id | name | city
----+------+------
(0 rows)
Hira’s missing city put a NULL into the list, and the answer became empty. The fix is to keep NULL out of the list:
SELECT id, name, city FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE country = 'Pakistan' AND city IS NOT NULL)
ORDER BY id;
id | name | city
----+--------------+---------
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
5 | Emma Clarke | London
6 | Daniel Weber | Berlin
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
(6 rows)
Lesson 14 shows NOT EXISTS, which does not have this problem.
NULL in calculations
Arithmetic and text joining with NULL also give NULL:
SELECT 10 + NULL AS plus_null, 'Emma' || NULL AS text_plus_null;
plus_null | text_plus_null
-----------+----------------
(null) | (null)
(1 row)
|| joins text; Lesson 8 covers it.
COALESCE: replace NULL with a default
COALESCE “returns the first of its arguments that is not null” (PostgreSQL docs: Conditional Expressions):
SELECT id, name, COALESCE(email, 'no email') AS email, COALESCE(city, 'unknown') AS city
FROM customers WHERE id IN (4, 5, 7) ORDER BY id;
id | name | email | city
----+---------------+---------------------------+---------
4 | bilal ahmed | bilal.ahmed@example.com | Karachi
5 | Emma Clarke | no email | London
7 | Hira Siddiqui | hira.siddiqui@example.com | unknown
(3 rows)
It can take more than two values and stops at the first one that is not NULL:
SELECT id, name, COALESCE(email, city, 'no contact') AS contact FROM customers WHERE id IN (5, 7) ORDER BY id;
id | name | contact
----+---------------+---------------------------
5 | Emma Clarke | London
7 | Hira Siddiqui | hira.siddiqui@example.com
(2 rows)
COALESCE changes only the result. The table still holds NULL.
NULLIF: turn a value into NULL
NULLIF(a, b) “returns a null value if value1 equals value2; otherwise it returns value1”:
SELECT NULLIF('unknown', 'unknown') AS same, NULLIF('London', 'unknown') AS different;
same | different
--------+-----------
(null) | London
(1 row)
Its most common job is avoiding division by zero:
shop=# SELECT 100 / 0;
ERROR: division by zero
SELECT 100 / NULLIF(0, 0) AS safe_division;
safe_division
---------------
(null)
(1 row)
When the divisor is 0, NULLIF turns it into NULL, and dividing by NULL gives NULL instead of an error.
COUNT(*) and COUNT(column)
Lesson 9 covers aggregate functions, but one NULL rule belongs here. count(*) “computes the number of input rows”, while count(column) counts only rows “in which the input value is not null” (PostgreSQL docs: Aggregate Functions):
SELECT count(*) AS all_rows, count(email) AS with_email, count(city) AS with_city FROM customers;
all_rows | with_email | with_city
----------+------------+-----------
10 | 9 | 9
(1 row)
NULL in DISTINCT and ORDER BY
SELECT DISTINCT city FROM customers ORDER BY city;
city
---------
Berlin
Chicago
Karachi
Lahore
London
Milan
Paris
Toronto
(null)
(9 rows)
DISTINCT treats all NULLs as one value, and PostgreSQL sorts NULL last in ascending order. Lesson 7 shows how to move it with NULLS FIRST.
Dialect note. IS NULL, IS NOT NULL and COALESCE work in MySQL, SQLite and SQL Server too. For a NULL-safe “equal”, MySQL has <=>, which “is equivalent to the standard SQL IS NOT DISTINCT FROM operator” (MySQL 9.7 manual: Comparison Operators). SQLite accepts IS DISTINCT FROM and also its own short form IS NOT (SQLite: Expressions). SQL Server supports IS [NOT] DISTINCT FROM from SQL Server 2022 (Microsoft Learn: IS DISTINCT FROM). You will also meet two-argument versions of COALESCE: IFNULL in MySQL and SQLite, and ISNULL in SQL Server (MySQL 9.7 manual: Flow Control Functions, SQLite: Core Functions, Microsoft Learn: COALESCE).
Try it yourself
Exercise 1: Who has no email?
List the id and name of customers without an email, and show the word missing in the email column.
Exercise 2: Not in London
List customers who do not live in London. The customer with an unknown city must be in the result.
Exercise 3: Count the missing cities
In one query, show the number of customers, the number with a city, and the number without a city.
Solutions
Solution 1
SELECT id, name, COALESCE(email, 'missing') AS email FROM customers WHERE email IS NULL;
id | name | email
----+-------------+---------
5 | Emma Clarke | missing
(1 row)
IS NULL finds the row, and COALESCE replaces the NULL in the output.
Solution 2
SELECT id, name, city FROM customers WHERE city IS DISTINCT FROM 'London' ORDER BY id;
id | name | city
----+---------------+---------
1 | Ayesha Malik | Lahore
2 | Lucas Martin | Toronto
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
6 | Daniel Weber | Berlin
7 | Hira Siddiqui | (null)
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
10 | Omar Haddad | Lahore
(9 rows)
WHERE city <> 'London' OR city IS NULL gives the same nine rows.
Solution 3
SELECT count(*) AS customers, count(city) AS with_city, count(*) - count(city) AS without_city FROM customers;
customers | with_city | without_city
-----------+-----------+--------------
10 | 9 | 1
(1 row)
count(city) skips the NULL, so the difference is the number of missing cities.
Common mistakes
- Writing
= NULLor<> NULL. There is no error, just no rows. UseIS NULLorIS NOT NULL. - Forgetting that
<>skips NULL.WHERE city <> 'Lahore'leaves out customers with no city. AddOR city IS NULL, or useIS DISTINCT FROM. - Using
NOT INwith a list that can hold NULL. One NULL makes the result empty. Filter NULLs out of the list, or useNOT EXISTS(Lesson 14). - Mixing types in COALESCE.
SELECT id, COALESCE(city, 0) FROM customers;fails withERROR: COALESCE types text and integer cannot be matched. Use a default of the same type:COALESCE(city, 'unknown'). - Treating an empty string as NULL.
''is a real value. Inshop,SELECT count(*) FROM customers WHERE email = '';returns 0, because Emma’s email is NULL, not empty.
Frequently asked questions
Is NULL the same as zero or an empty string?
No. Zero is a number and '' is a text value with no characters; both are known values. NULL means the value is missing or unknown, which is why comparisons with it give unknown instead of true or false.
Why does NOT IN return no rows?
Because the list contains a NULL. x NOT IN (a, NULL) means x <> a AND x <> NULL, and x <> NULL is never true. Remove NULLs from the list with WHERE column IS NOT NULL, or rewrite the query with NOT EXISTS.
Does COUNT count NULL values?
COUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL. In shop, count(*) is 10 and count(email) is 9.
Summary
- NULL means missing or unknown; in psql,
\pset null '(null)'makes it visible. - Comparisons with NULL give unknown, and WHERE keeps only true rows, so use
IS NULL,IS NOT NULLorIS DISTINCT FROM. NULL AND falseis false andNULL OR trueis true; other combinations stay unknown.- A NULL inside a
NOT INlist makes the whole result empty. COALESCEgives a default for NULL,NULLIFturns a value into NULL, andCOUNT(column)skips NULLs.
Missing values are one of the first data-quality problems you meet in real tables, a topic that comes up again and again in the data engineer roadmap and the data analyst roadmap.
Next lesson
Next: Lesson 7, SQL ORDER BY: Sorting, NULLS FIRST/LAST and Paging with LIMIT/OFFSET.
Sources
- PostgreSQL documentation: Comparison Functions and Operators
- PostgreSQL documentation: Logical Operators
- PostgreSQL documentation: Row and Array Comparisons
- PostgreSQL documentation: Conditional Expressions (COALESCE, NULLIF)
- PostgreSQL documentation: Aggregate Functions
- PostgreSQL documentation: psql
- MySQL 9.7 Reference Manual: Comparison Functions and Operators
- MySQL 9.7 Reference Manual: Flow Control Functions
- SQLite: SQL Language Expressions
- SQLite: Core Functions
- Microsoft Learn: IS [NOT] DISTINCT FROM
- Microsoft Learn: COALESCE





