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


"NOT IN" Operator and Three Valued Logic


"NOT IN" Operator and Three Valued Logic

Author
Message
pchirags
pchirags
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2991 Visits: 616
Comments posted to this topic are about the item "NOT IN" Operator and Three Valued Logic
twin.devil
twin.devil
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8264 Visits: 2737
:-)

excellent QotD , things aren't so straight as they seems right :-P

thanks for sharing
Ford Fairlane
Ford Fairlane
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4024 Visits: 836
Great QotD - made me think and slow down before answering ( also ran the code to check what I thought MAY be the answer )

Hope this helps...

Ford Fairlane
Rock and Roll Detective





sqlnaive
sqlnaive
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8209 Visits: 2774
Excellent QOTD Chirag. :-) T.G.I.F.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24579 Visits: 7500
Nice one to end the week on, thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Rune Bivrin
Rune Bivrin
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4520 Visits: 1572
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.


Just because you're right doesn't mean everybody else is wrong.
Ford Fairlane
Ford Fairlane
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4024 Visits: 836
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.


The man does have a point....

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35672 Visits: 12784
Could have been a good question, but seems to have got a bit muddled somehow. The muddle is a pity, as there is at least one important lesson buried in there.

As Rune Bivrin pointed, 0 isn't actually a possible result from such a query. In addition, without even attempting to evaluate the where clause it is obvious that the first option isn't a possible result from such a query, because it is a set of one column rows and the query selects two columns, not one. So the only answer which isn't ruled out by elementary logic is the third answer, but the third answer is wrong because the SQL syntax is fine and there's nothing there that could cause an error to be raised. I guess the majority of people (58% or reponses to date) understand 3-valued logic and correctly worked out that the query would return no rows if the logic conformed to the 3-value logic rules as specified in the ANSI and ISO standards and assumed that "0" meant not a row containg "0" but "0 rows"; that's how I came to pick 0 as the answer, but it's not really satisfactory to be forced to guess what the option was intended to be in order to guess which anser will be considered correct, but option 2 is perhaps the "least wrong" answer - certainly if ANSI ULLs is ON.

Even worse, if the errors in options 1 and 2 were fixed the only correct answer would still be "it depends": if ANSI NULLS are OFF, the first answer is nearer to correct than the second one, because it then returns the correct number of rows, and the correct firstnames; so even if the first anser were corrected to show both columns, and the second one corrected to say "0 rows", the question would be ambiguous because which of those two answers was correct would depend on whether ANSI NULLs was set ON or OFF.

Finally, I reckon there could be a case for assuming that since options 1 and 2 don't say what they mean neither does option 3: it doesn't mean that SQL Server would return an error, it means that the person who wrote the query committed an error in using a NOT IN clause when the domain of the elements of the set did not preclude nulls; in my opinion that's not 100% justifiable, because we don't know how the query is used, we don't have the context, and there may be a context in which getting no rows when there is at least one null creates no problem because the result required is the set of rows which are known not to match, but that's just my opinion and I don't think it unreasonable for people to take the opposite view. So there's a plausible argument that the third option is just as correct as the second (assuming ANSI NULL is on).

What's the buried lesson? That people need to understand how three-valued logic works, understand how the result of tests involving NULL can be a bit startling if you try to think in terms of two-valued logic, and understand how to write queries that will do what they expect in an environment with NULLs, unless they are able to design their data in such a way that every column in every table has a NOT NULL constraint.

Tom

Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92315 Visits: 11138
Ford Fairlane (11/1/2013)
Rune Bivrin (11/1/2013)
Well, good question, but the correct alternative was a bit misleading. You don't get a zero back, you get an empty result set. That's a different answer.


The man does have a point....
Agreed. The point of the lesson, however, was a good one.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Thomas Abraham
Thomas Abraham
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4685 Visits: 2256
L' Eomot Inversé (11/1/2013)
Could have been a good question, but seems to have got a bit muddled somehow. The muddle is a pity, as there is at least one important lesson buried in there. ...


Agreed. Good inspiration for a question. Could have been implemented better. So, try again with another QotD.

A side note: I notice Tom has changed his avatar again. The downward glance while appearing to be walking makes you look like a member of the Peripatetic School. Beats sitting at a desk!

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
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