How Exactly Does Using or in a MySQL Statement Differ With/Without Parentheses

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

How to Use AND and OR in MySQL

I'm not sure if I understand your question correctly, but you may try the following query:

SELECT * FROM master_post 
WHERE category = 'jmovies' AND (master_post_name LIKE '%ashi%' OR altname LIKE '%ashi%')

I suspect that the "problem" here is the fact that the AND operator has a greater precedence than the OR operator.

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

problem with operators in Mysql `select` query

Change this:

AND gender='$gender1' OR gender='$gender2'

to this:

AND (gender='$gender1' OR gender='$gender2')

You can additionally read about operators here.

What is the difference when comparing with parentheses: WHERE (a, b)=(1,2)

It can be very handy when needed to compare multiple columns to multiple combination of values by using IN() :

 SELECT * FROM YourTable
 WHERE (col1,col2) IN((1,2),(2,3),(4,4)...)

Instead of:

SELECT * FROM YourTable
WHERE (col1 = 1 and col2 = 2) OR
      (col1 = 2 and col2 = 3) OR
      (col1 = 4 and col2 = 4) OR
      ....

After reviewing the execution plan of both queries, I can say that in Oracle(Using IN() which is basically the same), the optimizer evaluate both the same way and both are using the indexes :

Separate conditions:

EXPLAIN PLAN FOR
SELECT * FROM dim_remedy_tickets_cache t
where t.tt_id = '1' and t.region_name = 'one';

6   |   0 | SELECT STATEMENT            |                              |     1 |   311 |    30   (0)| 00:00:01 |
7   |   1 |  TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE     |     1 |   311 |    30   (0)| 00:00:01 |
8   |   2 |   INDEX RANGE SCAN          | DIM_REMEDY_TICKETS_HISTORYPK |     1 |       |    20   (0)| 00:00:01 |

Combined conditions:

EXPLAIN PLAN FOR
SELECT * FROM dim_remedy_tickets_cache t
where (t.tt_id,t.region_name) in  (('1','one'))

6   |   0 | SELECT STATEMENT            |                              |     1 |   311 |    30   (0)| 00:00:01 |
7   |   1 |  TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE     |     1 |   311 |    30   (0)| 00:00:01 |
8   |   2 |   INDEX RANGE SCAN          | DIM_REMEDY_TICKETS_HISTORYPK |     1 |       |    20   (0)| 00:00:01 |

I assume all RDBMS will evaluate this queries the same.

Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):

So:

Cons - May be less readable for some people , but basically no cons.

Pros - Less code , and the combination of multiple columns comparison using IN() :

SQL WHERE condition, why the result using 'a != 7 and b !=836' is not equal ' !(a=7 and b=836)'?

The difference is in your logic operators, and and or, in combination with parentheses.

select * from test where a!=7 and b!=836

This query would select where BOTH of your statements return true, that is both a is not 7, and b is not 836. This would return nothing, there is no record where both of these are true.

select * from test where !(a=7 and b=836)

When you put the parentheses around your and, you move the logic around. That statement means that all records NOT matching any record where both a is 7 and b is 836. So inside the parenthesis it matches the first record, then it inverts that selection.

select * from test where a<>7 or b<>836

The <> is the same as != (Link to documentation). But in this query you use or. So it will match any record where a is not 7, AND any record that is not 836. So would match both second and third row.

For more reading material, take a look at the De Morgan's Laws. !(a and b) equals !a or !b. More explanation here and here.

MySQL Query with specific WHERE rules

Use bracket around OR condition.

SELECT * FROM cms_notificaties WHERE username = '". $user['id'] ."' AND ( userstatus = 'unseen' OR userstatus = 'priority' )

---

Related Topics