The difference between 'AND' and '&&' in MySQL?

NOTE: There is only one difference between AND and && is that AND is a standard while && is ownership syntax.

Except the above statement, there is no difference between AND and &&. Let us look at all the conditions.

The result of AND and && will always be either 1 or 0. As we know the AND and && both are logical operators, if there are more than one operand and any one of them has value 0 then result becomes 0 otherwise 1.

Here is the demo of AND and &&.

Case 1(a): If both operands are 1. Using AND.

The query is as follows:

mysql> select 1 AND 1 as Result;

The following is the output:

+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Case 1(b): If both operands are 1. Using &&.

The query is as follows:

mysql> select 1 && 1 as Result;

The following is the output:

+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Case 2(a): If any one operand is 0 then result becomes 0. Using AND.

The query is as follows:

mysql> select 1 AND 0 as Result;

The following is the output:

+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Case 2(b): If any one operand is 0 then result becomes 0. Using &&.

The query is as follows:

mysql> select 1 && 0 as Result;

The following is the output:

+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Here is the NULL case.

Case 3(a): If any one operand is NULL then result becomes NULL. Using AND.

The query is as follows:

mysql> select NULL AND 1 as Result;

The following is the output:

+--------+
| Result |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

Case 3(b): If any one operand is NULL then result becomes NULL. Using &&.

The query is as follows:

mysql> select NULL && 1 as Result;

The following is the output:

+--------+
| Result |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

NOTE: The cases discussed above does not only depend on 1 and 0. Any non-zero value will be true that means if we do AND or && of two negative numbers then result becomes 1.

Look at the negative case. The query is as follows:

mysql> select -10 AND -30 as Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.04 sec)
mysql> select -10 && -30 as Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

In the above if any one value is 0 then result becomes 0 in both AND and &&. The query is as follows:

mysql> select -10 AND 0 as Result;
+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Look at the positive case. The queries are as follows:

mysql> select 10 AND 30 as Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> select 10 && 30 as Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

In this, if any one operand becomes 0, then the result goes to 0. The query is as follows:

mysql> select 10 and 0 as Result;
+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)
mysql> select 10 && 0 as Result;
+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)