DEV Community

nadirbasalamah
nadirbasalamah

Posted on

MySQL tutorial - 8 Sub query

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 )
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode

This is the result from the query above.

+----+--------------+----------+-------+
| id | product_name | quantity | price |
+----+--------------+----------+-------+
|  3 | Apple        |      110 |   7.8 |
|  5 | Corn Flakes  |       19 |  2.99 |
+----+--------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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");
Enter fullscreen mode Exit fullscreen mode
-- 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");
Enter fullscreen mode Exit fullscreen mode

This is the output from both query above.

+-------------+---------------+
| title       | content       |
+-------------+---------------+
| title three | content three |
| title four  | content four  |
+-------------+---------------+
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 |
+----+--------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 |
+----+---------------+----------+-------+
Enter fullscreen mode Exit fullscreen mode

Notes

  • Learn more about sub query in here.

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

Top comments (0)