1

In the WHERE section or anywhere really where you have multiple conditions like OR's I know outer parenthesis are required but are the inner ones?

For example my assumption is that

WHERE A.Title='EMP'
AND ( (A.NAME='Mike') OR (A.ID='9001') )

Is the same as writing

WHERE A.Title='EMP'
AND ( A.NAME='Mike' OR A.ID='9001' )

However if we remove the outer parenthesis then I know the query will be different.

Example:

WHERE A.Title='EMP'
AND (A.NAME='Mike') OR (A.ID='9001')

And

WHERE A.Title='EMP'
AND A.NAME='Mike' OR A.ID='9001'

Are both the same thing but not at all what we want.

Is there any chance that data will be evaluated different between the first 2 conditions?

Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • 4
    *"Is there any chance that data will be evaluated different between the 2 conditions?"* No. Wrapping a single boolean expression inside parenthesis changes nothing. – Thom A Feb 12 '21 at 15:26
  • @Larnu Thanks. I was told something else and had to be sure. – Mike - SMT Feb 12 '21 at 15:27
  • (1) They are the same and you could drop the parens totally. (2) This logic should be in the `ON` clause not in a `WHERE` clause. – Gordon Linoff Feb 12 '21 at 15:27
  • @GordonLinoff you are correct I just threw this in. The question also applies to contions like `A.NAME = 'Mike'` – Mike - SMT Feb 12 '21 at 15:28
  • @GordonLinoff I updated my question to make more since in the where clause – Mike - SMT Feb 12 '21 at 15:29
  • 1
    `WHERE A.NAME='Mike' OR A.ID='9001'`, SQL is not Lisp. – jarlh Feb 12 '21 at 15:29
  • @jarlh Lisp? I am not familiar with that word – Mike - SMT Feb 12 '21 at 15:30
  • https://en.wikipedia.org/wiki/Lisp_(programming_language) – jarlh Feb 12 '21 at 15:31
  • 1
    @Mike-SMT `I was told something else` what exactly? It may not be about what you think. `AND` has a higher precedence than `OR` in binary logic, so in almost all languages `A OR B AND C` is not the same as `(A OR B) AND C`. It's no different than addition and multiplication in math. Even the same symbols are used, eg `+` for OR – Panagiotis Kanavos Feb 12 '21 at 15:32
  • @PanagiotisKanavos I was told "I want it to evaluate the `AND` statements before the `OR` statements" and the claim was that the inner paren's were needed for order of operations – Mike - SMT Feb 12 '21 at 15:35
  • 2
    Please, recommended view this top about [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) – Antonio Leonardo Feb 12 '21 at 15:42
  • @AntonioLeonardo thanks I am reading it now and will forward it on. – Mike - SMT Feb 12 '21 at 15:44
  • @Mike-SMT in that case, double no. The parentheses are needed when you want to evaluate `OR` before `AND`. I had quite a few queries that returned unexpected results when I added an `OR` and forgot the parentheses, precisely because AND takes precedence – Panagiotis Kanavos Feb 12 '21 at 15:55

1 Answers1

3

It's like in math

2 + 3 * 4   ==> 14

is the same as

2 + (3 * 4)   ==> 14

because the multiplication has a higher precedence than the addition. If you want to do the addition before the multiplication, you must add parenthesis.

(2 + 3) * 4   ==> 20

In SQL AND has a higher precedence than OR. = as comparison operator has a higher precedence than both of them. Therefore the inner parenthesis are not required, because the = will always be performed first. E.g.

A.Title='EMP' AND A.NAME='Mike' OR A.ID='9001'

is the same as

((A.Title='EMP') AND (A.NAME='Mike')) OR (A.ID='9001')

Parenthesis are only required if you want to perform the OR before the AND (even when it appears first in the expression):

A.Title='EMP' AND (A.NAME='Mike' OR A.ID='9001')

Only when operators have the same precedence, they are evaluated left to right.

For the full precedence list, see: Operator Precedence (Transact-SQL)

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • Correct I knew that. The person was saying the inner paren's were needed to evaluate OR first. like this `AND ( (A.NAME='Mike') OR (A.ID='9001') )` and I disagreed but of coarse had to question if I was wrong so I posted on SO :D – Mike - SMT Feb 12 '21 at 15:57
  • 1
    Though not needed, clarity is important, so you may put them in for readability on more complicated clauses (especially where ANDs and ORs are mixed, you may get what you want without any parens, but you should attempt to be clear for future readers). – Chris Steele Feb 12 '21 at 16:12