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 |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2006-02-07 : 23:16:06
|
I am running following query on my database.select distinct p.UnivID, p.GivenName AS GivenName, p.Surname AS Surname, p.Sex AS Sex from [dbo].[Episode] as e, [dbo].[Patients] as p where p.GivenName like '%john%' or p.Surname like '%john%' and e.UnivID = p.UnivID and e.TumourID = 10 When I run above query I get 136 rows as result.But when I put bracket across GivenName and Surname comparison, I get only 26 rows as result.select distinct p.UnivID, p.GivenName AS GivenName, p.Surname AS Surname, p.Sex AS Sex from [dbo].[Episode] as e, [dbo].[Patients] as p where (p.GivenName like '%john%' or p.Surname like '%john%') and e.UnivID = p.UnivID and e.TumourID = 10 whats the difference between these two??thanks,ujjaval |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-07 : 23:22:12
|
Refer to Operator Precedence in Books OnLinequote:
Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level: + (Positive), - (Negative), ~ (Bitwise NOT)* (Multiply), / (Division), % (Modulo)+ (Add), (+ Concatenate), - (Subtract)=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)NOTANDALL, ANY, BETWEEN, IN, LIKE, OR, SOME= (Assignment)
The AND operation will be evaluated before the ORSo your first query is effectively select distinct p.UnivID, p.GivenName AS GivenName, p.Surname AS Surname, p.Sex AS Sex from [dbo].[Episode] as e, [dbo].[Patients] as p where p.GivenName like '%john%' or (p.Surname like '%john%' and e.UnivID = p.UnivID and e.TumourID = 10) ----------------------------------'KH' |
 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-07 : 23:22:50
|
| Hi Ujjaval,In the Second dtatement you have specified the scope of OR operator. i.e get all the records where (givenname or surname is like John ) and matching universityid and with tour id = 10whereas in the first statement it fetches all the records with (Givenname like john) or(surname like john and matching universityid and with tour id = 10) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-07 : 23:27:17
|
| Just one more point. Should always use bracket '(' and ')' for clarity and to avoid uncertainty in the operator precedence.----------------------------------'KH' |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2006-02-07 : 23:34:12
|
| thanks both of you for your reply. That makes sense. |
|
|
|
|
|
|
|
|
OR operator in select query