AND and OR Operators in MySQL Query

Hi,

We have a table with following data:

mysql> select * from email;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
5 rows in set (0.00 sec)

Now you can use the AND and OR operator with SQL in following ways:

select * from email where first_name='Deepak' OR 

first_name='Rajeev';

select * from email where first_name='Deepak' AND 

email_count=100;

select * from email where email_count>90 AND 

(first_name='Deepak' OR  first_name='Rajeev');

Here is the full log of MySQL client when these queries are run:

mysql> select * from email;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
5 rows in set (0.00 sec)

mysql> select * from email where first_name='deepak';
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
+------------+-----------+----------------------

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

mysql> select * from email where first_name='deepak' and 

last_name='Kumar';
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
+------------+-----------+----------------------

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

mysql> select * from email where email_count>100;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
4 rows in set (0.00 sec)

mysql> select * from email where email_count=100;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
+------------+-----------+----------------------

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

mysql> select * from email where email_count between 100 

and 200;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
+------------+-----------+----------------------

+-------------+
2 rows in set (0.00 sec)

mysql> select * from email;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
5 rows in set (0.00 sec)

mysql> select * from email where email is null;
+------------+-----------+-------+-------------+
| first_name | last_name | email | email_count |
+------------+-----------+-------+-------------+
| Ishu       | roy       | NULL  |         800 |
+------------+-----------+-------+-------------+
1 row in set (0.00 sec)

mysql> select * from email;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    15
Current database: tutorial

+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
5 rows in set (0.00 sec)

mysql> 
mysql> select * from email where first_name='Deepak' OR 

first_name='Rajeev';
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
+------------+-----------+----------------------

+-------------+
2 rows in set (0.00 sec)

mysql> select * from email where first_name='Deepak' AND 

email_count=100;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
+------------+-----------+----------------------

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

mysql> select * from email where email_count>90 AND 

(first_name='Deepak' OR  first_name='Rajeev');
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Deepak     | Kumar     | deepak@roseindia.net |         

100 |
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
+------------+-----------+----------------------

+-------------+
2 rows in set (0.00 sec)

mysql> select * from email where email_count>100;
+------------+-----------+----------------------

+-------------+
| first_name | last_name | email                | 

email_count |
+------------+-----------+----------------------

+-------------+
| Rajeev     | Singh     | rajeev@roseindia.net |         

500 |
| Dinesh     | Singh     | dinesh@roseindia.net |         

200 |
| John       | T         | john@roseindia.net   |         

400 |
| Ishu       | roy       | NULL                 |         

800 |
+------------+-----------+----------------------

+-------------+
4 rows in set (0.00 sec)

Thanks

Check following video:

Thanks