MariaDB Crash Course - Ben Forta [21]
* * *
Using the OR Operator
The OR operator is exactly the opposite of AND. The OR operator instructs MariaDB to retrieve rows that match either condition.
Look at the following SELECT statement:
Input
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;
Analysis
The preceding SQL statement retrieves the product name and price for any products made by either of the two specified vendors. The OR operator tells the DBMS to match either condition, not both. If an AND operator had been used here, no data would be returned (it would have created a WHERE clause that could never be matched). The output generated by this SQL statement is as follows:
Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
* * *
New Term: OR
A keyword used in a WHERE clause to specify that any rows matching either of the specified conditions should be retrieved.
* * *
Understanding Order of Evaluation
WHERE clauses can contain any number of AND and OR operators. Combining the two enables you to perform sophisticated and complex filtering.
But combining AND and OR operators presents an interesting problem. To demonstrate this, look at an example. You need a list of all products costing 10 or more made by vendors 1002 and 1003. The following SELECT statement uses a combination of AND and OR operators to build a WHERE clause:
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 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
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) processes 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 parentheses. 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