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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1696
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8979 Visits: 7281
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.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2355 Visits: 615
Nice Question Hugo..
Thanks..
Sean Lange
Sean Lange
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: 26297 Visits: 17553
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2553 Visits: 3463
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2436 Visits: 1115
Ha ha.
Very funny!


Sad
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14394 Visits: 12213
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 2832
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11082 Visits: 12001
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.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1697 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