MySQL AND operator

2856 Views 0 Comments AND logical operator in MySQL, MySQL AND

In MySQL, there exists three logical operators namely AND, OR and NOT. In this post we are going to learn how AND operator works in MySQL.

The 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.

In simpler terms MySQL AND operator compares two expressions and returns true if both of the expressions are TRUE otherwise it returns FALSE. It is represented with “&” symbol.

Let us discuss about AND operator in simple cases and later on will discuss under different cases.

1. AND operator returns 1 when all the operands are non-zero and not NULL.

We can check it from the below example:

mysql> select 3 && 3;
+--------+
| 3 && 3 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 3 && 2;
+--------+
| 3 && 2 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select 3 && 0;

2. AND operator returns 1 or more operands are 0 , otherwise it returns NULL.

For example,

mysql> select 3 && 0;
+--------+
| 3 && 0 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select 3 && NULL;
+-----------+
| 3 && NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.02 sec)

3. What happens when the inputs are 0 and NULL!!

AND operator returns 0 when one of the operands is 0 and the other is NULL.

For example,

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

4.AND operator returns NULL when all the operands are NULL.

For example,

mysql> select NULL && NULL;
+--------------+
| NULL && NULL |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

In MySQL, What if there is a situation to evaluate an expression (which includes with AND operator). In such case, it evaluates the remaining parts of the expression until it can determine the result. This function is called short-circuit evaluation.

For example,

mysql> select 1=0 AND 1+0;
+-------------+
| 1=0 AND 1+0 |
+-------------+
|           0 |
+-------------+
1 row in set (0.01 sec)

In MySQL, Notice that 0 is FALSE and other than 0 are TRUE.

From the above example, we consider 1=0 as the first part of expression ‘1=0 AND 1+0’ . Since 1=0 evaluates to FALSE and so that MySQL can conclude as the final result of whole expression as FALSE. With this there is no need to evaluate the remaining part of the expression i.e., 1+0.

Till now we have seen how AND operator in MySQL works on simple statements. Let us discuss how AND operator in MySQL works with databases…

The AND operator is often used in the WHERE clause of the SELECT, UPDATE, DELETE statement to form Boolean expressions.

The syntax of AND operator is as follows:

SELECT column1, column2, … column n FROM table_name WHERE condition1 AND condition2 AND condition3 …condition n;

To demonstrate this with example, I am choosing my sample database as follows.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use sports;
Database changed
mysql> show tables;
+------------------+
| Tables_in_sports |
+------------------+
| players          |
| rankings         |
| rankings_1       |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from rankings_1;
+---------+------+-----------+
| name    | rank | country   |
+---------+------+-----------+
| Nadal   |    8 | Spain     |
| L Messi |   11 | Barcelona |
| C Marin |    3 | Spain     |
| Dhoni   |    1 | India     |
| Sachin  |    2 | India     |
| T Woods |   13 | Sweden    |
+---------+------+-----------+
6 rows in set (0.01 sec)

Now,

mysql> select * from rankings_1 where country="India" AND rank=2;
+--------+------+---------+
| name   | rank | country |
+--------+------+---------+
| Sachin |    2 | India   |
+--------+------+---------+
1 row in set (0.00 sec)

mysql> select name from rankings_1 where country="Spain" AND rank=3;
+---------+
| name    |
+---------+
| C Marin |
+---------+
1 row in set (0.00 sec)

In the above example we are combining only two expressions. But we can also combine more than two expressions. For example,

mysql> select name from rankings_1 where name="Nadal" AND country="Spain" AND rank<10;
+-------+
| name  |
+-------+
| Nadal |
+-------+
1 row in set (0.00 sec)

If the given condition fails it returns nothing. We can observe it from the below example:

mysql> select name from rankings_1 where name="Nadal" AND country="Spain" AND rank>10;
Empty set (0.00 sec)

(From the exact above two examples please notice the conditions where rank < 10 and rank>10.)

Example HTML page

VMS

MySQL AND operator

Leave a Reply Cancel reply

You May Also Like

MySQL Assignment 3

MySQL Assignment 7

MySQL NOT operator

Leave a Reply Cancel reply

Pin It on Pinterest