August 17, 2016 at 8:16 pm
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
August 17, 2016 at 11:10 pm
This was removed by the editor as SPAM
August 18, 2016 at 4:14 am
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.
August 18, 2016 at 4:15 am
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.
August 18, 2016 at 5:13 am
Not crazy about how the question was formatted, however I did learn something.
August 18, 2016 at 5:33 am
I learned something new today, so thank you.
August 18, 2016 at 5:52 am
Didn't even know these existed. Thanks.
August 18, 2016 at 7:10 am
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.
August 18, 2016 at 7:47 am
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
August 18, 2016 at 9:47 am
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
August 18, 2016 at 10:50 am
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
August 18, 2016 at 6:33 pm
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.
August 19, 2016 at 2:43 pm
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)
August 23, 2016 at 9:51 am
Stewart "Arturius" Campbell (8/17/2016)
Interesting question, Bob, thanksHowever, 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 18 total)
You must be logged in to reply to this topic. Login to reply