December 26, 2014 at 8:58 am
Jamie-2229 (12/26/2014)
In my experience, MICROSOFT SQL SERVER application for NOT IN and IN is rarely applied the way it is intended.
This is what is so odd about what you are stating. Please read this prior to any response you may make.
http://en.wikipedia.org/wiki/SQL
Thank you. Notice that all the behaviors you are blaming "MICROSOFT SQL SERVER" for are standard and common to SQL as a Language.
There are places where the M$ TSQL does not follow the ANSI SQL standard.
MHO: The IN operator and using the NOT qualifier with it are "NOT IN" that set. Not even if you add to how they handle NULLS and 3VL.
December 26, 2014 at 5:09 pm
PHYData DBA (12/26/2014)
There are places where the M$ TSQL does not follow the ANSI SQL standard.MHO: The IN operator and using the NOT qualifier with it are "NOT IN" that set. Not even if you add to how they handle NULLS and 3VL.
That is correct. ANSI standard defined IN as a series of OR'ed comparisons, so WHERE x IN (1, NULL) has to have the exact same effect as (x=1 OR x=NULL). For x equal to 1 this equates to True OR Unknown, which yields True; the row is returned. For x is NULL this equates to Unknown OR Unknown (because any comparison with a NULL yields Unknown, even if both operands are NULL), is Unknown. And for x = 2, this is False OR Unknown, also Unknown. Those two rows are not returned.
Also, ANSI defined x NOT IN (...) as equal to NOT (x IN (...)), so WHERE x NOT IN (1, NULL) must behave identical as WHERE NOT (x IN (1, NULL)). The inner part of that expression will, as explained above, return True for x = 1 and Unknown for x is null or any value other than 1. NOT (True) is False and NOT (Unknown) is Unknown. The NOT IN expression returns False for x = 1 (so the row is not returned). For x equal to any other value or NULL, the expression returns Unknown, so those rows, too, are skipped.
Every database product that behaves different is in violation of the ANSI standard. I have never used other products, but I would be very surprised if any of the big competitors would have a non-standard behaviour by default in this area.
To my chagrin, SQL Server does have an option (SET ANSI_NULLS OFF) that forces SQL Server to use different logic for comparing with NULL. This setting has been deprecated a long time ago, and will be removed in a future version - much to my delight, because I don't like non-standard behaviour.
December 27, 2014 at 12:43 pm
I didn't think I was actually blaming Microsoft. I may not be very clear when I state the deal as it appears to me. What I was trying to get at was, perhaps the reason so many folks think it is ok to use the In and Not In operators, may be its behavior in Oracle is efficient. But more to the point, I don't know how it behaves in Oracle and didn't wish to say folks were wrong if they'd had the experience in Oracle with a more efficient capability for using In and Not In.
Nice to see it in printing, that it is Or'd. Makes sense why this operator is so inefficient. Thanks for clearing it up.
Jamie
December 28, 2014 at 2:17 pm
Jamie-2229 (12/26/2014)
I'll be the first to admit I took a sharp right turn here.Still, very valid point ... "NOT IN" and "IN" opens itself up to SQL abuse.
An alternative view is that anyone who fails to understand how 3-valued logic works should be forbidden to write any SQL until they have learnt to understand it. The SQL standard is very clear that NULL exists, that the logic used by SQL is 3-valued logic, and that every comparison with NULL returns neither TRUE nor FALSE but UNKNOWN. As long as one pays attention to those facts, one won't get unexpected results from using IN and NOT IN.
One may of course get terrible performace if one uses IN or NOT IN inappropriately. The same is true of INNER JOIN, of LEFT JOIN, of RIGHT JOIN, of FULL JOIN and of many other features of SQL. So why suggest that the rather useful (when used properly) IN construct should be thrown away but not suggest that the equally dangerous JOIN constructs should be thrown away too?
Tom
December 28, 2014 at 2:25 pm
Tom,
Mostly, by way of English language flow. It is simple to say "Show me the stuff that isn't there or show me what you've got IN there." No one says, "show me the stuff that you would join to that set that will limit it in the way that I expect". Or at least not anyone I've heard speak. Maybe Sheldon from "The Big Bang Theory". 🙂
Jamie
December 29, 2014 at 7:25 am
Jamie-2229 (12/28/2014)
Tom,Mostly, by way of English language flow. It is simple to say "Show me the stuff that isn't there or show me what you've got IN there." No one says, "show me the stuff that you would join to that set that will limit it in the way that I expect". Or at least not anyone I've heard speak. Maybe Sheldon from "The Big Bang Theory". 🙂
LOL - When asking were something is in SQL you should pretend you are talking to Sheldon and not to Congress (home of no value logic). SQL is a platform and application independent standard based on math. Anyone with knowledge of that understands comparisons in Set Theory and 3 value logic play out the same way everywhere, not just in SQL. Computers do Math. They don't go to social functions, win Ms. America, or join in with polite society. 😎
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply