June 12, 2013 at 9:15 am
Ha ha.
Very funny!
🙁
June 12, 2013 at 9:47 am
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
June 12, 2013 at 10:38 am
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:
NumericColumnTextColumn
NULLNULL1
NULLNULL2
NULLNULL3
NULLNULL4
NULLNULL5
1VAL1
2VAL1
3VAL1
4VAL1
5VAL1
Query 2:
NumericColumnTextColumn
1VAL1
2VAL1
3VAL1
4VAL1
5VAL1
Query 3:
NumericColumnTextColumn
NULLNULL1
NULLNULL2
NULLNULL3
NULLNULL4
NULLNULL5
June 12, 2013 at 11:53 am
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!)
June 13, 2013 at 12:14 am
Nice one....
June 13, 2013 at 4:51 am
Interesting question Hugo, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2013 at 9:42 am
This is another question from Hugo that is tough. And of course there is a good explanation followed by enhanced explanation in the thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 13, 2013 at 10:19 am
Tooke me over an hour to figure this one out. Thanks, Hugo!
June 17, 2013 at 1:38 am
Coming after a long time and started with this question :w00t:
how come -1 and -10 didn't span the whole number line !!! I got caught in the first option; I see a reason - it did not show the author name.
I guess I should have used the other half of the brain to solve this one.
Thanks for the question Hugo. I have been thinking on creating questions on Isolation levels, well I guess nothing beats this one; partly though, it solves the purpose.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
June 17, 2013 at 12:48 pm
Nicely subtle, I'm afraid I didn't get it.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
June 19, 2013 at 2:26 am
I found this one easy because we had another BETWEEN <high value> <low value> question a little while ago and that put me on the right track straight away.
June 21, 2013 at 2:42 am
marlon.seton (6/19/2013)
I found this one easy because we had another BETWEEN <high value> <low value> question a little while ago and that put me on the right track straight away.
Agreed. I got it right straight away using that old (not so) QOTD reference. 🙂
June 21, 2013 at 11:08 pm
marlon.seton (6/19/2013)
I found this one easy because we had another BETWEEN <high value> <low value> question a little while ago and that put me on the right track straight away.
+1
June 26, 2013 at 2:08 am
Nice question!
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply