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.
Following is the syntax of the WHERE clause.
SELECT * FROM table_name WHERE condition;
Where, condition is used to filter the result.
condition
In this tutorial we will be using the employee and comments table that we have been working on in this tutorial series.
employee
comments
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)
Following are the rows in the comments table.
mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+ | 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)
We use the following operators to check condition with the WHERE clause.
Equal to
Not equal to
Less than
Greater than
Less than or equal to
Greater than or equal to
IN
LIKE
BETWEEN
In the following example we are selecting details of employee having employeeid equal to 'e01'.
mysql> SELECT * FROM employee WHERE employeeid = 'e01';
+------------+-----------+----------+-------------------+-------+------------+---------------------+---------------------+ | 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'
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.
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';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | 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 the following example we are selecting details of employeeid 'e01', 'e03' and 'e05'.
mysql> SELECT * FROM employee WHERE employeeid IN ('e01', 'e03', 'e05');
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+ | 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)
We use the LIKE operator to match pattern. There are three ways we can use the LIKE operator as shown below.
In the following example we are selecting all the employees having firstname starting with 'J'.
mysql> SELECT * FROM employee WHERE firstname LIKE 'J%';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | 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)
In the following example we are selecting all the employees having lastname ending with 'oe'.
mysql> SELECT * FROM employee WHERE lastname LIKE '%oe';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | 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)
In the following example we are selecting all the comments having the pattern 'co' in the commentbody column.
commentbody
mysql> SELECT * FROM comments WHERE commentbody LIKE '%co%';
+-----------+------------+---------------------+---------------------+---------------------+ | 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)
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';
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+ | 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)