CODEWITHSUNDEEP
mysqlsql
OferP
OferP

Reputation: 393

mysql when sub-query return null

I try to do the the following query with mysql (Should add more conditions, but simplified it for the question, so the sub-query sometimes return null and sometimes return value, this is just for making the query shorter for the question):

SELECT COUNT(*)
FROM table
WHERE date = (SELECT MAX(date) FROM table)

My problem is that if the sub-query return null, my result will be 0, which is not the desired result. Since I can't do IS instead of =, I'm wondering if there is a simple solution.

Upvotes: 4

Views: 2857

Answers (4)

Kenneth Vogt
Kenneth Vogt

Reputation: 995

You can make a union of both results and calculate the max:

SELECT MAX(datecount) FROM
(SELECT COUNT(*) AS datecount FROM table WHERE date IS NULL 
UNION ALL
SELECT COUNT(*) AS datecount FROM table WHERE date = (SELECT MAX(date) FROM table)) AS derivedtable

Upvotes: 0

spencer7593
spencer7593

Reputation: 108480

MySQL provides a NULL safe equality comparison <=> (spaceship) operator.

I suspect that if you replace the = equality comparison operator with the NULL safe equality comparison operator, the query will return the results it looks like you are after.

This expression:

  a <=> b

is basically shorthand equivalent for: 

  a = b OR ( a IS NULL AND b IS NULL )

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270573

This should not be a concern.

The subquery only returns NULL if there are no rows or if date is NULL in all rows.

The query will return 0 in this case. That makes sense to me. What would you want it to return?

Upvotes: 0

james_bond
james_bond

Reputation: 6906

To count all the rows with null date this should work:

SELECT COUNT(*) FROM table WHERE date IS NULL;

Upvotes: 0

Related Questions