MySQL - Subquery
MySQL
In this tutorial we will learn about subquery in MySQL.
A sub query is simply a query within a query.
Tables
In this tutorial we will be using the employee, orders and comments tables.
Table: employee
mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)
Table: orders
mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified | created |
+---------+------------+--------+-------------+---------------------+---------------------+
| 1 | e03 | 15.00 | OPEN | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
| 2 | e01 | 25.50 | OPEN | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
| 3 | e05 | 100.70 | CLOSED | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
| 4 | e02 | 22.18 | OPEN | 2018-01-02 03:04:05 | 2018-01-02 03:04:05 |
| 5 | e04 | 9.50 | CANCELLED | 2018-01-04 03:04:03 | 2018-01-04 03:04:03 |
| 6 | e04 | 99.99 | OPEN | 2018-02-02 03:03:04 | 2018-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)
Table: comments
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)
Example #1
In the following example we will list details of the employees who commented at least once.
To show the detail of the employee we can use the SELECT query and show the columns like employeeid, firstname and lastname.
mysql> SELECT
e.employeeid, e.firstname, e.lastname
FROM
employee e;
+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01 | Yusuf | Shakeel |
| e02 | John | Doe |
| e03 | Jane | Doe |
| e04 | Tin | Tin |
| e05 | Bob | Coder |
+------------+-----------+----------+
5 rows in set (0.00 sec)
So, we have the detail of the employees from the employee table.
Now, we will write the sub query in the WHERE clause to get only those employees who commented.
In the following query we are using the IN operator. Click here to learn more.
We are also using SELECT DISTINCT which will give us unique value.
mysql> SELECT
e.employeeid, e.firstname, e.lastname
FROM
employee e
WHERE
e.employeeid IN (SELECT DISTINCT
c.employeeid
FROM
comments c);
+------------+-----------+----------+
| employeeid | firstname | lastname |
+------------+-----------+----------+
| e01 | Yusuf | Shakeel |
| e03 | Jane | Doe |
| e04 | Tin | Tin |
+------------+-----------+----------+
3 rows in set (0.00 sec)
So, we are listing details of only those employees who commented at least once.
Example #2
In the following example we will show the detail of the employee and the total number of comments and total number of OPEN orders.
Lets say we want to display employeeid, firstname, lastname, total_comment and total_open_order in the result set.
To compute the total_comment and total_open_order value we will write two sub queries.
Sub query for the total_comment is as follows.
mysql> SELECT
COUNT(c.commentid)
FROM
comments c
WHERE
c.employeeid = e.employeeid;
Where, e is an alias for the employee table.
And sub query for the total_open_order is as follows.
mysql> SELECT
COUNT(o.orderid)
FROM
orders o
WHERE
o.employeeid = e.employeeid;
So, the final query with the two sub queries to get the desired result will be the following.
mysql> SELECT
e.employeeid,
e.firstname,
e.lastname,
(SELECT
COUNT(c.commentid)
FROM
comments c
WHERE
c.employeeid = e.employeeid) AS 'total_comment',
(SELECT
COUNT(o.orderid)
FROM
orders o
WHERE
o.employeeid = e.employeeid) AS 'total_open_order'
FROM
employee e;
+------------+-----------+----------+---------------+------------------+
| employeeid | firstname | lastname | total_comment | total_open_order |
+------------+-----------+----------+---------------+------------------+
| e01 | Yusuf | Shakeel | 2 | 1 |
| e02 | John | Doe | 0 | 1 |
| e03 | Jane | Doe | 2 | 1 |
| e04 | Tin | Tin | 1 | 2 |
| e05 | Bob | Coder | 0 | 1 |
+------------+-----------+----------+---------------+------------------+
5 rows in set (0.00 sec)
ADVERTISEMENT