June 28, 2022 at 11:12 am
I've been here before on this but have come across it again, so wanted to re-visit it.
select case when 1 = NULL then 'Yes' else 'No' end --No as expected
select case when NOT (1 = NULL) then 'Yes' else 'No' end --also No!
Second result is correct in three-valued logic, NOT (UKNOWN) = UNKNOWN. I would argue this is not what most people would expect when converting some business rule to SQL. Not because stuck in the world of two-valued logic, rather that AND and OR ultimately treat UKNOWN as FALSE, and then NOT (UNKOWN) would return TRUE.
Perhaps I'm the only one with this issue but I'm going to avoid using NOT wherever possible, rewriting the logic.
June 28, 2022 at 11:15 am
Apologies for writing unknown several different ways!
June 28, 2022 at 11:46 am
I suggest you get out of the habit of writing = NULL. Instead, write IS NULL. NOT is very useful, do not discard it!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 28, 2022 at 12:02 pm
This both expected and documented behaviour: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver16#remarks
As it states, the following logic is used for NOT:
NOT(TRUE) = FALSE
NOT(FALSE) = TRUE
NOT(UNKNOWN) = UNKNOWN
As such, in your case expression you have CASE WHEN NOT(UNKNOWN) THEN 'Yes' ELSE 'No' END, and as UNKNOWN is *not* TRUE, then the expression in the ELSE ('No') is returned. If you need to deal with NULL values, then explicity deal with them using IS (NOT) NULL logic. Not using NOT because it treats NULL values correctly isn't the right choice here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2022 at 12:06 pm
I get it. Dealing with NULL is a pain. However, it's a unique value and has unique functions. Follow Phil's advice. Use IS NULL and IS NOT NULL instead of equals (or any other comparison). This because NULL isn't simply an empty set. It's literally unknown, AND, unknowable. Because of this, no value equals NULL and NULL can't equal NULL. It's a bit of a tricky pain in the bottom, but that's how it works. Trying to avoid it will only lead to difficulty & pain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2022 at 12:46 pm
I suggest you get out of the habit of writing = NULL. Instead, write IS NULL. NOT is very useful, do not discard it!
It's only an example to get a Boolean expression resulting in NULL. I have a situation where there is some quite involved logic, not just a column to test. It would be nice if could simply NOT it, rather than altering it.
June 28, 2022 at 1:21 pm
Phil Parkin wrote:I suggest you get out of the habit of writing = NULL. Instead, write IS NULL. NOT is very useful, do not discard it!
It's only an example to get a Boolean expression resulting in NULL. I have a situation where there is some quite involved logic, not just a column to test. It would be nice if could simply NOT it, rather than altering it.
So what would you propose NOT(UNKNOWN) would be? NOT(UNKNOWN) = KNOWN? What is a "known" in boolean? It can't be either TRUE or FALSE as UNKNOWN isn't opposite to either. UNKNOWN is very specifically neither TRUE or FALSE; you don't know if it's true or false and therefore the opposite also cannot be true or false. Things like WHERE 1 = NULL don't equal FALSE, they equalUNKNOWN, soNOT(1 = NULL)can't beTRUE.
This is just a mindset they you need to learn, and write your logic accordingly. Much like learning to write things using set-based methodology when writing SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2022 at 1:29 pm
Agree, NOT(UNKNOWN) cannot be anything apart from UNKNOWN.
It is a mindset thing, and when I write an AND or OR my mind says treat UNKNOWN as FALSE and that works for these logical operators that are finding TRUE cases. But this mindset doesn't work with NOT, so the risk of getting it wrong is much higher.
June 28, 2022 at 2:44 pm
my mind says treat UNKNOWN as FALSE
Nope, as people have stated about that. In this area, it's your mind that's wrong. NULL ("UNKNOWN") is obviously not FALSE or you wouldn't need NULL at all.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply