January 14, 2004 at 3:10 am
Hello All,
can anybody tell me the precedance of AND, OR, (, ), NOT operators predance in MS SQL server? Please do it fast I need this very urgently.
Regards,
Mahesh
January 15, 2004 at 12:53 am
From BOL searching on AND:
Hope is helps.
When a query is executed, it evaluates first the clauses linked with AND, and then those linked with OR.
Note The NOT operator takes precedence over both AND and OR.
For example, to find either employees who have been with the company for more than five years in lower-level jobs or employees with middle-level jobs without regard for their hire date, you can construct a WHERE clause such as the following:
WHERE hire_date < '01/01/90' AND job_lvl = 100 OR job_lvl = 200 To override the default precedence of AND over OR, you can put parentheses around specific conditions in the SQL pane. Conditions in parentheses are always evaluated first. For example, to find all employees who have been with the company more than five years in either lower or middle-level jobs, you can construct a WHERE clause such as the following:
WHERE hire_date < '01/01/90' AND (job_lvl = 100 OR job_lvl = 200)Tip It is recommended that, for clarity, you always include parentheses when combining AND and OR clauses instead of relying on the default precedence.
Understanding how AND and OR clauses are related when combined can help you construct and understand complex queries in the Query Designer.
If you link multiple conditions using AND, the first set of conditions linked with AND applies to all the conditions in the second set. In other words, a condition linked with AND to another condition is distributed to all the conditions in the second set. For example, the following schematic representation shows an AND condition linked to a set of OR conditions:
A AND (B OR C)The representation above is logically equivalent to the following schematic representation, which shows how the AND condition is distributed to the second set of conditions:
(A AND B) OR (A AND C)This distributive principle affects how you use the Query Designer. For example, imagine that you are looking for all employees who have been with the company more than five years in either lower or middle-level jobs. You enter the following WHERE clause into the statement in the SQL pane:
WHERE (hire_date < '01/01/90' ) AND (job_lvl = 100 OR job_lvl = 200)The clause linked with AND applies to both clauses linked with OR. An explicit way to express this is to repeat the AND condition once for each condition in the OR clause. The following statement is more explicit (and longer) than the previous statement, but is logically equivalent to it:
WHERE (hire_date < '01/01/90' ) AND (job_lvl = 100) OR (hire_date < '01/01/90' ) AND (job_lvl = 200)The principle of distributing AND clauses to linked OR clauses applies no matter how many individual conditions are involved. For example, imagine that you want to find higher or middle-level employees who have been with the company more than five years or are retired. The WHERE clause might look like this:
WHERE (job_lvl = 200 OR job_lvl = 300) AND (hire_date < '01/01/90' ) OR (status = 'R')After the conditions linked with AND have been distributed, the WHERE clause will look like this:
WHERE (job_lvl = 200 AND hire_date < '01/01/90' ) OR (job_lvl = 200 AND status = 'R') OR (job_lvl = 300 AND hire_date < '01/01/90' ) OR (job_lvl = 300 AND status = 'R')
January 16, 2004 at 9:05 pm
Thanks to all.
Regards,
Mahesh
May 13, 2009 at 7:30 am
the precedence of OR & AND & NOT goes with brackets i think.
select * from temp where (loginname='Raj' AND password='abcd')
OR FirstName='Raju'
here both (loginname and password) acts s one condition (cond1) &
FirstName acts as one condition(cond2).Here to get the result
either cond1(both loginname and password) OR cond2 has to b satisfied
select * from temp where (loginname='Raj' (cond1)) AND (password='abcd' OR FirstName='Raju'(cond2))
here (loginname) acts s one condition & ( password OR FirstName) acts as one condition.Here to get result
cond1 AND either of cond2 (password or firstname) has 2 b satisfied
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply