SOME/ANY/ALL

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Comments posted to this topic are about the item SOME/ANY/ALL

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • This was removed by the editor as SPAM

  • BrainDonor

    SSCoach

    Points: 19233

    I think it should have read 'if an additional row with NULL is inserted'.

    Just the pedant in me but it does give a different result if you replace one of the values inserted in the code with NULL.

    Steve Hall
    Linkedin
    Blog Site

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post, good one.

    I checked all the correct answers and at the end unchecked the last one... inserting the NULL row to the existing 6 data rows makes 7 rows... and the last option says "same amount of rows" so I thought 7rows (6 + 1 null row)... and I see now that "same amount of rows" indicates 6 rows only ... I got red.:-D

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    BrainDonor (8/18/2016)


    I think it should have read 'if an additional row with NULL is inserted'....

    +1

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Mike Hays

    SSCommitted

    Points: 1871

    Not crazy about how the question was formatted, however I did learn something.

  • Ed Wagner

    SSC Guru

    Points: 286982

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Didn't even know these existed. Thanks.

  • RonKyle

    SSC-Dedicated

    Points: 31482

    This was a good question, but I would have pushed off the NULLS until another day, especially in a multi select answer. It added a level of complexity for a largely unknown area.

  • George Vobr

    SSCrazy Eights

    Points: 9220

    Interesting question, thanks Bob. SET ANSI_NULLS OFF does not eliminate the column

    with the value NULL, therefore, Query1 returns 7 rows. Below I present a fragment

    of the example from the MSDN SET ANSI_NULLS (Transact-SQL):

    DECLARE @t1 TABLE (a INT NULL);

    DECLARE @varname int = NULL;

    INSERT INTO @t1 values (NULL),(0),(1);

    SET ANSI_NULLS ON;

    IF ( (32 & @@OPTIONS) = 32 )

    PRINT 'The ANSI_NULLS option turned on.'

    ELSE

    PRINT 'The ANSI_NULLS option turned off - returns NULL';

    SELECT a FROM @t1

    WHERE a = @varname;

    SELECT a FROM @t1

    WHERE a <> @varname;

    SET ANSI_NULLS OFF;

    IF ( (32 & @@OPTIONS) = 32 )

    PRINT 'The ANSI_NULLS option turned on.'

    ELSE

    PRINT 'The ANSI_NULLS option turned off - returns NULL';

    SELECT a FROM @t1

    WHERE a = @varname;

    SELECT a FROM @t1

    WHERE a <> @varname;

    GO

    Results

    --------

    (3 row(s) affected)

    The ANSI_NULLS option turned on.

    a

    -----------

    (0 row(s) affected)

    a

    -----------

    (0 row(s) affected)

    The ANSI_NULLS option turned off - returns NULL

    a

    -----------

    NULL

    (1 row(s) affected)

    a

    -----------

    0

    1

    (2 row(s) affected)

    See MSDN https://msdn.microsoft.com/en-us/library/ms188048.aspx

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    George, I read and understand what you are saying, but I have run the queries multiple times with ANSI_NULLs OFF and ANSI_NULLS on.

    * Query 1 returns 6 rows, with or without the addition of a seventh NULL row being added.

    Try running this

    SET ANSI_NULLS OFF;

    --SET ANSI_NULLS ON;

    CREATE TABLE #temp ( ID INT);

    INSERT INTO #temp

    VALUES ( 1 ),

    ( 2 ),

    ( 3 ),

    ( 4 ),

    ( 5 ),

    ( 2 ),

    (NULL);

    select * from #temp where null = ID

    select * from #temp where null >= ID

    We might argue with that this is inconsistent behavior, but for purposes of the QoTD, it is more important to know what the behavior actually is.

    I had hoped that specifying ANSI_NULLS ON in the example would keep people from having to consider that.

    To everyone else, you make valid points and I apologize for the inadvertent clumsiness of the wording. Thanks for the feedback.

    I probably should have saved the NULL behavior for a second QoTD.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    Since the option of not having ANSI NULLs will not be around for ever it's essential (and somewhat amusing) to remember that whether NULL is less than anything (or greater than anything, or equal to anything) is unknown although it certainly sorts as if it were equal to itself and less everything else.

    Tom

  • George Vobr

    SSCrazy Eights

    Points: 9220

    The Dixie Flatline (8/18/2016)


    George, I read and understand what you are saying, but I have run the queries multiple times with ANSI_NULLs OFF and ANSI_NULLS on.

    * Query 1 returns 6 rows, with or without the addition of a seventh NULL row being added.

    Try running this

    SET ANSI_NULLS OFF;

    --SET ANSI_NULLS ON;

    CREATE TABLE #temp ( ID INT);

    INSERT INTO #temp

    VALUES ( 1 ),

    ( 2 ),

    ( 3 ),

    ( 4 ),

    ( 5 ),

    ( 2 ),

    (NULL);

    select * from #temp where null = ID

    select * from #temp where null >= ID

    We might argue with that this is inconsistent behavior, but for purposes of the QoTD, it is more important to know what the behavior actually is.

    I had hoped that specifying ANSI_NULLS ON in the example would keep people from having to consider that.

    To everyone else, you make valid points and I apologize for the inadvertent clumsiness of the wording. Thanks for the feedback.

    I probably should have saved the NULL behavior for a second QoTD.

    Hello Bob,

    Thank You for Your post. I'm sorry that I'm in my post above, not emphasizing

    that the Query1 returns 7 rows only if Query 1's comparision is changed from (>=) to (=).

    Also, I forgot to quote MSDN, that SET ANSI_NULLS specifies ISO compliant behavior

    only of the Equals (=) and Not Equal To (<>) comparison operators when they

    are used with null values.

    So that explains it, that in Your query the statement:

    select * from #temp where null >= ID

    returns zero rows independently on SET ANSI_NULLS ON | OFF .

    Please, try yet in Your query to add these two commands:

    SELECT * FROM #temp WHERE ID <> NULL;

    SELECT * FROM #temp WHERE ID IS NULL;

    If You run this extended query, then the results depends on the setting of SET ANSI_NULLS

    and match the description in MSDN.

    Thanks again for Your feedback.

    I remain with best regards, G.V.

  • sknox

    SSChampion

    Points: 12292

    TomThomson (8/18/2016)


    ...

    (NULL) certainly sorts as if it were equal to itself and less everything else.

    That is because it's in the standard:

    SQL-92 section 20.2 <direct select statement: multiple rows> General Rule 5.b (p. 531)


    ...Whether a sort key value that is null is considered greater or less than a non-null value is implementation-defined, but all sort key values that are null shall either be considered greater than all non-null values or be considered less than all non-null values....

    (from http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

  • Kevin Gill

    SSCrazy

    Points: 2316

    Stewart "Arturius" Campbell (8/17/2016)


    Interesting question, Bob, thanks

    However, i noticed a slight inconsistency in the explanation, viz. When a NULL is inserted as a seventh row, Query 2 still returns 6 rows. The NULL row is not returned because a NULL ID will fail the >= comparisons against any value. Query 1 will return zero rows because all ID values will fail the >= comparison against the NULL row.

    i think mayhap you meant to state When a NULL is inserted as a seventh row, Query 1 still returns 6 rows. The NULL row is not returned because a NULL ID will fail the >= comparisons against any value. Query 2 will return zero rows because all ID values will fail the >= comparison against the NULL row.

    I also came here just to say this. I've never seen these before but I surmised correctly. 🙂

    -------------------------------
    Oh no!

Viewing 15 posts - 1 through 15 (of 19 total)

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