In SQL, What Does Using Parentheses with an or Mean

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

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.

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

In SQL, what do the parentheses in the 'as' clause mean?

T is your derived table's alias.

C is the column name that is shredded from the /Data/Children nodes.

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

Three words: order of operations. It's like you learned in math, certain operators take precedence over others (like multiplying comes before adding) unless you use parentheses to force it your way. In this case, AND has a higher precedence than OR.

Without adding in your own parentheses, your WHERE clause gets evaluated like this:

Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )
OR 
(Upper( OBJ_TITLE ) LIKE Upper( '%smith%' ) 
    AND OBJ_ID IN (select obj_id from PITS_OBJECT where 
    ...)

But when you manually add in those parentheses, you're forcing the OR to be evaluated first.

(Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' )
    OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' ))
AND OBJ_ID IN (select obj_id from PITS_OBJECT where 
...

Edit: I should directly answer your question about why you're getting back more data. The reason is because, without the parentheses, the engine will short-circuit its check if it finds that line 7 is true. In other words, it will include all records where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' ), regardless of the other criteria.

When you add in those parentheses, the logic changes. It will include records where Upper( UI_DISPLAYNAME ) LIKE Upper( '%smith%' ) OR Upper( OBJ_TITLE ) LIKE Upper( '%smith%' ), and then it checks that the record ALSO satisfies the inner select that starts on line 12. Those extra records don't show up because they're not meeting the criteria of that inner select.

What do brackets mean around column names in SQL Server?

Columns enclosed in square brackets are usually keywords or contain special characters or spaces.

What specific column name do you have enclosed in brackets?

why I have to add parentheses to make union work

Without extra parentheses, LIMIT is only allowed once at the end of the query and applies to the result of the UNION. To attach a LIMIT to each SELECT parentheses are required.

Please check :: https://www.postgresql.org/docs/current/sql-select.html#SQL-UNION

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

---

Related Topics