Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
burt
Starting Member
4 Posts |
 Posted - 2014-07-07 : 02:34:16
|
| Documentation sources indicate that the AND operator is evaluated before ALL, ANY, BETWEEN, IN, LIKE, OR, and SOME. How can the AND operator that is used to combine conditions be evaluated before operators such as IN, BETWEEN, and LIKE that are used within conditions? It seems impossible to combine conditions before the conditions have been evaluated individually. What would that even mean? It almost makes some sense with IN, because that could be rewritten instead using = and OR. However, BETWEEN is logically equivalent to >= and <= combined with AND, in which case it should have equal precedence to another AND, not lower precedence. Meanwhile, LIKE would be extremely difficult to rewrite in terms of its equivalent OR statements.Any insight anybody could provide on this would be appreciated.BTW, one source of documentation showing operator precedence can be found here....http://msdn.microsoft.com/en-us/library/ms190276.aspxThanks in advance.Cheers,Burt |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-07 : 09:20:20
|
| You may be confusing evaluation precedence with how SQL actually does the work on a real box. The two are not the same! Think of it like this: All the basic conditions are logically evaluated simultaneously, then the boolean result is computed according to the precedence chart. That doesn't mean that SQL actually executes it like that (though it may be able to given enough cores!) |
 |
|
|
burt
Starting Member
4 Posts |
Posted - 2014-07-07 : 14:34:36
|
| Hello Gerald,Thank you for your response.Not sure I understand what you are saying. If the precedence the documentation refers to is not actually used by SQL, then why is it documented? Does it have any significance that we can actually see in any results?Thanks,Burt |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-08 : 08:55:13
|
The precedence is used by SQL to determine the logical result of the predicate. However, that doesn't mean that SQL will evaluate things in that order. e.g.declare @FirstDate datetime = '2014-06-01'declare @SecondDate datetime = '2013-06-01'if @FirstDate between '2014-01-01' and '2015-01-01' and @SecondDate between '2013-01-01' and '2013-12-31' select 'between' Clearly SQL cannot evaluate the AND before it evaluates the BETWEENs. First, it parses the whole expression into an evaluation tree. Then it traverses the tree to determine if it is TRUE or FALSE overall. While traversing the tree, it may (and does in this sample) need to evaluate a leaf-node or two (the BETWEENs in this example) in order to determine the truthiness of the branch of the overall tree. However, that is why I suggested you consider the lowest-level expressions (the leaf nodes of the tree; the BETWEENS in my example) as being all evaluated simlutaneously. Then SQLs Boolean engine can evaluate the overall tree by the documented precedence order. The overall truth or falsehood of the entire expression (predicate) is evaluated according to the precedence order. Consider an example from real life:You say to your friend, "Let's have lunch together if we're both hungry and we have time." AND has the highest precedence, in that both conditions must be true for lunch together to happen. However, when you're evaluating this, you have to stop and evaluate the lower-level conditions to make the final determination. SQL must do the same thing.Unfortunately the examples in the article you cited are all (integer) arithmetic. It's a pity Boolean algebraic expression examples are not shown. |
|
|
|
burt
Starting Member
4 Posts |
Posted - 2014-07-08 : 23:27:23
|
| Hello again. I appreciate your effort to explain, but I am not sure we are getting very far. I am looking for some tangible significance to the AND being evaluated before the BETWEENS. If you provided this in your explanation, I missed it.You have included additional steps, such as parsing. That does not change the part I am focused on. Which happens first, the AND or the BETWEEN? If the AND happens first, what does that mean logically?You say the BETWEENS (the leaf nodes) are evaluated simultaneously, then SQLs Boolean engine can evaluate the overall tree by the documented precedence order. To me, that sounds like you are saying the BETWEENS happen first, which is exactly the opposite of the documented precedence order. So it cannot do that, and then follow the documented precedence order, which it would have already violated. I guess I am not understanding you correctly. I apologize, I am not trying to put words in your mouth, I just do not understand what you are saying.Oh, also, you say that a Boolean algebraic expression would provide a good illustration. Perhaps you could be kind enough to provide an example, because I am unclear what you have in mind.BTW, I am a long-time Oracle user and fairly new to SQL Server. So out of curiosity I just checked, and it looks like the Oracle documentation shows operators such as LIKE, IN, and BETWEEN as being higher precedence than AND and OR, which to me makes a lot more sense. So the fact that Oracle and SQL Server apparently handle this differently adds to the mystery a little. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-07-09 : 09:22:47
|
Actually I did provide an example (previous post). The Betweens are evaluated first. They have to be, otherwise it is impossible to evaluate the AND. The Documented precedence order is correct once the results to all the atomic and subexpressions are known. When you see a Boolean expression like: (1 = 2) AND (1 = 1) OR (2 = 2) you can see at a glance that the overall expression evaluates to TRUE. However, in any programming language (SQL, C++, VB, Haskell, whatever you like), it is impossible to evaluate the AND if the results of the subexpressions are not known. AND is evaluated first, but while evaluating AND, SQL (and any other language) says, "Hey! I need to know the values of either side of the AND to finish my evaluation!" So, it evaluates the subexpressions as part of evaluating the AND. It's just a normal in-order tree traversal.I agree that the documentation could be better, but don't get hung up on it. Normal rules of (human) logic apply as well. |
|
|
|
|
|
|
|
|
trying to understand operator precedence