What is the difference Between AND, OR operator in MySQL while Retrieving the Rows?

The difference between AND, OR is that AND evaluates both conditions must be true for the overall condition to be true. The OR evaluates one condition must be true for the overall condition to be true.

Let us create a table −

mysql> create table demo70
−> (
−> id int not null auto_increment primary key,
−> name varchar(20),
−> age int
−> );
Query OK, 0 rows affected (0.67 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo70(name,age) values('John',23);
Query OK, 1 row affected (0.18 sec)

mysql> insert into demo70(name,age) values('David',21);
Query OK, 1 row affected (0.08 sec)

mysql> insert into demo70(name,age) values('Mike',22);
Query OK, 1 row affected (0.15 sec)

mysql> insert into demo70(name,age) values('Chris',20);
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo70(name,age) values('John',24);
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo70(name,age) values('David',22);
Query OK, 1 row affected (0.15 sec)

Display records from the table using select statement −

mysql> select *from demo70;

This will produce the following output −

+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | John  |   23 |
|  2 | David |   21 |
|  3 | Mike  |   22 |
|  4 | Chris |   20 |
|  5 | John  |   24 |
|  6 | David |   22 |
+----+-------+------+
6 rows in set (0.00 sec)

Following is the OR operator query −

mysql> select *from demo70
−> where name="John" or age=22;

This will produce the following output −

+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | John  |   23 |
|  3 | Mike  |   22 |
|  5 | John  |   24 |
|  6 | David |   22 |
+----+-------+------+
4 rows in set (0.00 sec)

In the OR result, if name is John then condition will be true. If any row has the age 22, then it will be true.

Let us now see the result of AND operator.

The query is as follows −

mysql> select *from demo70
−> where name="John" and age=22;

This will produce the following output −

Empty set (0.00 sec)

The AND gives empty set because no one row has the same name John and age 22.