Summary: in this tutorial, you will learn how to the MySQL
AND operator to combine multiple Boolean expressions to filter data.
Introduction to MySQL AND operator
AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true. The
AND operator returns false if one of the two expressions evaluate to false.
Here is the syntax of the
boolean_expression_1 AND boolean_expression_2
The following table illustrates the results of the
AND operator when combining true, false, and null.
AND operator is often used in the
WHERE clause of the
DELETE statement to form a condition. The
AND operator is also used in join conditions of the
INNER JOIN and
LEFT JOIN clauses.
When evaluating an expression that has the
AND operator, MySQL stops evaluating the remaining parts of the expression whenever it can determine the result. This function is called short-circuit evaluation.
Consider the following example.
SELECT 1 = 0 AND 1 / 0 ;
1 = 0 AND 1 / 0
Note that in MySQL, zero is considered false and non-zero is treated as true.
MySQL only evaluates the first part
1 = 0 of the expression
1 = 0 AND 1 / 0. Because the expression
1 = 0 returns false, MySQL can conclude the result of the whole expression, which is false. MySQL does not evaluate the remaining part of the expression, which is 1/0; If it did, it would issue an error because of the division by zero error.
AND operator examples
Let’s use the
customers table in the sample database for the demonstration.
The following statement use the AND operator to find customers who locate in California (CA), USA:
country = 'USA' AND state = 'CA';
By using the
AND operator, you can combine more than two Boolean expressions. For example, the following query returns the customers who locate in California, USA, and have the credit limit greater than 100K.
WHERE country = 'USA'
AND state = 'CA'
AND creditlimit > 100000;
In this tutorial, we have shown you how to use the MySQL
AND operator to combine two or more Boolean expressions to form a condition for the