MySQL Subquery?

Summary: in this tutorial, we will show you how to use the MySQL subquery to write complex queries and explain the correlated subquery concept.

A MySQL subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. In addition, a MySQL subquery can be nested inside another subquery.

A MySQL subquery is called an inner query while the query that contains the subquery is called an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses.

The following query returns employees who work in the offices located in the USA.

1 2 3 4 5 6 7 8 9 10 11

SELECT     lastName, firstName FROM     employees WHERE     officeCode IN (SELECT             officeCode         FROM             offices         WHERE             country = 'USA');

In this example:

• The subquery returns all office codes of the offices located in the USA.
• The outer query selects the last name and first name of employees who work in the offices whose office codes are in the result set returned by the subquery.

When the query is executed, the subquery runs first and returns a result set. Then, this result set is used as an input of the outer query.

MySQL subquery in WHERE clause

We will use the payments table in the sample database for the demonstration.

MySQL subquery with comparison operators

You can use comparison operators e.g., =, >, <, etc., to compare a single value returned by the subquery with the expression in the WHERE clause.

For example, the following query returns the customer who has the maximum payment.

1 2 3 4 5 6 7 8 9

SELECT     customerNumber, checkNumber, amount FROM     payments WHERE     amount = (SELECT             MAX(amount)         FROM             payments);

In addition to the equality operator, you can use other comparison operators such as greater than ( >), less than( <), etc.

For example, you can find customers whose payments are greater than the average payment using a subquery. First, use a subquery to calculate the average payment using the AVG aggregate function. Then, in the outer query, query the payments that are greater than the average payment returned by the subquery.

1 2 3 4 5 6 7 8 9

SELECT     customerNumber, checkNumber, amount FROM     payments WHERE     amount > (SELECT             AVG(amount)         FROM             payments);

MySQL subquery with IN and NOT IN operators

If a subquery returns more than one value, you can use other operators such as IN or NOT INoperator in the WHERE clause.

See the following customers and orders tables:

For example, you can use a subquery with NOT IN operator to find the customers who have not placed any orders as follows:

1 2 3 4 5 6 7 8 9

SELECT     customerName FROM     customers WHERE     customerNumber NOT IN (SELECT DISTINCT             customerNumber         FROM             orders);

MySQL subquery in the FROM clause

When you use a subquery in the FROM clause, the result set returned from a subquery is used as a temporary table. This table is referred to as a derived table or materialized subquery.

The following subquery finds the maximum, minimum and average number of items in sale orders:

1 2 3 4 5 6 7 8

SELECT     MAX(items), MIN(items), FLOOR(AVG(items)) FROM     (SELECT         orderNumber, COUNT(orderNumber) AS items     FROM         orderdetails     GROUP BY orderNumber) AS lineitems;

MySQL correlated subquery

In the previous examples, you notice that a subquery is independent. It means that you can execute the subquery as a standalone query, for example:

1 2 3 4 5 6

SELECT     orderNumber,     COUNT(orderNumber) AS items FROM     orderdetails GROUP BY orderNumber;

Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the outer query. In other words, a correlated subquery depends on the outer query. A correlated subquery is evaluated once for each row in the outer query.

In the following query, we select products whose buy prices are greater than the average buy price of all products in each product line.

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     productname,     buyprice FROM     products p1 WHERE     buyprice > (SELECT             AVG(buyprice)         FROM             products         WHERE             productline = p1.productline)

The inner query executes for every product line because the product line is changed for every row. Hence, the average buy price will also change. The outer query filters only products whose buy price is greater than the average buy price per product line from the subquery.

MySQL subquery with EXISTS and NOT EXISTS

When a subquery is used with the EXISTS or NOT EXISTS operator, a subquery returns a Boolean value of TRUE or FALSE.  The following query illustrates a subquery used with the EXISTS operator:

1 2 3 4 5 6

SELECT     * FROM     table_name WHERE     EXISTS( subquery );

In the query above, if the subquery returns any rows, EXISTS subquery returns TRUE, otherwise, it returns FALSE.

The EXISTS and NOT EXISTS are often used in the correlated subqueries.

Let’s take a look at the orders and orderDetails table in the sample database:

The following query selects sales orders whose total values are greater than 60K.

1 2 3 4 5 6 7 8 9

SELECT     orderNumber,     SUM(priceEach * quantityOrdered) total FROM     orderdetails         INNER JOIN     orders USING (orderNumber) GROUP BY orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;

It returns 3 rows, meaning that there are 3 sales orders whose total values are greater than 60K.

You can use the query above as a correlated subquery to find customers who placed at least one sales order with the total value greater than 60K by using the EXISTS operator:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT     customerNumber,     customerName FROM     customers WHERE     EXISTS( SELECT             orderNumber, SUM(priceEach * quantityOrdered)         FROM             orderdetails                 INNER JOIN             orders USING (orderNumber)         WHERE             customerNumber = customers.customerNumber         GROUP BY orderNumber         HAVING SUM(priceEach * quantityOrdered) > 60000);

In this tutorial, we have shown you how to use MySQL subquery and correlated subquery to construct more complex queries.