MySQL tutorial - 8 Sub query
Tags: mysql, beginners, tutorial
Published: 2021-03-22 23:52:32

In MySQL, a single query can be split into many queries for some use cases. This mechanism is called sub query. The sub query can be useful to solve a certain problem. This is the basic query structure of using sub query. Notice that the sub query is wrapped inside the parenthesis `()`. The sub query is mainly used with `SELECT` query.

```
QUERY_SAMPLE ( SUB_QUERY_SAMPLE )
```

> The sub query will be executed first after outer query.

## Sub query returns single columns

This is the example of using sub query mechanism. In this case, using the `SELECT` query to retrieve a data from the `shop` table that has a price less than a price from a `Low Fat Milk` data.

```sql
SELECT * FROM shop WHERE price < (SELECT shop.price FROM shop WHERE shop.product_name = "Low Fat Milk");
```

This is the result from the query above.

```
+----+--------------+----------+-------+
| id | product_name | quantity | price |
+----+--------------+----------+-------+
|  3 | Apple        |      110 |   7.8 |
|  5 | Corn Flakes  |       19 |  2.99 |
+----+--------------+----------+-------+
```

## Sub query returns scalar values

Sub query may returns a single column of data, multiple columns or even a scalar value like numbers. In this example, the sub query returns a scalar value from a `AVG` operation.

```sql
-- select the data where the price is greater than the price average.
SELECT * FROM shop WHERE price > (SELECT AVG(price) FROM shop);
```

This is the result from the query above.

```
+----+---------------+----------+-------+
| id | product_name  | quantity | price |
+----+---------------+----------+-------+
|  1 | Mango         |       90 |  12.5 |
|  2 | Low Fat Milk  |       15 |   8.8 |
|  4 | Fresh Chicken |       25 |  10.3 |
+----+---------------+----------+-------+
```

## Sub query returns multiple columns

The sub query that returns multiple columns is usually used together with `ANY`, `ALL`, `IN` and `SOME` clause. This is the example of sub query usage with `IN` clause.

```sql
-- select the data that has a quantity more than 20
SELECT * FROM shop WHERE quantity IN (SELECT quantity FROM shop WHERE quantity > 20);
```

This is the result from the query above.

```
+----+---------------+----------+-------+
| id | product_name  | quantity | price |
+----+---------------+----------+-------+
|  1 | Mango         |       90 |  12.5 |
|  3 | Apple         |      110 |   7.8 |
|  4 | Fresh Chicken |       25 |  10.3 |
+----+---------------+----------+-------+
```

This is another example of sub query usage with `ANY` and `SOME` clause. these clause will return the same result.

```sql
-- using ANY clause
-- select the title and content from posts table where the user_id is retrieved from username called nathan
SELECT title, content FROM posts WHERE user_id = ANY (SELECT id FROM users WHERE username = "nathan");
```

```sql
-- using SOME clause
-- select the title and content from posts table where the user_id is retrieved from username called nathan
SELECT title, content FROM posts WHERE user_id = SOME (SELECT id FROM users WHERE username = "nathan");
```

This is the output from both query above.

```
+-------------+---------------+
| title       | content       |
+-------------+---------------+
| title three | content three |
| title four  | content four  |
+-------------+---------------+
```

Another clause that can be used together with the sub query are `EXISTS` and `NOT EXISTS`. `EXISTS` means that certain data is available inside the sub query when the `NOT EXISTS` is the opposite of `EXISTS`. This is the example of sub query usage with `EXISTS` clause.

> `EXISTS` clause returns true if rows of data is exists although has a null value.

```sql
-- select the product data that has inserted inside the cart table.
SELECT * FROM shop s WHERE EXISTS (SELECT * FROM cart WHERE product_id = s.id);
```

This is the output from the query above.

```
+----+--------------+----------+-------+
| id | product_name | quantity | price |
+----+--------------+----------+-------+
|  1 | Mango        |       90 |  12.5 |
|  2 | Low Fat Milk |       15 |   8.8 |
+----+--------------+----------+-------+
```

This is the example of `NOT EXISTS` usage.

```sql
-- select the product data that has not inserted inside the cart table.
SELECT * FROM shop s WHERE NOT EXISTS (SELECT * FROM cart WHERE product_id = s.id);
```

This is the output from query above.

```
+----+---------------+----------+-------+
| id | product_name  | quantity | price |
+----+---------------+----------+-------+
|  3 | Apple         |      110 |   7.8 |
|  4 | Fresh Chicken |       25 |  10.3 |
|  5 | Corn Flakes   |       19 |  2.99 |
+----+---------------+----------+-------+
```

## Notes

- Learn more about sub query in [here](https://www.w3resource.com/mysql/subqueries/index.php).

I hope this article is helpful for learning SQL, If you have any thoughts or comments you can write in the discussion section below.