0

To be clear I understand why it technically doesn't work as it's following the behavior defined in the ANSI spec. But what I don't understand is why this is a desirable behavior. Further, the existence of IS NULL, to me at least, seems like a kludge to fix the fact that @x = null is always false.

So why would you want to design a language such that checking if anything equals null fails and forcing users to use IS NULL instead? Besides legacy compatibility what would be a scenario where checking if a field or parameter = null would lead to ambiguous or erroneous results?

Legion
  • 3,922
  • 8
  • 51
  • 95
  • Please read https://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-mean and Microsoft documentation about [ANSI_NULLS](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017) – Alex Yu Feb 04 '19 at 18:47
  • 1
    NULL is not treated as a value you can not do an = or != on it. https://www.tutorialspoint.com/sql/sql-null-values.htm – Brad Feb 04 '19 at 18:48
  • 2
    SQL uses three valued logic. It is not always false. It is `unknown` – Martin Smith Feb 04 '19 at 18:48
  • It can work and not work depending on settings – Alex Yu Feb 04 '19 at 18:49
  • @AlexYu Good to know, but SET ANSI_NULLS is being deprecated. Since it's going to be always ON going forward, the question still remains why this is desirable. – Legion Feb 04 '19 at 18:51
  • @MartinSmith I've read this, but I still don't understand why this is a desirable design choice. I have never found the inability to check if something is equal to NULL without IS NULL useful and in fact makes my WHERE clauses cumbersome. – Legion Feb 04 '19 at 18:53
  • @Legion You're mistaken: ["In a future version of SQL Server, ANSI_NULLS will be ON and any applications that explicitly set the option to OFF **will** generate an error"](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-2017) – Alex Yu Feb 04 '19 at 18:58
  • @AlexYu I'm not sure how I'm mistaken. That's the sentence I'm referring to. I consider it being always on and trying to set it to off throwing an error to be de facto deprecated, ie. you can't use it. – Legion Feb 04 '19 at 19:03
  • @Legion Ah, I see. It's just a question of terminology. In practice deprecation of such features usually postponed from version to version and even than they are rarely goes into complete inexistence. – Alex Yu Feb 04 '19 at 19:11
  • Ask the ANSI standards committee. – Brian Feb 04 '19 at 20:48
  • Its because NULL isnt a value. It means unknown, which put another way means "could be anything". So if I have one car that is red, but the colour hasn't been recorded, and another that is blue but also hasn't been recorded, then saying car1.colour=car2.colour would be incorrect. – TomC Feb 05 '19 at 00:29
  • @TomC I see what you're saying but I'd be inclined to say car1.color = car2.color if they are both unknown. Once the color is recorded for one or the other car then car1.color <> car2.color. – Legion Feb 05 '19 at 16:48

0 Answers0