Akdora’s Blog

Programming, Oracle, Life, Fun

Rules of Precedence in SQL Where Clause February 18, 2009

Filed under: SQL — Akdora @ 8:49 pm
Tags: , , ,

Expressions in where clause process in the following order:

Number Expression
1 Arithmetic Operators
2 Concatenation Operator
3 Comparision Condition
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not Equal To
7 NOT Logical Condition
8 AND Logical Condition
9 OR Logical Condition

 

For example on AND and OR conditions:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
 
SQL> set serveroutput on
 
SQL>
SQL> SELECT t.employee_id, t.first_name, t.last_name, t.salary  ,t.manager_id
  2    FROM employees t
  3   WHERE t.salary > 15000
  4         AND t.manager_id = 100
  5         OR t.manager_id = 103;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY MANAGER_ID
----------- -------------------- ------------------------- ---------- ----------
        101 Neena                Kochhar                     17000,00        100
        102 Lex                  De Haan                     17000,00        100
        104 Bruce                Ernst                        6000,00        103
        105 David                Austin                       4800,00        103
        106 Valli                Pataballa                    4800,00        103
        107 Diana                Lorentz                      4200,00        103
 
6 rows selected
SQL> SELECT t.employee_id, t.first_name, t.last_name, t.salary  ,t.manager_id
  2    FROM employees t
  3   WHERE t.salary > 15000
  4         OR t.manager_id = 100
  5         AND t.manager_id = 103;
 
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY MANAGER_ID
----------- -------------------- ------------------------- ---------- ----------
        210 Sue                  Andreas                     20000,00        145
        100 Steven               Black                       24000,00
        101 Neena                Kochhar                     17000,00        100
        102 Lex                  De Haan                     17000,00        100
 
SQL>

In the first query, we try to find employees supply this condition => (salary>15000 and manager_id = 100) or (manager_id=103).

In the second query, we try to find employees supply this condition => (manager_id = 100 and manager_id = 103) or (salary > 15000)

 
 

6 Responses to “Rules of Precedence in SQL Where Clause”

  1. moleboy Says:

    It amazes me how often I see posts like this outlining the order in which conditions were evaluated. Not because the topic isn’t a little confusing, but because it is so very unnecessary.

    The solution I always use is (), and I can’t understand why you would ever NOT use them, especially in a clause that can be confusing in any way.
    If I write some SQL like you have, perhaps I know how these things will evaluate and I can tell what the results will be.
    However, I can’t guarantee that the next guy to look at this code will understand it.
    So, in your example, I would ALWAYS do
    (manager_id=100 and manager_id=103) or (salary > 15000) because that is ALWAYS clear and easy to understand. I can count on the next guy knowing what the query does. Heck, I can count on even a non-sql person understanding it.

    • Donaghy Says:

      It’s usefull, in the case that you find a stored procedure which contents similar query, and you try to figure out, what was the reason because the previous database administrator wrote that. What was his logic?

  2. gkhnlts Says:

    really good explanation.. thanks..

  3. quietchaos Says:

    @moleboy,
    what if you ARE that next guy who looks at the code? i needed it explained to me because in my case, the actual AND/OR keyword is a variable in multiple positions in the query…

  4. Don’t you mean…

    WHERE (manager_id=100 OR manager_id=103) AND (salary > 15000)

    It’s impossible to have (manager_id=100 AND manager_id=103), so the selection of records returned will always be (salary > 15000).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s