CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
m.edmondson
m.edmondson

Reputation: 30922

Compare when value could be both NULL or text

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.

Upvotes: 5

Views: 1520

Answers (5)

Mitch
Mitch

Reputation: 22301

Consider that it is easier to find equality:

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

Which results in true when both values are equal. We would ideally be able to negate this statement to find inequality, but the tri-state logic of SQL breaks that idea, as NOT(UNKNOWN) = UNKNOWN

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

Therefore, if we check for only the TRUE value, and negate it, we still end up with a readable operation.

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

Upvotes: 1

bla
bla

Reputation: 5480

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'

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171559

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))

Upvotes: 5

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

Use IFNULL function for such cases.

i.e.

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

Upvotes: 2

Naeem Sarfraz
Naeem Sarfraz

Reputation: 7430

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

Upvotes: 1

Related Questions