MySQL - WHERE clause with AND, OR and NOT operators

In this tutorial we will learn to select data from tables using the where clause with AND, OR and NOT operators in MySQL.

Select using WHERE with AND operator

We use the AND operator with the WHERE clause to get the result only if all conditions is TRUE.

Syntax:

SELECT * FROM table_name
WHERE condition1
AND condition2;

For the above query we will fetch only those rows for which condition1 and condition2 both are TRUE.

In the following example we will fetch all the employees satisfying all the conditions given below.

• Employee with score > 5
• And employee born on or after '1900-05-01'

mysql> SELECT * FROM employee
WHERE score > 5
AND birthday >= '1900-05-01';

Output:

+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e03        | Jane      | Doe      | janedoe@example.com |     9 | 1900-05-20 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com  |     7 | 1900-10-20 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Select using WHERE with OR operator

We use the OR operator with the WHERE clause to get the result if any condition is TRUE.

Syntax:

SELECT * FROM table_name
WHERE condition1
OR condition2;

For the above query we will fetch all those rows for which either condition1 or condition2 is TRUE.

In the following example we will fetch all the employees satisfying any one of the given conditions.

• Employee with score > 5
• Or employee born on or after '1900-05-01'

mysql> SELECT * FROM employee
WHERE score > 5
OR birthday >= '1900-05-01';

Output:

+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     6 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
| e02        | John      | Doe      | johndoe@example.com  |     8 | 1900-02-03 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| e04        | Tin       | Tin      | tintin@example.com   |     7 | 1900-10-20 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     5 | 1900-08-20 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Select using WHERE with NOT operator

We use the NOT operator with the WHERE clause to get the result only if the condition is FALSE.

Syntax:

SELECT * FROM table_name
WHERE NOT condition;

For the above query we will fetch only those rows for which condition is FALSE.

In the following example we are fetching all the employees NOT having lastname as 'Doe'.

mysql> SELECT * FROM employee
WHERE NOT lastname = 'Doe';

Output:

+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        | Yusuf     | Shakeel  | yusuf@example.com    |     6 | 1900-01-01 | 2018-01-01 01:01:01 | 2018-01-01 01:01:01 |
| e04        | Tin       | Tin      | tintin@example.com   |     7 | 1900-10-20 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 |
| e05        | Bob       | Coder    | bobcoder@example.com |     5 | 1900-08-20 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)