Example:
select count(*) from my table
where
column1 is not null
and
(column1 = 4 OR column1 = 5)
Example 2:
select count(*) from my table
where
column1 is not null
and
column1 = 4 OR column1 = 5
In my database with the real column names, I get two different results. The one with the parentheses is right because if I do:
select count(*) from my table
where
column1 is not null
and
column1 = 4
and then
select count(*) from my table
where
column1 is not null
and
column1 = 5
and add them together, I get the right answer...I think. Same as the first example with the parentheses above.
Why do I get different results by changing precedence with the OR test?
It's not Oracle or SQL. It's basic boolean logic. The AND condition is "stronger" (has precedence) than OR, meaning it will be evaluated first:
column1 is not null
and
column1 = 4 OR column1 = 5
Means
column1 is not null
and
column1 = 4
is evaluated first, then OR is applied between this and column1 = 5
Adding parentheses ensures OR is evaluated first and then the AND.
Pretty much like in maths:
2 * 3 + 5 = 6 + 5 = 11
but
2 * (3 + 5) = 2 * 8 = 16
More reading here: http://msdn.microsoft.com/en-us/library/ms190276.aspx
AND
as having higher precedence than OR
. http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions001.htm#i1034834 And not just Oracle, MS SQL Server also. http://msdn.microsoft.com/en-us/library/ms190276.asp - Shannon Severance 2012-04-05 23:26
This comes down to whether your expression is parsed as:
(column1 is not null and column1 = 4) OR column1 = 5
or
column1 is not null and (column1 = 4 OR column1 = 5)
See the difference?