What is the Operator?
An operator is a special keyword used to join or change clauses within a WHERE clause, e.g. AND, OR, etc. It is also known as logical operators.
AND Operator
AND keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved.
To filter by more than one column, you use the AND operator to append conditions to your WHERE clause. The following code demonstrates this:
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_id <= 5 AND prod_price <= 10;
The preceding SQL statement retrieves the product name and price for all products having prod_id less than or equal to 5 as long as the price is 10 or less. The WHERE clause in this SELECT statement is made up of two conditions, and the keyword AND is used to join them. AND instructs the DBMS to return only rows that meet all the conditions specified. If a product has prod_id 5 or less but it costs more than 10, it is not retrieved. Similarly, products that cost less than 10 that has prod_id greater than 5 are not retrieved. The output generated by this SQL statement is as follows:
+---------+------------+------------+ | prod_id | prod_name | prod_price | +---------+------------+------------+ | 1 | product 1 | 5.99 | | 2 | product 2 | 3.99 | | 3 | product 3 | 8.99 | | 5 | product 5 | 9.09 | +---------+------------+------------+
The example just used contained a single AND clause and was thus made up of two filter conditions. Additional filter conditions could be used as well, each separated by an AND keyword.
OR Operator
OR keyword used in a WHERE clause to specify that any rows matching either of the specified conditions should be retrieved.
The OR operator is exactly the opposite of AND. The OR operator instructs MySQL (or MariaDB) to retrieve rows that match either condition.
Look at the following SELECT statement:
SELECT prod_name, prod_price FROM products WHERE prod_id &alt;= 5 OR prod_price <= 10;
The preceding SQL statement retrieves the product name and price for any products that has either prod_id 5 or less, or price is 10 or less. The OR operator tells the DBMS to match either condition, not both. If an AND operator would have been used here, the above result would be returned. You can see that the statement has returned all rows that have prod_id 5 and less, it was the first condition. Then the statement returned all rows that have price 10 or less, that was the second condition. The output generated by this SQL statement is as follows:
+---------+------------+------------+ | prod_id | prod_name | prod_price | +---------+------------+------------+ | 1 | product 1 | 5.99 | | 2 | product 2 | 3.99 | | 3 | product 3 | 8.99 | | 4 | product 4 | 13.99 | | 5 | product 5 | 9.09 | | 6 | product 6 | 8.99 | | 7 | product 7 | 7.00 | | 8 | product 8 | 1.80 | | 10 | product 10 | 4.75 | +---------+------------+------------+
Using AND and OR in Correct Order
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:
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
The above statement returned the following output:
+------------+------------+ | prod_name | prod_price | +------------+------------+ | product 1 | 5.99 | | product 4 | 13.99 | | product 5 | 9.09 | | product 9 | 20.00 | | product 10 | 4.75 | | product 11 | 10.25 | +------------+------------+
Look at the previously listed results. Two of the rows returned have prices less than 10so, 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 preceding 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:
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
The above statement returned the following output:
+------------+------------+ | prod_name | prod_price | +------------+------------+ | product 4 | 13.99 | | product 9 | 20.00 | | product 11 | 10.25 | +------------+------------+
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.
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 downside to using parentheses, and you are always better off eliminating any ambiguity.