AND and OR Operators in MySQL Query

Hi,

How to use the AND and OR Operators in MySQL Query?

Thanks

View Answers

June 26, 2016 at 10:25 PM

Hi,

We have a table with following data:

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

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

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

+-------------+
| Deepak     | Kumar     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

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     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

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     | [email protected] |         

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     | [email protected] |         

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     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

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     | [email protected] |         

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     | [email protected] |         

100 |
| Dinesh     | Singh     | [email protected] |         

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

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

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

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

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

+-------------+
| Deepak     | Kumar     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

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     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

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     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

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     | [email protected] |         

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     | [email protected] |         

100 |
| Rajeev     | Singh     | [email protected] |         

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     | [email protected] |         

500 |
| Dinesh     | Singh     | [email protected] |         

200 |
| John       | T         | [email protected]   |         

400 |
| Ishu       | roy       | NULL                 |         

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

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

Thanks

July 14, 2016 at 3:28 AM

Check following video:

Thanks