4 ms·
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 equa
by SigmundA 7y ago
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.
- mumblemumble 7y agoLetting 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.
- SigmundA 7y agoAlready behaves differently in GROUP BY, UNION, DISTINCT and PARTITION.
- stetrain 7y agoSounds like a disaster when trying to correlate Ids/keys between tables.
- bloomer 7y agoNULL is similar to floating point NaN (aka not a number) which also has the same comparison operation NaN != NaN.
- trts 7y agowhere coalesce(a, '') = coalesce(b, '')
- SigmundA 7y agoNormally can't use an index as every value has to be coalesced before comparison.
- danaliv 7y agoYou 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.
- Tactic 7y agoMy 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.)
- SigmundA 7y agoIn MSSQL you have SET ANSI_NULLS OFF to do this.
- WorldMaker 7y agoSomething 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".)
- zvrba 7y agoActually 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.
- progval 7y ago> Null should equal null like in every other programming language Think of SQL's NULL like it's a NaN.
- jeltz 7y agoIt 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 https://modern-sql.com/feature/is-distinct-from