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

Question

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?

Solution 1

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

Solution 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?

Solution 3

Parenthesis matter, (A AND B) OR C ≠ A AND (B OR C) just like in math: (0 * 1) + 2 ≠ 0 * (1 + 2)

However, you can choose not to use parenthesis : SQL doesn't have operator precedence rules, so it strictly evaluates expressions from left to right. For instance:

true OR false AND false

is false, just like

(true OR false) AND false

while

true OR (false AND false)

is true.

Comments

[colithium]

AND has precedence because it appears first, not because AND has a higher precedence than OR

[Shannon Severance]

@colithium: Incorrect. Oracle defines AND as having higher precedence than OR. docs.oracle.com/cd/E11882_01/server.112/e26088/… And not just Oracle, MS SQL Server also. msdn.microsoft.com/en-us/library/ms190276.aspx

[colithium]

@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 me

[Shannon Severance]

@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.)

[Sander de Jong]

Not true, SQL absolutely has operator precedence rules, as the accepted answer states correctly.