Compare when a value can be either NULL or text

Question

Now I know that you cannot directly compare NULL with everything (since the null value is unknown), since I could 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' can also be NULL.

Therefore, how do I compare these two? Both sides can be either text or NULL.

Answer 1

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 2

Use the IFNULL function for such cases.

i.e.

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

Answer 3

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

Answer 4

?

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

Answer 5

, :

(Column = @Value or (Column is null and @Value is null))

This leads to truth when both values ​​are equal. Ideally, we can reverse this statement to find the inequality, but the tri-core SQL logic violates this idea, sinceNOT(UNKNOWN) = UNKNOWN

--DO NOT USE, broken
NOT (Column = @Value or (Column is null and @Value is null))

Therefore, if we check only the value TRUEand deny it, we still get a readable operation.

CASE WHEN Column is null and @Value is null or Column = @Value THEN 1 ELSE 0 END = 0