SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BETWEEN a hard place and a rock


BETWEEN a hard place and a rock

Author
Message
Dana Medley
Dana Medley
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3162 Visits: 1707
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! Hehe



Everything is awesome!
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17610 Visits: 7425
This was an interesting question, thanks Hugo
Also shows that NULLs can cause interesting (and usually unexpected) results.Cool

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
pchirags
pchirags
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2719 Visits: 616
Nice Question Hugo..
Thanks..
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65156 Visits: 17979
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!!! :-P

_______________________________________________________________

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 Modens 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)
sestell1
sestell1
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3594 Visits: 3512
Nice question Hugo!

Unfortunately I fell for the trap in the title.
Evidentally I have more studying to do of the RCS isolation level. Ermm
paul s-306273
paul s-306273
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3551 Visits: 1169
Ha ha.
Very funny!


Sad
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26513 Visits: 12506
Caught again! :-D

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

sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4125 Visits: 2935
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


Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19201 Visits: 12426
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
Bangla
Bangla
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1885 Visits: 180
Nice one....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search