zechdc zechdc - 5 months ago 6
SQL Question

How is mysql interpreting/grouping a duplicate WHERE statement with an OR statement in the middle?

I am using an Laravel's ORM. It is generating a huge query and inside that query I noticed that there is a WHERE statement that repeats itself, and the order of that WHERE statement seems to be very important. I believe it has something to do with how MySQL is grouping the WHERE statements but I don't understand how MySQL works well enough. I'm trying to understand why this works the way it does. How is mysql interpreting/grouping this?

Table

items
-----------------------------------------------
id | status
-----------------------------------------------
19 | 1
20 | 0
21 | 1


Results needed:

-----------------------------------------------
id | status
-----------------------------------------------
19 | 1
21 | 1


The query is much longer than this. But here is the code the ORM is generating that produces the above results needed:

SELECT * FROM campaigns WHERE status = 1 OR id IN ('20') AND status = 1 ORDER BY id DESC;


If I remove the last
status = 1
the query does not return the needed results. Is MySQL grouping the WHERE statements like this:

SELECT * FROM campaigns WHERE status = 1 OR (id IN ('20') AND status = 1);


The query the ORM produces is a few pages long, so when reading this it is pretty confusing without the parentheses. It seems like MySQL is grouping it like this. I guess I don't understand well enough how MySQL works. Any recommendations on books to better understand MySQL?

Building up the Query/Trying to understand what MySQL is doing



1)



SELECT * FROM items WHERE status = 1


Results

-----------------------------------------------
id | status
-----------------------------------------------
19 | 1
21 | 1


2)



SELECT * FROM items WHERE status = 1 OR id IN ('20')


Results

-----------------------------------------------
id | status
-----------------------------------------------
19 | 1
20 | 0
21 | 1


3)



SELECT * FROM items WHERE status = 1 OR id IN ('20') AND status = 1


Results

-----------------------------------------------
id | status
-----------------------------------------------
19 | 1
21 | 1

Answer

AND has precedence over OR, see also SQL Logic Operator Precedence: And and Or.

For your example, this means

SELECT * FROM campaigns WHERE status = 1 OR id IN ('20') AND status = 1 ;

is automatically interpreted as

SELECT * FROM campaigns WHERE status = 1 OR (id IN ('20') AND status = 1);

even if you don't put the parenthesis.

It is a good idea to always write the parenthesis, even if you know they are not needed, to make the intention clear to other readers of your code (and to the compiler/interpreter, if needed).