Post reply

WHERE statement using both OR and AND

  • March 30, 2006 at 7:31 pm

    #111602

    Hi All,

    I have a SELECT statement that has a WHERE statement that I need to be sure is returning the correct data. I can not find a reference that clearly states which order AND's and OR have priority. The WHERE statement is as follows:

    --***

    WHERE

    (

    (actual_charges = actual_charge)

    AND

    (standard_costs = standard_costs)

    AND

    (actual_charge IS NOT NULL)

    AND

    (standard_costs IS NOT NULL)

    )

    OR

    (

    (actual_charges = actual_charge)

    AND

    (actual_charge IS NOT NULL)

    AND

    (standard_costs IS NULL)

    )

    OR

    (

    (standard_costs = standard_costs)

    AND

    (actual_charge IS NULL)

    AND

    (standard_costs IS NOT NULL)

    )

    OR

    (

    (actual_charge IS NULL)

    AND

    (standard_costs IS NULL)

    )

    OR

    ( service_code_type = 'U' )

    AND

    (ISNULL(billing_type_flag, 'C') = 'C')

    Can anyone help me to work out which has priority.

    Thank you in advance for any guidance.

    Cheers,

    David

  • March 30, 2006 at 8:46 pm

    #630002

    David - BOL has a pretty comprehensive explanation..the first 3 statements sum it all..

    1) When a query is executed, it evaluates first the clauses linked with AND, and then those 
linked with OR.

2) 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.

Note: The NOT operator takes precedence over both AND and OR.

Precedence of AND and OR
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.

How AND Works with Multiple OR Clauses
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')







    **ASCII stupid question, get a stupid ANSI !!!**

  • March 30, 2006 at 9:01 pm

    #630003

    Thank you very much for your quick and informative reply. I will study up on the examples that you gave me and read BOL a bit more.

    Cheers,

    David

  • March 31, 2006 at 6:35 am

    #630073

    Hi David:

    I've been studying your "where" clause and I thought I'd add a comment or two for you just in case you hadn't noticed these things:

    First, I like how you've grouped your various criteria using "or" on its on line. But towards the end of your criteria it looks like things might fall apart for you. Based on your use of parentheses, is the criteria "(ISNULL(billing_type_flag, 'C') = 'C')" supposed to be a standing requirement in addition to any of the other grouped clauses? If so, I would suggest putting a pair of parentheses around all of the grouped "or" clauses so that any of those conditions AND (ISNULL(billing_type_flag, 'C') = 'C') will function as expected:

    WHERE

    (

    (

    (actual_charges = actual_charge)

    AND

    (standard_costs = standard_costs)

    AND

    (actual_charge IS NOT NULL)

    AND

    (standard_costs IS NOT NULL)

    )

    OR

    ...

    (

    (actual_charge IS NULL)

    AND

    (standard_costs IS NULL)

    )

    OR

    ( service_code_type = 'U' )

    )

    AND

    (ISNULL(billing_type_flag, 'C') = 'C')

     

    If that isn't your intent, then the next potential problem I see is that your last "or" criteria do not have a parentheses. Perhaps you meant to write the statement as follows:

    WHERE

    (

    (actual_charges = actual_charge)

    AND

    (standard_costs = standard_costs)

    AND

    (actual_charge IS NOT NULL)

    AND

    (standard_costs IS NOT NULL)

    )

    OR

    ...

    (

    (actual_charge IS NULL)

    AND

    (standard_costs IS NULL)

    )

    OR

    (

    ( service_code_type = 'U' )

    AND

    (ISNULL(billing_type_flag, 'C') = 'C')

    )

     

    Just a couple of comments to help make sure you've got your criteria established as expected. Good luck.

    --Pete

  • March 31, 2006 at 8:02 am

    #630129

    I would just emphasise : if there is ANY possibility of confusion for you or a successor, ALWAYS use parentheses. You cannot overclarify or overcomment code. Always think of the poor *** who comes after and may not have your experience or skills.

  • March 31, 2006 at 8:09 am

    #630133

    Usually people end up with the wrong results because they are mixing ANDs and ORs within a where clause without the proper ( ) 's or enough ( )'s.

    A while back I wrote an article on boolean logic and how it is used in SQL Server when it comes to ands and ors.  It may be worth a read:

    http://weblogs.sqlteam.com/jhermiz/archive/2005/12/22/8604.aspx

  • March 31, 2006 at 11:31 am

    #630203

    Sushila,

    I believe you have extra parentheses in your last example.

    It should be as follows to match the expansion you specified:

    WHERE

    (job_lvl = 200 OR job_lvl = 300) AND

    (hire_date < '01/01/90' OR status = 'R')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply