Data Filtering Using MySQL’s AND and OR Operators
The condition can be paired with one or more logical expressions in this case. These logical formulations are referred to as predicates. Predicates, or logical expressions, can be true, false, or unknown. When the WHERE clause returns no rows, the predicate is evaluated as FALSE. If it yields a row, it is added to the result set and the condition is evaluated as TRUE.
To demonstrate, we will utilize the Sakila database’s customer table. In this essay, I will illustrate several usage cases of the WHERE clause, with a focus on the following:
- The WHERE clause includes an equal operator.
- The WHERE clause, which includes the AND, OR, and BETWEEN operators,
- The WHERE clause, together with the LIKE and IN operators
- The WHERE clause, which includes comparison operators
The WHERE clause includes an equal operator.
The following query returns a list of customers whose first name is ‘Linda.’ To obtain the records, write the query as follows:
- where the first name is ‘LINDA’ from the customer;
- The criterion in the query is that the first name = ‘LINDA ‘, which is true.
The AND operator in the WHERE clause
In this example, I’ll illustrate how we may utilize various predicates to populate the table with the appropriate records. For instance, suppose we wish to populate the customer with address id 598 and store id 1. The query should be constructed as follows to fetch the records from the table:
select * from customers where address id = 598 and store id = 1.
The AND operator is used in the WHERE clause here. The expression is where address id = 598 AND store id = 1. If both expressions are assessed as TRUE, the expression as a whole is evaluated as TRUE. The record in the table has an address id of 598 and a store id of 1, so the entire expression is evaluated as TRUE, and the query returns the result set.
The OR operator in the WHERE clause
For instance, suppose we want to fill the customer whose store id=1 AND active=0. To obtain the records, write the query as follows:
where store id=1 OR active=0 from customer
The OR operator is used in the WHERE clause here. WHERE store id=1 OR active=0 is the expression. Any one of the expressions is assessed as true; the full expression is then evaluated as true. We have a record in the table with store id = 1 OR active = 0; so, the full expression is evaluated as TRUE, and the query returns the result set.
The WHERE clause combined with the BETWEEN operator
For instance, let’s suppose we wish to fill a customer whose address ID is between 560 and 570. To obtain the records, write the query as follows:
if address id is between 560 and 570, pick * from the customer;
In this case, we used the BETWEEN operator in the WHERE clause. Where the address id is between 560 and 570, is the expression. Because there are records in the table with address ids BETWEEN 560 and 570, the expression is evaluated as TRUE, and the query returns the result set.
The IN operator in the WHERE clause
For instance, suppose we wish to fill the customer whose customer id is in (10,11,12). To obtain the records, write the query as follows:
where customer id is in select * from customer (10,11,12)
In this case, we used the IN operator in the WHERE clause. The phrase is “where the customer is in” (10,11,12). We have a record in the table with the customer id (10,11,12); hence, the expression is evaluated as TRUE, and the query returns the result set.