MySQL AND operator

AND operator

MySQL logical AND operator compares two expressions and returns true if both of the expressions are true.

This function is useful in -

• AND ensures that all conditions specified in a query must be true for a row to be included in the result set.
• It enables you to express logical relationships between conditions, such as "A AND B AND C" where all conditions must be satisfied.
• By using AND judiciously, you can optimize your queries to efficiently retrieve the desired data.
• In more advanced queries, especially those involving joins and subqueries, AND is used to establish relationships between different parts of the query.
• By using AND, you can refine your queries to retrieve a more specific set of results.
• When combined with UPDATE and DELETE statements, AND allows you to specify multiple conditions that must be met for the operation to be executed.
• It's useful for combining different filters based on various attributes or columns.

Syntax:

AND, &&

This operator returns 1 if all operands are nonzero and not NULL, 0 if one or more operands are 0, otherwise, NULL is returned.

MySQL Version: 8.0

Example: MySQL AND operator

In the following MySQL statement all the operands are non-zero (5 and 5) and both of them are not NULL, so logical and operator returns 1.

Code:

SELECT 5 && 5;

Output:

MySQL> SELECT 5 && 5;
+--------+
| 5 && 5 |
+--------+
|      1 | 
+--------+
1 row in set (0.00 sec)

Example of MySQL AND operator with zero input

In the following MySQL statement, one of the operands is non zero (5 and 0), so logical and operator returns 0.

Code:

SELECT 5 && 0;

Output:

MySQL> SELECT 5 && 0;
+--------+
| 5 && 0 |
+--------+
|      0 | 
+--------+
1 row in set (0.00 sec)

Example of MySQL AND operator with NULL input

In the following MySQL statement, one of the operands is non zero (5) and another one is NULL, so logical and operator returns NULL.

Code:

SELECT 5 && NULL;

Sample Output:

MySQL> SELECT 5 && NULL;
+-----------+
| 5 && NULL |
+-----------+
|      NULL | 
+-----------+
1 row in set (0.00 sec)

Example of MySQL AND operator with zero and NULL input

In the following MySQL statement, one of the operands is zero (0) and another one is NULL, so logical and operator returns 0.

Code:

SELECT 0 && NULL;

Output:

MySQL> SELECT 0 && NULL;
+-----------+
| 0 && NULL |
+-----------+
|         0 | 
+-----------+
1 row in set (0.00 sec)

Example of MySQL AND operator with both inputs are NULL

In the following MySQL statement, all the operands are NULL. So the operator returns NULL.

Code:

SELECT NULL && NULL;

Output:

MySQL> SELECT NULL && NULL;
+--------------+
| NULL && NULL |
+--------------+
|         NULL | 
+--------------+
1 row in set (0.00 sec)

Previous: NOT LIKE
Next: NOT operator