AND and OR Operators in MySQL Query

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

Ads









Related Tutorials/Questions & Answers:

Ads