Lower(null) comparison with string does not return row

Question

I have these two tables school_classes and classes.

school_classes
------------------------
id | class_id 
 1 |  2
 2 |  1

classes
------------------------
id | status    | name
 1 | null      | A
 2 | null      | B
 3 | Active    | C 
 4 | Cancelled | D

Here is the query I am trying,

select
    c.status, c.name
from
    school_classes sc
join classes c on
    sc.class_id = c.id and (LOWER(c.status) != 'cancelled')
where
 sc.id = 1

For above query it returns empty result. But my understanding that lower(null) will be 'null' which is not equal to 'cancelled' so it should return the row.

When I change the status from null to some other string then the row is returned.

`lower(null)` is `null`, and `null` is not equal to anything, or different to anything, even to other nulls. In short, unlike in programming languages, `null` is not a value. Therefore is not different from `'cancelled'` and the row should not be returned. — The Impaler, Feb 16 '22 at 14:28
@TheImpaler What is the fix? How I make it to filter only `cancelled` status and return the row even if the status is null? — confusedWarrior, Feb 16 '22 at 14:30
@a_horse_with_no_name's solution is the canonical solution according to the SQL Standard, that few engines actually implement. PostgreSQL does; isn't PostgreSQL great? — The Impaler, Feb 16 '22 at 14:57

score 1 · Answer 1 · answered Feb 16 '22 at 14:31

1

If you want to retrieve the nulls, you can check for them, as in:

select
    c.status, c.name
from
    school_classes sc
join classes c on
    sc.class_id = c.id
 and (LOWER(c.status) != 'cancelled' or c.status is null) 
where
 sc.id = 1

answered Feb 16 '22 at 14:31

The Impaler

45,731
9
39
76

Erwin Brandstetter · Accepted Answer · 2022-02-16T15:44:15.803

my understanding that lower(null) will be 'null' ...

It will be NULL, not 'null'. The missing quotes matter, signifying it's not a string but a genuine NULL value - or null, capitalization does not matter.
There are various ways to fix, IS NOT TRUE being possibly simplest:

AND (lower(c.status) = 'cancelled') IS NOT TRUE

Additional parentheses are optional since operator precedence works in our favor anyway.

See:

Best way to check for "empty or null value"

Lower(null) comparison with string does not return row

2 Answers2