MySQL SubQuery with Examples

What is a SubQuery in SQL?

A subquery is a SELECT query that is contained inside another query. The inner select query is usually used to determine the results of the outer select query, so the database evaluates the inner statement first and then passes its output upwards.

The inner query is called the inner query or nested query, and the statement that contains it is called the outer query. Let us look into the sub query syntax.

The diagram above shows the general shape of the statement: the outer SELECT supplies the columns you want to see, and the bracketed inner SELECT supplies the value or the list of values that the WHERE clause compares against.

Why Use a SubQuery?

Before looking at the different types, it helps to know when a subquery earns its place in a statement.

A subquery answers a question whose filter value is not known in advance. It has to be calculated from the data itself. A common customer complaint at the MyFlix Video Library is the low number of movie titles, and the management wants to buy movies for the category which has the least number of titles. Nobody knows which category that is until the database is asked, so the value must be computed first and then used as a filter.

Subqueries are attractive for three practical reasons:

• Readability: Each part of the logic sits in its own bracketed block, so the statement reads as a sequence of small questions rather than one complex expression.
• Isolation: An inner query can be run on its own to confirm that it returns the expected value, which makes testing and debugging far easier.
• Flexibility: The same pattern works in the WHERE, HAVING, SELECT, and FROM clauses, and also inside INSERT, UPDATE, and DELETE statements.

The trade-off is speed, which is examined in the JOIN comparison later in this article.

Types of SubQueries in MySQL

MySQL supports three types of subqueries, and the type is decided by the shape of the result that the inner query returns. Each type is explained below with a working example against the myflixdb database.

1) Scalar SubQuery

A scalar subquery returns exactly one row and one column, which means it returns a single value. Because the result is a single value, it can be used anywhere a literal value is allowed. Returning to the MyFlix problem above, you can use a query like this one:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

It gives a result:

Let us see how this query works.

As the execution diagram shows, MySQL first runs SELECT MIN(category_id) FROM movies, receives one value, and only then runs the outer query with that value in place. Because a single value is returned, the permissible operators are the standard comparison set: =, <> (or !=), >, >=, <, and <=.

💡 Tip: If a subquery placed after = returns more than one row, MySQL raises error 1242, Subquery returns more than 1 row. Switch the operator to IN, or tighten the inner WHERE clause.

2) Row SubQuery

A row subquery also returns a single row, but that row may carry more than one column. The outer query therefore compares a row of values against a row constructor rather than against a single value.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

The permissible operators are the same comparison operators listed above, applied to the whole row at once.

3) Table SubQuery

A table subquery returns multiple rows, and often multiple columns, so the outer query must use a set operator such as IN, NOT IN, ANY, ALL, or EXISTS.

Suppose you want names and phone numbers of members who have rented a movie and are yet to return it, so that you can call them up to give a reminder. You can use a query like this one:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Let us see how this query works.

In this case, the inner query returns more than one result, so the list of membership numbers is handed to the IN operator and every matching member is returned.

Nesting SubQueries Several Levels Deep

So far you have seen two levels. A subquery may also contain another subquery, which produces a triple nested statement.

Suppose the management wants to reward the highest paying member. We can run a query like this one:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

The innermost query finds the largest payment, the middle query converts that amount into a membership number, and the outer query converts the membership number into a name. The above query gives the following result:

How to Use SubQueries with INSERT, UPDATE, and DELETE

Subqueries are not limited to SELECT statements. The same bracketed pattern works inside data modification statements, which allows a whole set of rows to be changed in one pass without creating a temporary table.

INSERT with a subquery. A subquery can supply the rows that are inserted, which copies data from one table into another. The column list of the SELECT must line up with the column list of the INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

UPDATE with a subquery. Here the inner query decides which rows are touched. The example below flags every member whose rental is still outstanding.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

DELETE with a subquery. The same idea removes rows that satisfy a condition held in a second table.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Warning: MySQL does not allow a statement to modify a table and select from the same table inside a subquery in the FROM clause. If error 1093 appears, wrap the inner query in a derived table, for example SELECT * FROM (SELECT ...) AS t, so that MySQL materialises the result before the change is applied. It is also wise to run the inner SELECT on its own first, and to confirm the row count, before running an UPDATE or a DELETE in production.

SubQueries vs Joins

Both a subquery and a JOIN can combine information from more than one table, so the natural question is which one to reach for.

When compared with joins, sub-queries are simple to use and easy to read. They are not as complicated as Joins, and hence they are frequently used by SQL beginners.

But sub-queries have performance issues. Using a join instead of a sub-query can at times give you up to 500 times performance boost, because the optimiser can resolve a join in a single pass instead of evaluating the inner statement repeatedly.

Given a choice, it is recommended to use a JOIN over a sub query. Sub-queries should only be used as a fallback solution when you cannot use a JOIN operation to achieve the above.

Subqueries are also easy to break down into single logical components, which is very useful when testing and debugging the queries.

FAQs