Advanced Data Filtering - MariaDB Crash Course - page 52

▼ Analysis
Look at the previously listed results. Two of the rows returned have prices less
than 10 —so, obviously, the rows were not filtered as intended. Why did this
happen? The answer is the order of evaluation. SQL (like most languages) pro-
cesses AND operators before OR operators. When SQL sees the previous WHERE
clause, it reads products made by vendor 1002 regardless of price, and any products
costing 10 or more made by vendor 1003 . In other words, because AND ranks
higher in the order of evaluation, the wrong operators were joined together.
The solution to this problem is to use parentheses to explicitly group related
operators. Take a look at the following SELECT statement and output:
▼ Input
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
▼ Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
▼ Analysis
The only difference between this SELECT statement and the earlier one is that,
in this statement, the first two WHERE clause conditions are enclosed within
parentheses. As parentheses have a higher order of evaluation than either AND
or OR operators, the DBMS first filters the OR condition within those parenthe-
ses. The SQL statement then becomes any products made by either vendor 1002 or
vendor 1003 costing 10 or greater , which is exactly what you want.
Tip
Using Parentheses in WHERE Clauses Whenever you write WHERE clauses that use
both AND and OR operators, use parentheses to explicitly group operators. Don't ever
rely on the default evaluation order, even if it is exactly what you want. There is no down-
side to using parentheses, and you are always better off eliminating any ambiguity.