MySQL tutorial - 8 Sub query

#mysql #beginners #tutorial

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.

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.

-- 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.

-- 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.

-- 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");

-- 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.

-- 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.

-- 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 |
+----+---------------+----------+-------+