In SQL, what does using parentheses with an OR mean?

Go To StackoverFlow.com

1

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?

2012-04-05 19:05
by johnny


8

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

2012-04-05 19:14
by CyberDude
AND has precedence because it appears first, not because AND has a higher precedence than O - colithium 2012-04-05 19:37
@colithium: Incorrect. Oracle defines 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
@ShannonSeverance Oh wow, I'm really glad I use a lot of parentheses when conditions are complicated because I've been thinking otherwise all these years without it ever biting m - colithium 2012-04-06 08:13
@Colithium That's a good practice anyway. I always use enough parentheses so that almost all the precedence rules do not come into play. The exception being assignment having the highest precedence and multiply coming before add or subtract. I do not have room in my head to keep track of the precedence rules for all the languages I use. (Except for lips/scheme where there are none. - Shannon Severance 2012-04-06 15:45


2

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?

2012-04-05 19:12
by Matt Fenwick
Ads