It is quite wordy but the SQL solution for this is "WHERE a IS NOT DISTINCT FROM b".

https://modern-sql.com/feature/is-distinct-from

> Null should equal null like in every other programming language

Think of SQL's NULL like it's a NaN.

Something to petition ANSI about.

(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".)

Actually I find this a very practical behavior. It allows me to have NULLs in columns participating in UNIQUE indexes. At least with indexes, it's very intuitive and useful.

My preference would be a flag that you can set per connection.

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.)

In MSSQL you have SET ANSI_NULLS OFF to do this.

NULL is similar to floating point NaN (aka not a number) which also has the same comparison operation NaN != NaN.

Sounds like a disaster when trying to correlate Ids/keys between tables.

Letting null equal null might work okay for a WHERE clause, but it turn JOINing into a terrible muddle. And I don't want null to behave different ways in different clauses.

Already behaves differently in GROUP BY, UNION, DISTINCT and PARTITION.

where coalesce(a, '') = coalesce(b, '')

Normally can't use an index as every value has to be coalesced before comparison.

You can index the results of functions (or other expressions) in at least some databases. PostgreSQL can do this, for example.

  CREATE INDEX idx ON tbl (coalesce(col, ''))

Combining this with UNIQUE can make for some neat tricks.