No I am tired of this WHERE (a = b) or (a IS NULL AND b IS NULL)
Null should equal null like in every other programming language even SQL group by do null equals null which is even more inconsistent.
Null should equal null like in every other programming language even SQL group by do null equals null which is even more inconsistent.
Think of SQL's NULL like it's a NaN.
(Microsoft's SQL Server still defaults to the non-ANSI NULL behavior where a = b when both are NULL, and that's something that still pings on checklists of SQL Server if it follows ANSI standards. SQL Server is kind enough to let you enable/disable the behavior, and likely that would persist even after the default switches to meet the standard as the docs assure will happen "in some future version".)
NULL = Undefined or No Data. Whereas a blank field can, in and of itself, be data. It may indicated something is intentionally left blank.
But for those times where you want to consider them the same, it would be nice to have a setting.
(Note that I admit the possibility that this may exist already, like most my great ideas.)
CREATE INDEX idx ON tbl (coalesce(col, ''))