MySQL - Select using WHERE clause
MySQL
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.
| Operator | Example | Description |
|---|---|---|
| = | leftSide = rightSide | We use the Equal to operator to check if the left side is equal to the right side. |
| != | leftSide != rightSide | We use the Not equal to operator to check if the left side is not equal to the right side. |
| < | leftSide < rightSide | We use the Less than operator to check if the left side is less than the right side. |
| > | leftSide > rightSide | We use the Greater than operator to check if the left side is greater than the right side. |
| <= | leftSide <= rightSide | We use the Less than or equal to operator to check if the left side is less than or equal to the right side. |
| >= | leftSide >= rightSide | We use the Greater than or equal to operator to check if the left side is greater than or equal to the right side. |
| IN | leftSide IN ('val1', 'val2', ...) | We use the IN to check if left side value is present in possible list of values. |
| LIKE | leftSide LIKE pattern | We use the LIKE operator to search for matching pattern. |
| BETWEEN | leftSide BETWEEN option1 AND option2 | We use the BETWEEN operator to check if left side value is in between option1 and option2, both inclusive. |
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)