Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

BETWEEN a hard place and a rock Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 6:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 1,991, Visits: 1,496
batgirl (6/12/2013)
More proof that one should not attempt QOTD before the morning dose of caffeine.


+1 Great question Hugo. I completely overlooked the hint in the title. I also seemed to have overlooked half the question too!




Everything is awesome!
Post #1462604
Posted Wednesday, June 12, 2013 7:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
This was an interesting question, thanks Hugo
Also shows that NULLs can cause interesting (and usually unexpected) results.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1462617
Posted Wednesday, June 12, 2013 7:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 1,848, Visits: 590
Nice Question Hugo..
Thanks..
Post #1462635
Posted Wednesday, June 12, 2013 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
Excellent question Hugo. I think the most disturbing thing is that at the time of this posting 38% of responders think there are rows that between -1 and -10!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1462657
Posted Wednesday, June 12, 2013 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 1,422, Visits: 2,444
Nice question Hugo!

Unfortunately I fell for the trap in the title.
Evidentally I have more studying to do of the RCS isolation level.
Post #1462659
Posted Wednesday, June 12, 2013 9:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:32 AM
Points: 1,417, Visits: 809
Ha ha.
Very funny!


Post #1462689
Posted Wednesday, June 12, 2013 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 7,928, Visits: 9,653
Caught again!

The trouble with having a programming language with elements that are loosely based on natural language is that the people who devise them produce hard and fast rules that don't actually match English particularly well. That is something that SQL and COBOL have in common.

I tend not to use "between" when writing SQL, partly because I (like pretty well everyone else I know) am likely to misinterpret it when reading code. The more questions we get like this the more likely I am to start remembering to check when I see between, which would be a good thing, so for me it's a good question.


Tom
Post #1462706
Posted Wednesday, June 12, 2013 10:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:41 PM
Points: 1,382, Visits: 1,753
There is a bug in your answer. You say:

This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.


That should read:

This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn not equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.


In order for the final query to return the rows with NULL in them, the second query has to return the ones that are not null, for them to be excepted from the first query, which includes all rows. More to the point, WHERE NOT (1 between -1 and -10) will pass the row, because NOT (FALSE) evaluates to true, while WHERE NOT (NULL between -1 and -10) will cull the row, because NOT (UNKNOWN) evaluates to unknown which is treated as not true. So query 2 will pass not-null rows, which will then be excepted, which will leave the null rows to be returned.

Here's a complete test harness (using a table variable instead of an actual table, which eliminates the red herrring of the isolation level):
declare @MyTable table (NumericColumn int, TextColumn char(5));

insert @MyTable
values
(NULL,'NULL1'),
(NULL,'NULL2'),
(NULL,'NULL3'),
(NULL,'NULL4'),
(NULL,'NULL5'),
(1,'VAL1'),
(2,'VAL1'),
(3,'VAL1'),
(4,'VAL1'),
(5,'VAL1');

--1: all rows
select *
from @MyTable

--2: rows to be excepted
select *
from @MyTable where not (NumericColumn between -1 and -10)

--3: full query
select *
from @MyTable
except
select *
from @MyTable where not (NumericColumn between -1 and -10)

and the results:

Query 1:
NumericColumn	TextColumn
NULL NULL1
NULL NULL2
NULL NULL3
NULL NULL4
NULL NULL5
1 VAL1
2 VAL1
3 VAL1
4 VAL1
5 VAL1

Query 2:
NumericColumn	TextColumn
1 VAL1
2 VAL1
3 VAL1
4 VAL1
5 VAL1

Query 3:
NumericColumn	TextColumn
NULL NULL1
NULL NULL2
NULL NULL3
NULL NULL4
NULL NULL5

Post #1462727
Posted Wednesday, June 12, 2013 11:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 6,133, Visits: 8,396
sknox (6/12/2013)
There is a bug in your answer. You say:

This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.


That should read:

This means that the first query returns all rows from MyTable, the second only the rows with NumericColumn not equal to NULL. The EXCEPT removes the second set from the first, leaving the rows with NumericColumn equal to NULL in the result set. Apparently, there are five rows with NumericColumn = NULL.

Thanks for catching that missing "not", sknox! You are completely right.
(And thanks for the great demo code too!)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1462761
Posted Thursday, June 13, 2013 12:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:04 AM
Points: 917, Visits: 167
Nice one....
Post #1462909
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse