Info from http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions001.htm#SQLRF52103
Precedence is the order in which Oracle Database evaluates different conditions in the same expression. When evaluating an expression containing multiple conditions, Oracle evaluates conditions with higher precedence before evaluating those with lower precedence. Oracle evaluates conditions with equal precedence from left to right within an expression, with the following exceptions:
- Left to right evaluation is not guaranteed for multiple conditions connected using AND
- Left to right evaluation is not guaranteed for multiple conditions connected using OR
This table lists the levels of precedence among SQL condition from high to low. Conditions listed on the same line have the same precedence. As the table indicates, Oracle evaluates operators before conditions.
|Type of Condition
|SQL operators are evaluated before SQL conditions
||See "Operator Precedence"
|=, !=, <, >, <=, >=,
|IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF type
||exponentiation, logical negation
Info from http://www.praetoriate.com/t_garmany_easysql_sql_logical_operators.htm
It is important to understand how the database evaluates multiple comparisons in the WHERE clause. All the AND comparisons (evaluated from Left to Right) are evaluated before the OR comparisons (evaluated from Left to Right).
As you read this query, it appears that you collect all the employees from the finance and sales department and then check that the salary is less than 1000000.
In this case, you would be wrong! The database will evaluate the AND before the OR.
What this query actually does is list employees in SALES with salary < 100000, and all the employees in the FINANCE department.
Nice example from http://stackoverflow.com/questions/2700819/logical-operator-and-having-higher-order-of-precedence-than-in
Just yesterday I got caught by this. Started with working code:
Changed it in haste:
Broke, because this is what I wanted:
But this is what I got: