In this tutorial we will learn to select data from tables using the where clause in MySQL.

We covered how to select rows from tables in the SELECT FROM Table tutorial. Feel free to check that out.

We use the WHERE clause to filter the result based on some conditions.

WHERE syntax

Following is the syntax of the WHERE clause.

SELECT * FROM table_name
WHERE condition;

Where, condition is used to filter the result.

In this tutorial we will be using the employee and comments table that we have been working on in this tutorial series.

Employee Table

Following are the rows in the employee table.

mysql> SELECT * FROM employee;

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)

Comments Table

Following are the rows in the comments table.

mysql> SELECT * FROM comments;

Output:

+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2018-01-02 01:02:03 | 2018-01-02 01:02:03 |
|         3 | e01        | Happy               | 2018-01-02 01:04:03 | 2018-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

Operators for the WHERE clause

We use the following operators to check condition with the WHERE clause.

Select using WHERE clause

The = and != operator

In the following example we are selecting details of employee having employeeid equal to 'e01'.

mysql> SELECT * FROM employee
WHERE employeeid = 'e01';

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 |
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

Similary, we can use the not equal to != operator to select all the rows that does not match the value 'e01'.

In the following example we are selecting all the employees not having employeeid equal to 'e01'.

mysql> SELECT * FROM employee
WHERE employeeid != 'e01';

Enclose text value in single quotes like the following.
employeeid != 'e01'

The > and < operator

In the following example we are selecting rows having score greater than 5 from the employee table.

mysql> SELECT * FROM employee
WHERE score > 5;

Outpupt:

+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| 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 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

Similarly, we can use the less than < operator to select employee having score < 5.

mysql> SELECT * FROM employee
WHERE score < 5;

We don't have to enclose numeric value in single quotes.

The <= and >= operators

In the following example we are selecting all the employees born on or before '1900-05-01' using the <= operator.

mysql> SELECT * FROM employee
WHERE 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 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

Similarly, we can select all the employees born on or after '1900-05-01' using the >= operator.

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

IN operator

In the following example we are selecting details of employeeid 'e01', 'e03' and 'e05'.

mysql> SELECT * FROM employee
WHERE employeeid IN ('e01', 'e03', 'e05');

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 |
| e03        | Jane      | Doe      | janedoe@example.com  |     9 | 1900-05-20 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 |
| 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)

LIKE operator

We use the LIKE operator to match pattern. There are three ways we can use the LIKE operator as shown below.

• Match pattern at the start of a value.
• Match pattern at the end of a value.
• Match pattern in between a value.

LIKE 'pattern%' - match starting

In the following example we are selecting all the employees having firstname starting with 'J'.

mysql> SELECT * FROM employee
WHERE firstname LIKE 'J%';

Output:

+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| 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 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.01 sec)

LIKE '%pattern' - match ending

In the following example we are selecting all the employees having lastname ending with 'oe'.

mysql> SELECT * FROM employee
WHERE lastname LIKE '%oe';

Output:

+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email               | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| 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 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

LIKE '%pattern%' - match in between

In the following example we are selecting all the comments having the pattern 'co' in the commentbody column.

mysql> SELECT * FROM comments
WHERE commentbody LIKE '%co%';

Output:

+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         4 | e04        | This is my comment. | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2018-04-04 03:02:01 | 2018-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

BETWEEN operator

In the following example we are selecting all the employees born from '1900-03-01' to '1900-06-01' both date inclusive.

mysql> SELECT * FROM employee
WHERE birthday BETWEEN '1900-03-01' AND '1900-06-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 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
1 row in set (0.00 sec)