Documentation on usage of brackets() in mysql queries I have an application where i am needed to write queries with multiple conditions and i am using several other keywords such as between, and, or etc

I have this two conditions

select * from countries where date between (x and y) and filter = filter and column choosen like '%col%';

and

select * from countries where date between (x and y) and filter = filter

I haven't actually run the queries but i want the date condition using between to be executed first and i thought the brackets would help.

What is the exact usage of brackets () in mysql and where is that documented?.
Putting parentheses in a BETWEEN leads to strangenesses:

date between (x and y) and filter1 = filter2


is treated as

(date between (x and y) and filter1) = filter2


Why?


BETWEEN expects 2 'arguments'; you have given it (1) (x and y), which returns true or false, which are numerically 1 or 0, and filter1.
The result of that (probably 0) is then compared:  0 = filter, which is likely to be false (0).


Recommendations:


Do not use parens that way in BETWEEN.  These are OK:

(date between x and y)

(date) between (x) and (y)  -- but this is clutter

When mixing AND and OR use parens to make it clear what you want to do.  AND will be done before OR, but too many SQL programmers fail know that, so be explicit:

a AND (b OR c)  -- Without parens, you get  (a AND b) OR c

x = 1 AND ( y = 2 OR z = 3 )

Don't clutter:  Rarely do you need (x) instead lf x.
When in doubt, add parens, even if it clutters.
When using function calls, no space before the (; there are cases where the parser decides whether it is a function call versus something else by using that spacing:  DATE(foo), not DATE (foo).


The documentation spells all of this out, but not necessarily in an obvious way.  Here are clues:


The BETWEEN syntax does not show the parens you asked about.
Any expression can have parens around it.
There is a "precedence" that says what happens when mixing AND and OR -- which is just like * and +.  (You do understand that 1+2*3 is 7, not 9 because * is evaluated before +?)