SQL Parentheses use in an OR clause

sql parentheses
angela picture angela · May 11, 2011 · Viewed 68.9k times · Source

Was wondering whether anyone would know why do we use the parentheses in this SQL: So, the format goes as follows:

Name,location and department of the service of the employees whose name starts with A or B. (A rough translation from French).

I answered the following way:

SELECT service.nom_serv, localite.ville, localite.departemen
FROM service, localite, employe
WHERE service.code_loc=localite.code_loc
AND employe.service=service.code_serv
AND ((employe.nom LIKE 'A%') OR (employe.nom LIKE 'B%'))

Basically, where the last AND is concerned for the WHERE, couldn't I simply do without the parenthesis in order to have the SQL select for me employees with their name starting either with an A or a B? What difference does positioning a parenthesis in that way make? And ahy is there a double use of parentheses? Or is it to prioritize the OR in the last clause, since an AND is preceding it?

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · May 11, 2011

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 before1 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%'

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

Related questions

Why would the addition of parentheses in SQL query cause the results to change?

When I execute the following query, even though there are 11 records that match, none are returned as written. However, if I remove the parentheses on lines 6 and 9, all 11 records are returned as expected. 1 select obj_id, obj_title, UI_DISPLAYNAME 2 …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More