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.
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 )
To count all the rows with null date this should work:
SELECT COUNT(*) FROM table WHERE date IS NULL;
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?
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
