MySQL
In this tutorial we will learn about subquery in MySQL.
A sub query is simply a query within a query.
In this tutorial we will be using the employee, orders and comments tables.
employee
orders
comments
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)
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.
SELECT DISTINCT
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.
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.
total_comment
total_open_order
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.
e
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)