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.

CodePudding user response：

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"

CodePudding user response：

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