Author 
Topic 

sapator
Constraint Violating Yak Guru
Greece
346 Posts 
Posted  07/02/2014 : 07:00:14

It is very hard to google this as it is a common term. I show a sproc that did
WHERE ( table1.Linked = @onReference)
OR ( table1.Linked IN ((SELECT table1.tab
 etc
Does or search for an alternative if the variable @onReference is null? Or what exactly does it do. I would appreciate the msdn documentation if you are bored to explain here. Thanks. 

gbritton
Constraint Violating Yak Guru
412 Posts 
Posted  07/02/2014 : 07:45:23

It's Boolean algebra. The WHERE clause is satisfied if any of the expressions separated by OR evaluate to true. So, in the example you gave, rows would be returned from the query if one of these two expressions evaluate to true:
1. table1.Linked = @onReference 2. table1.Linked IN ((SELECT table1.tab ...etc.
Documentation can be found here: http://msdn.microsoft.com/enus/library/ms173545.aspx 


webfred
Flowing Fount of Yak Knowledge
Germany
8760 Posts 
Posted  07/02/2014 : 10:32:41

...and if both expressions evaluate to true :)
I don't want to bother you Gerald  I was just in the mood to post this... :)
Too old to Rock'n'Roll too young to die. 
Edited by  webfred on 07/02/2014 10:35:00 


gbritton
Constraint Violating Yak Guru
412 Posts 
Posted  07/02/2014 : 11:35:34

quote: Originally posted by webfred
...and if both expressions evaluate to true :)
Of course, if both expressions evaluate to true then one of them surely does! 


ScottPletcher
Constraint Violating Yak Guru
USA
340 Posts 
Posted  07/02/2014 : 12:03:53

Almost certainly both expressions will never evaluate to true ... because once one of them does, SQL has no need to evaluate the other one.
That is, for the expression: ( A = B ) OR ( C = D ) If A=B is "true", the final result will always be "true", no matter what the result of C=D is. To save overhead, most optimizers simply skip  the technical term is "shortcircuit"  the "second" comparison in those cases.
Note, though, that SQL could choose to do either comparison first, not necessarily in the order written. Also, in some cases, both might be evaluated anyway due to multithreading "preprocessing". 


gbritton
Constraint Violating Yak Guru
412 Posts 
Posted  07/02/2014 : 13:04:54

"Almost certainly both expressions will never evaluate to true "
By SQL? Probably. But when I say, "if both expressions evaluate to true" I mean mathematically. No SQL required!
Also you're caution about relying on shortcircuiting is correct. Never count on SQL to short circuit. 


sapator
Constraint Violating Yak Guru
Greece
346 Posts 
Posted  07/03/2014 : 02:28:23

Hi.My problem here is what will be returns? So if, for example, the WHERE ( table1.Linked = @onReference) is false, it will be ignored from the query? What will "table.linked =" received value will be, or it will be like there is no WHERE ( table1.Linked = @onReference) and we get our value from OR ( table1.Linked IN ? And above that, what if both are true? Will precedence of WHERE ( table1.Linked will take over? Thanks. 


gbritton
Constraint Violating Yak Guru
412 Posts 
Posted  07/03/2014 : 08:40:37

The returns depend on the Where clause. If either side of the 'OR' is true for a row, the row is returned. Think about an example from life:
Your friend asks you to go for coffee tomorrow and you say, "I'll have coffee with you if I'm not running late or I'm really tired".
Under what conditions will you have coffee with your friend? When you're not running late? When you're tired? When you're not running late and you are tired?
SQL's OR works the same way. And, perhaps just like you, it's irrelevant which condition is considered first. 



Topic 
