MySQL - Subquery

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.

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)