My understanding of the OR operator in TSQL is that if I have used OR then it will check conditions until it encounters the value TRUE. As soon as it gets TRUE it will not check the remaining conditions. Please see the example below wherein even if userid=58 is TRUE, it is still evaluating the other conditions. Create Table #MenuId ( UserId Int, GroupID Int, UserAccessMenuID Int ) Insert Into #MenuId (UserId,UserAccessMenuID) Values (58,1),(58,2),(58,3),(58,4) Insert Into #MenuId (GroupID,UserAccessMenuID) Values (4,1),(4,2) Select * from #MenuId where (UserId=58 Or GroupID=4) Drop Table #MenuId If UserId=58 is true then it should move out, but in above case it is checking both cases and returning all data. Is my understanding of the OR operator wrong or its is something else?
I understand what you are saying, but the order of evaluation is not necessarily in the order that you have written in the query. It depends on the underlying data, and more specifically the indexes and statistics on the columns that you are using. But from the example you've given I'm not sure what you are asking ?
I figured it out. Earlier I was under impression that once the OR operator gets a TRUE, it will not check the remaining conditions, but that is not correct. Even though it gets TRUE it will check further and if the second condition is also TRUE, the OR operator will show data for both conditions.
Just to make sure I understand, are you surprised that it is returning all rows? Remember that what you are effectively asking with that query is for the Server to return the set of all rows in that table that meet the condition. So, for any given row I think that it will stop evaluating that row once it knows that the row meets that condition. In this case, once it finds the row meets one part of the OR it will not check to see if it meets the other part of the OR. But then it moves on to the next row to check. As Kev said, the exact way it does this and finds the whole set can be somewhat more complicated depending on indexes, but the important thing for this purpose is that it is looking for the entire set of rows that meet the condition given. Now, there is a keyword [EXISTS] that probably comes closer to what you are looking for. That just checks to see if there is one row that meets the condition and will stop evaluating once it finds one row. This is normally used with subqueries and there is more detail on it at the MSDN article I linked to. :