How to Compare Two Values When One Is Null

SQL Tutorial Home

In standard SQL , compare expression involving null values is tricky. The first point to mention is that the only way to compare a column with null is by using the is null or is not null operator. Other important point to mention is that the result of a boolean comparison between two columns can return 3 values: true, false, or unknow(or null).

What is unknow ?, sometimes, when one of the columns has a null value, then the result of the comparison is unknow(or null). Let’s see

select * from test
name1 name2
John John
John Peter
John null 
Select name1 = name2 as comparison_result, count(*) from test group by 1;
comparison_result count
true 1
false 1
null 1

Postgres provides an operator called is distinct from which is null aware, so the following query:

Select name1 is distinct from name2 as comparison_result, count(*) 
from test 
group by 1;
comparison_result count
true 2
false 1

The previous result is a proof that the operator is distinct from is null aware.

IN THIS PAGE