Sql Parentheses Use in an or Clause

SQL Parentheses use in an OR clause

Take a look at the Operator Precedence in SQL Server (You've not specified that, but I'd imagine it's the same for all RDBMS). What this means is that ANDs (without parenthesis) are evaluated before¹ bind more tightly than ORs.

So in your specific case, without the parenthesis, the conditions are:

• employe.service=service.code_serv AND employe.nom LIKE 'A%'

OR

• employe.nom LIKE 'B%'

---

¹Evaluation order is deliberately not specified in SQL, allowing many more possible re-orderings that languages that guarantee left-to-right or precedence ordered evaluation.

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

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

Are inner parenthesis needed in the WHERE clause? or anywhere

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)

Parentheses in Sql

Try this:

where (Product_Category = 'Fit' and  Product_number IN (1234, 1235, 1236, 1237, 1238)) or
      (Product_Category in ('Tight', 'Wide') and Product_number = 1324) or
      (Product_Category in ('Tall', 'Short', 'Thin') and Product_number = 1325)

You can see if the parentheses balance more easily if the code lines up.

Importantly, I assume that product_number is a number. Compare it to numbers. Type conversions can adversely affect performance.

postgreSQL when to use parentheses for keywords, ON vs USING( )

USING() can be applied on a list of columns - like in USING(id,seq). The comma, without the parentheses, could be the introduction of a following clause, so the parser can only safely determine the comma between id and seq as a list separator when we put the list into parentheses.

ON, on the other side, is always followed by a Boolean expression, which is straightforward to parse: expression - comparison operator - expression .

Are brackets in the WHERE clause standard sql

Yes. You can use parenthesis to bind components of where clauses. This isn't necessary in your example, but if you had multiple and and or components, you might need parenthesis to either ensure correct order of operations or simply to self-document the query.

Example 1:

select *
from foo
where 
   (class='A' and subclass='B')
   or (class='C' and subclass='D')

In example 1, the parens aren't strictly required because and binds more tightly than or, but if you had multiple or conditions tied by and you would need it to get correct results, as in example 2 below.

Example 2:

select *
from foo
where 
   (class='A' or class='B')
   and (subclass='C' or subclass='D')

I use them in either case, because I don't like having to parse the sql in my head the same way the query optimizer does -- I'd rather be explicit about it and more quickly understand what the intent is.

How exactly does using OR in a MySQL statement differ with/without parentheses?

This is because OR has lower operator precedence than AND. Whenever the DB sees an expression like

A AND B OR C

the AND is evaluated first, i.e. it is equivalent to

(A AND B) OR C

So if you explicitly want

A AND (B OR C)

instead, you must put in the parentheses.

This is btw not specific to SQL. The order of precedence of these operators is the same in all programming languages I know (i.e. at least C, C++, C#, Java and Unix shell scripts).

What are the parentheses doing in this SQL Statement in the FROM CLAUSE (TSQL)

Nothing in this case as inner joins are associative and commutative.

In general you can change the virtual tables that participate in joins by moving the position of the on clause and this can have optional parentheses applied also to hopefully make things clearer.

So for example you could have

SELECT P.PersonName,
       Pt.PetName,
       Pa.AccessoryName
FROM   Persons P
       LEFT JOIN Pets Pt
                  INNER JOIN PetAccessories Pa
                    ON Pt.PetName = Pa.PetName
         ON P.PersonName = Pt.PersonName;

Which optionally might also be written

SELECT P.PersonName,
       Pt.PetName,
       Pa.AccessoryName
FROM   Persons P
       LEFT JOIN (Pets Pt
                  INNER JOIN PetAccessories Pa
                    ON Pt.PetName = Pa.PetName)
         ON P.PersonName = Pt.PersonName;

---

Related Topics