NOT and NULL

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

  • Apologies for writing unknown several different ways!

  • I suggest you get out of the habit of writing = NULL. Instead, write IS NULL. NOT is very useful, do not discard it!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

  • kuopaz wrote:

    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

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

  • kuopaz wrote:

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    kuopaz wrote:

    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.

    I'll give it a good talking to.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply