Comparing NULLs in SQL

Consider the following table "mm_mailtable", containing the following data:

id	name	toname	whensent	subject	haveread	newmail
27999	William	Joe	2018-03-13 12:41:25	Golf next week?	1	0
28000	William	Linda	2018-03-13 12:41:25	I'm out!	1	0
28001	Linda	William	2018-03-13 12:41:25	Okay!	1	0
28002	Joe	William	2018-03-13 12:41:25	Sure thing!	1	1
28003	Jim	William	2018-03-13 12:41:25	The house	1	NULL

Also consider the following query I found in our source code somewhere.

SELECT *
FROM mm_mailtable
WHERE toname = :NAME
AND newmail = COALESCE(:NEWMAIL, newmail);

Let's say we try the query out with "William" as NAME and 0 as NEWMAIL.

We get an old mail to William with "Okay!" as subject.

Let's say we try the query out with "William" as NAME and 1 as NEWMAIL.

We get a new mail to William with "Sure thing!" as subject.

Let's say we try the query out with "William" as NAME and NULL as NEWMAIL.

Now I would expect to get all three entries returned, but the one containing the NULL value for "newmail" is not returned.

It takes some getting used to, but comparing null values is not possible in most databases, as it is undefined (a.k.a.. NULL).

See for more and better explanations the references below.

P.S. in this case, in our database, the column "newmail" should have been defined as "NOT NULL" and given a "DEFAULT" value, to prevent this sort of thing. Apparently it was forgotten.

Rewriting the query

This should work:

SELECT * 
FROM mm_mailtable 
WHERE toname = :NAME
AND (:NEWMAIL is null OR :NEWMAIL = newmail);

References

StackOverflow - why is null not equal to null false

https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false

Baron Schwartz's Blog - Why NULL never compares false to anything in SQL

https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/