Recently had trouble with some SQL. My tests would not pass, so I had to manually run it to debug it.

SELECT * FROM a JOIN b ON [...] WHERE b.foo NOTNULL

Yielded 0 of 3 rows. Expected 1

Tried querying WHERE b.foo ISNULL instead. Would have expected 2 of 3 rows, but got 1.

After googling i discovered that comparing with NULL does not return a true/false binary description, but may also return unknown if the type is not a NULL type, e.g. 42 ISNULL =" UNKNOWN

😳