dealing with NULL values in where statement (SQL)

Question

So I have query with the following where statement. Basically, I want to ensure there was no sports or player activity within the last 7 days.

WHERE......   
AND (( (SELECT max(sportswagerbyleague.asOfDate) 
            FROM sportswagerbyleague 
            WHERE sportswagerbyleague.userID = customer_profile.userID)  
                <= date(CURDATE()) - INTERVAL 7 DAY  ) 
                AND ((SELECT max(pa.txndate) 
                    FROM player_activity pa 
                    WHERE pa.userID = customer_profile.userID) <= date(CURDATE()) - INTERVAL 7 DAY ) )

However, if there is a NULL value (aka that user either has no player activity or sports activity), then the WHERE statement becomes false for that particular user. I want the WHERE statement to remain true even for a single NULL value.

Suggestions?

Striezel · Accepted Answer · 2016-08-17 20:22:28Z

1

Comparing a value against a NULL value is always false, so you have to handle that case separately. You could use something like IS NULL for that purpose.

answered Aug 17, 2016 at 20:22

Striezel

3,7867 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dnoeth · Accepted Answer · 2016-08-17 20:34:59Z

0

Switch to NOT EXISTS instead to avoid three-valued-logic:

WHERE......   
AND NOT EXISTS
 ( SELECT *
   FROM sportswagerbyleague 
   WHERE sportswagerbyleague.userID = customer_profile.userID  
     AND sportswagerbyleague.asOfDate > date(CURDATE()) - INTERVAL 7 DAY
 ) 
AND NOT EXISTS
 (
   SELECT *max(pa.txndate) 
   FROM player_activity pa 
   WHERE pa.userID = customer_profile.userID
     AND pa.txndate > date(CURDATE()) - INTERVAL 7 DAY
 )

answered Aug 17, 2016 at 20:34

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

2 Comments

EdwardC Over a year ago

any reason why it is " * " instead of "max(sportswagerbyleague.asOfDate) " or is it just a typo?

dnoeth Over a year ago

No type, aggregation like max(sportswagerbyleague.asOfDate) will always return a row (NULL if there's no matching row) , so NOT EXISTS will never be true. [NOT] EXISTS is the only place where * is actually recommended :-)

Collectives™ on Stack Overflow

dealing with NULL values in where statement (SQL)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related