How to Compare Two Values When One is Null in Redshift

Imagine you're comparing two Redshift columns and you want to know how many rows are different. No problem, you think:

select count(1)
from items
where width != height;

Not so fast. If some of the widths or heights are null, they won't be counted! Surely that wasn't your intention. In other databases, there are null-aware comparison operators you can use, like PostgreSQL's is distinct from. In Redshift, you can achieve similar functionality by using the coalesce() function.

select count(1)
from items
where coalesce(width, 0) != coalesce(height, 0);

Now, your count will be "null aware" and you'll get the result you want 💥

database icon
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and Redshift