Compare when value could be both NULL or text

Question

Now I know you can't directly compare NULL to anything (as null is unknown) so how would I achieve the following:

select  *
    from    Material as m
    where   MtrlCode = 826 and
            Exposlimit <> 'compareMe'

Where Exposlimit MAY be NULL or it may not be. 'compareMe' may also be NULL.

Therefore how do I compare the two? Both sides could be either text or NULL.

Answer 1

select  *
    from    Material as m
    where   (MtrlCode = 826 or MtrlCode IS NULL)  and
            (Exposlimit <> 'compareMe' or Exposlimit IS NULL)

Answer 2

Use IFNULL function for such cases.

i.e.

WHERE IFNULL(FieldA, 'MagicConstant') = IFNULL(FieldB, 'MagicConstant')

Answer 3

select  * 
from    Material as m 
where   MtrlCode = 826 
    and (Exposlimit <> 'compareMe'
         or (Exposlimit is null and compareme is not null) 
         or (Exposlimi is not null and compareme is null))

Answer 4

Did you try this?

select  *
    from    Material as m
    where   MtrlCode = 826 and
            Exposlimit IS NOT NULL AND 'compareMe' IS NOT NULL AND Exposlimit <> 'compareMe'