NULL

  • EL Jerry

    SSCertifiable

    Points: 7053

    Thank you for the easy question.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • honza.mf

    SSCertifiable

    Points: 5519

    mtassin (8/15/2012)


    honza.mf (8/15/2012)

    And even worse the answer is incorrect with ANSI NULLS off.

    At leats this thankfully won't be an issue sooner or later. SET ANSI NULLS OFF will throw an error in a coming SQL Release.

    http://msdn.microsoft.com/en-us/library/ms188048.aspx

    Yes, but it's legal today. And sometimes one meets the yesterday.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks for an easy one!

  • Tee Time

    Hall of Fame

    Points: 3693

    One of the very first things I learned, and somehow still occasionally forget, even now. 😛

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    honza.mf (8/15/2012)


    Lokesh Vij (8/14/2012)


    rVadim (8/14/2012)


    I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.

    +1

    Yes that's correct!

    +1

    And even worse the answer is incorrect with ANSI NULLS off.

    Nope. The ANSI_NULLS option only affects the result of a comparison between two NULL values. With ANSI_NULLS on (the default, and in the future the only option), both NULL = NULL and NULL <> NULL evaluate to Unknown; with ANSI_NULLS off, they evaluate to True and False respectively.

    This setting has no impact at all on this question.

    EDIT: Please read the two following posts as well; the above explanation is not correct and a better explanation of why ANSI_NULLS does not affect the question is given in the next two messages.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • honza.mf

    SSCertifiable

    Points: 5519

    Hugo Kornelis (8/16/2012)


    honza.mf (8/15/2012)


    Lokesh Vij (8/14/2012)


    rVadim (8/14/2012)


    I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.

    +1

    Yes that's correct!

    +1

    And even worse the answer is incorrect with ANSI NULLS off.

    Nope. The ANSI_NULLS option only affects the result of a comparison between two NULL values. With ANSI_NULLS on (the default, and in the future the only option), both NULL = NULL and NULL <> NULL evaluate to Unknown; with ANSI_NULLS off, they evaluate to True and False respectively.

    This setting has no impact at all on this question.

    Sorry, I don't agree:

    set ansi_nulls off

    select 1 where 1 <> null



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    honza.mf (8/16/2012)


    Hugo Kornelis (8/16/2012)


    honza.mf (8/15/2012)


    Lokesh Vij (8/14/2012)


    rVadim (8/14/2012)


    I think any comparison against NULL will yield Unknown, which is not True, and therefore records won't be returned.

    +1

    Yes that's correct!

    +1

    And even worse the answer is incorrect with ANSI NULLS off.

    Nope. The ANSI_NULLS option only affects the result of a comparison between two NULL values. With ANSI_NULLS on (the default, and in the future the only option), both NULL = NULL and NULL <> NULL evaluate to Unknown; with ANSI_NULLS off, they evaluate to True and False respectively.

    This setting has no impact at all on this question.

    Sorry, I don't agree:

    set ansi_nulls off

    select 1 where 1 <> null

    Hmmm, interesting. Good, solid evidence. However, when I add SET ANSI_NULLS OFF or SET ANSI_NULLS ON to the code in the QotD, I does not affect the number of rows returned. So how do you explain that?

    (later)

    Found it!

    My previous post was indeed incorrect. I wrote it after I found that changing the ANSI_NULLS setting didn't affect the result (on SQL 2012). My first theory was that the deprecation path had made this an option that will be ignored, but I could not find anything in Books Online to support that theory. So I had to find another explanation. After reading Books Online and misinterpreting some text, I wrote the incorrect explanation in my first post.

    I should have read a bit further. On the page that describes SET ANSI_NULLS (see http://msdn.microsoft.com/en-us/library/ms188048.aspx), it says explicitly:

    "SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison."

    You may argue that this text does not literally apply here (there is no NULL variable or constant, but neither are both sides columns or compoud expressions). But the first part of the sentence is very clear: one of the operands has to be either the keyword NULL, or a variable with the NULL value. Not a column with the NULL value.

    So while my original explanation was completely wrong, my observation that SET ANSI_NULLS has no effect at all on this question was correct.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • honza.mf

    SSCertifiable

    Points: 5519

    Hugo Kornelis (8/16/2012)


    honza.mf (8/16/2012)


    Sorry, I don't agree:

    set ansi_nulls off

    select 1 where 1 <> null

    Hmmm, interesting. Good, solid evidence. However, when I add SET ANSI_NULLS OFF or SET ANSI_NULLS ON to the code in the QotD, I does not affect the number of rows returned. So how do you explain that?

    (later)

    Found it!

    My previous post was indeed incorrect. I wrote it after I found that changing the ANSI_NULLS setting didn't affect the result (on SQL 2012). My first theory was that the deprecation path had made this an option that will be ignored, but I could not find anything in Books Online to support that theory. So I had to find another explanation. After reading Books Online and misinterpreting some text, I wrote the incorrect explanation in my first post.

    I should have read a bit further. On the page that describes SET ANSI_NULLS (see http://msdn.microsoft.com/en-us/library/ms188048.aspx), it says explicitly:

    "SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison."

    You may argue that this text does not literally apply here (there is no NULL variable or constant, but neither are both sides columns or compoud expressions). But the first part of the sentence is very clear: one of the operands has to be either the keyword NULL, or a variable with the NULL value. Not a column with the NULL value.

    So while my original explanation was completely wrong, my observation that SET ANSI_NULLS has no effect at all on this question was correct.

    Yes, this is OK, sorry for the first mistake.

    Either way, ANSI NULLS off are an evil.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • marlon.seton

    SSCrazy

    Points: 2623

    Stewart "Arturius" Campbell (8/15/2012)


    Good back-to-basics question, thanks.

    However, as others have stated, the explanation is lacking a wee bit.

    Is a wee bit an eighth of a wee byte?

  • Paul White

    SSC Guru

    Points: 150442

    It drives me nuts to see column definitions without a NULL or NOT NULL constraint.

  • g_one_2020

    Grasshopper

    Points: 11

    Hi,

    Sorry i tried, but failed to understand what u r saying...

    I tried with the below query

    CREATE TABLE #table1

    (c1 INT, c2 INT)

    INSERT INTO #table1 VALUES(1,2),(2,NULL),(3,2),(4,NULL),(5,3),(6,4)

    select * from #table1 where c2<>2

    As per the post, NULL may be NOT EQUAL to 2, so we should get rows with NULL values as well...

    But, I'm not getting the rows with NULL values. Just getting rows with c2=3 and 4.

    Please make me understand here. This may be very basic....:)

  • sandeep rawat

    Ten Centuries

    Points: 1317

    Hi Use

    Not exist .

    as it uses contain search.

    http://msdn.microsoft.com/en-us/library/ms188336.aspx

    AND

    <> (Not equal or =equal) uses match search .

    NULL is undefined data that is why in match search it will never come.

    SET ANSI_NULLS is deprecated in 2005 ..

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    g_one_2020 (8/30/2012)


    Hi,

    Sorry i tried, but failed to understand what u r saying...

    I tried with the below query

    CREATE TABLE #table1

    (c1 INT, c2 INT)

    INSERT INTO #table1 VALUES(1,2),(2,NULL),(3,2),(4,NULL),(5,3),(6,4)

    select * from #table1 where c2<>2

    As per the post, NULL may be NOT EQUAL to 2, so we should get rows with NULL values as well...

    But, I'm not getting the rows with NULL values. Just getting rows with c2=3 and 4.

    Please make me understand here. This may be very basic....:)

    I just typed a lengthy reply - and then got an error when trying to post it. And the "back" button on my browser gave me a different error screen instead of returning to the screen where I had typed my post, so I was unable to use copy and paste to try to post another time. I reallly hate it when that happens - please, site owners, FIX THIS!!!

    I don't have the time to type that whole response again, so instead I will just refer you to my series of blog posts where I explain everything about NULLs:

    1. NULL - The database's black hole

    2. The logic of three-valued logic

    3. Dr. Unknown, or how I learned to stop worrying and love the NULL

    4. What if null if null is null null null is null?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104773

    SQL Kiwi (8/21/2012)


    It drives me nuts to see column definitions without a NULL or NOT NULL constraint.

    What drives me nuts is that SQL doesn't default to NOT NULL when neither NULL nor NOT NULL is specified; defaulting to NULL, which for any schema competent designer is the exceptional case, is crazy (as is defaulting to exception generally).

    Quite horrifyingly, I ticked the wrong point for this one. I think an insufficiency of either alcohol or of cafeine (or maybe of both) made me point the mouse at the wrong hole. I noticed while clicking submit, too late to not click.

    Tom

Viewing 15 posts - 16 through 30 (of 31 total)

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