MySQL AND Operator

 MySQL AND Operator

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

The AND the operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate as true. The AND the operator returns false if one of the two expressions evaluate as false.

Here is the syntax of the AND operator:

boolean_expression_1 AND boolean_expression_2

The following table illustrates the results of the AND operator when combining true, false, and null.

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

The AND the operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form a condition. The AND the 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
---------------
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. Since 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.

MySQL AND operator examples

Let’s use the customers the table in the sample database for the demonstration.

The following statement uses the AND operator to find customers who locate in California (CA), USA:

SELECT 
    customername, 
    country, 
    state
FROM
    customers
WHERE
    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 a credit limit greater than 100K.

SELECT    
	customername, 
	country, 
	state, 
	creditlimit
FROM    
	customers
WHERE country = 'USA'
	AND state = 'CA'
	AND creditlimit > 100000;

In this tutorial, you have learned how to use the MySQL AND operator to combine two or more Boolean expressions to form a condition in the WHERE clause.