MySQL AND operator

If you want to combine one or more expressions in the WHERE clause, you can use the logical operator AND, OR or NOT. In this article, we will learn the AND operator.

AND operator syntax

The AND Operator is a binary logical operator, and it needs two operands. Only when both operands are TRUE, it returns TRUE, otherwise it returns FALSE or NULL.

The syntax of AND Operator:

a AND b

Here:

• a and b are the two operands of AND. They can be expressions or values.
• If both a and b are 1 (TRUE), AND returns 1 (TRUE), or else returns 0 (FALSE) or NULL.

Unlike some programming languages, there is no Boolean type in MySQL. The result of AND is 1, 0 or NULL.

The operating rules of the AND operator are as follows:

• If neither operand is 0 (FALSE) and NULL, then AND returns 1.

  SELECT 1 AND 1, 1 AND 2;
  

  +---------+---------+
  | 1 AND 1 | 1 AND 2 |
  +---------+---------+
  |       1 |       1 |
  +---------+---------+

• If an operand is 0 (FALSE), then AND returns 0.

  SELECT
      0 AND 0,
      0 AND 1,
      0 AND 2,
      0 AND NULL;
  

  +---------+---------+---------+------------+
  | 0 AND 0 | 0 AND 1 | 0 AND 2 | 0 AND NULL |
  +---------+---------+---------+------------+
  |       0 |       0 |       0 |          0 |
  +---------+---------+---------+------------+

• If one of the two operands is NULL and the other is not 0 (FALSE), then AND returns NULL.

  SELECT 1 AND NULL, 2 AND NULL, NULL AND NULL;
  

  +------------+------------+---------------+
  | 1 AND NULL | 2 AND NULL | NULL AND NULL |
  +------------+------------+---------------+
  |       NULL |       NULL |          NULL |
  +------------+------------+---------------+

• The order of the operands does not affect the result of the AND operator.

  SELECT 1 AND 0, 0 AND 1, 1 AND NULL, NULL AND 1;
  

  +---------+---------+------------+------------+
  | 1 AND 0 | 0 AND 1 | 1 AND NULL | NULL AND 1 |
  +---------+---------+------------+------------+
  |       0 |       0 |       NULL |       NULL |
  +---------+---------+------------+------------+

The following table lists the results of the AND operator with different operands:

AND in the WHERE clause

In WHERE clause, the AND operator conbines two query conditions. The general form is as follows:

column_name1 = value1 AND column_name2 = value2

The WHERE Clause filters rows that satisfy the two comparison conditions.

In other words, the rows will be returned in the result set must have column_name1 column valued value1 and column_name2 column valued value2.

AND operator examples

In the following examples, we will use the actor table from the Sakila sample database for the demonstration.

The following SQL statement returns all actors whose last names are DAVIS and first names are SUSAN:

SELECT *
FROM actor
WHERE last_name = 'DAVIS'
  AND first_name = 'SUSAN';

In this statement, last_name = 'DAVIS' AND first_name = 'SUSAN' means that the row’s last_name column has a value DAVIS, and the row’s first_name has a value SUSAN.

last_name = 'DAVIS' is a condition and first_name = 'SUSAN' is a condition too. Combining the two by AND means that the row must meet these two conditions at the same time.

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

We can also query actor the table for actors whose last names are DAVIS and actor_id are less than 100:

SELECT *
FROM actor
WHERE last_name = 'DAVIS'
  AND actor_id < 100;

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Conclusion

In this article, we learned the syntax and operation rules of the AND operator. The main points of the AND operator are as follows:

• The AND operator is a binary operator and requires two Boolean operands.
• If neither operand is 0 (FALSE) and NULL, then AND returns 1.
• If an operand is 0 (FALSE), then AND returns 0.
• If one of the two operands is NULL and the other is not 0 (FALSE), then AND returns NULL.
• The order of the operands does not affect the result of the AND operator.