2

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?

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
johnny
  • 19,272
  • 52
  • 157
  • 259

3 Answers3

12

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

CyberDude
  • 8,541
  • 5
  • 29
  • 47
  • AND has precedence because it appears first, not because AND has a higher precedence than OR – colithium Apr 05 '12 at 19:37
  • 2
    @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.aspx – Shannon Severance Apr 05 '12 at 23:26
  • 4
    @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 – colithium Apr 06 '12 at 08:13
  • 1
    @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 Apr 06 '12 at 15:45
3

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?

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
2

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.

Maxime 1000he
  • 31
  • 3