Boolean operators are logical operators.  Per Oracle’s documentation, “logical operators manipulate the results of conditions”.  There are three Boolean operators – listed in order of precedence: (1) NOT; (2) AND; (3) OR.  (There are additional “Boolean operators” with regard to Boolean algebra, but they are essentially combinations of these three and will not be discussed.)

When a SQL statement is processed the conditions in the WHERE clause are evaluated in order of precedence and not necessarily in visual order.

Consider the statement below.

In this example the visual order of the logical operators is: (1) OR; (2) AND; (3) NOT.  The visual order does not match the order of precedence mentioned above.  In order to determine how the WHERE clause will be processed, we can rewrite the WHERE clause. First, look for NOT.  We see the comparison NOT STADIUM = ‘N’.  This will be evaluated first.

You may be wondering if NOT STADIUM = ‘N’ would be processed in the same order as STADIUM != ‘N’ and the answer is no.  Even though they logically mean the same thing when interpreted individually, STADIUM = ‘N’ is the expression and NOT STADIUM = ‘N’ is the comparison.  Changing the expression will affect the output, but will not affect Boolean operator precedence.  Try to think of expressions (shown before and after the Boolean operator) as being enclosed in parenthesis, i.e., NOT (STADIUM = ‘N’).

Next we look for AND.  We can see that the next AND condition is GRASS = ‘TURF’ AND CITY = ‘TULSA’.  The remaining condition is OR, OR FIELD = ‘BASEBALL’ and it will be evaluated last.  Below is how the statement will be interpreted.

STATEMENT

You can override these rules by using parenthesis and grouping the conditions how you want them to be evaluated.  Below is the same SQL statement, but using parenthesis to override the default order.

In most SQL statements I come across and write, parenthesis are almost always used to override the order.  Overriding the order using parenthesis provides clarity to the reader/editor of the statement.  If you have several expressions to be compared (beyond 1 or 2) using different Boolean operators it is highly impractical to expect the reader/editor to analyze this.  When at all possible I recommend always using parenthesis to override the order.

 

4 Comments

  1. Rosalind June 12, 2017 at 10:43 am

    SUPERB Post.thanks for share..more wait.

     
  2. Theron June 16, 2017 at 2:49 pm

    This really answered my dilemma, thank you!

     
  3. Renate June 19, 2017 at 12:49 am

    I wanted to thank you for this excellent read!! I definitely loving every little bit of
    it I have you bookmarked to have a look at new stuff
    you post.

     
  4. Annett July 19, 2017 at 1:20 pm

    The post has actually peaks my interest. Iwill bookmark your site and keep checking
    for new information.

     

Leave a Reply

Your email address will not be published. Required fields are marked *

 

 

*