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 that is nested inside another query such as SELECT, INSERT, UPDATE or DELETE. A MySQL subquery is also can be nested inside another subquery. A MySQL subquery is also called an inner query, while the query that contains the subquery is called an outer query.
Let’s take a look at the following subquery that returns employees who locate in the offices in the USA.
The subquery returns all offices codes of the offices that locate in the USA.
The outer query selects the last name and first name of employees whose office code is in the result set returned from the subquery.
You can use a subquery anywhere an expression can be used. A subquery also must be enclosed in parentheses.
MySQL subquery within a WHERE clause
MySQL subquery with comparison operators
If a subquery returns a single value, you can use comparison operators to compare it with the expression in the WHERE clause. For example, the following query returns the customer who has the maximum payment.
SELECT customerNumber, checkNumber, amount FROM payments WHERE amount = ( SELECT MAX(amount) FROM payments )
You can also use other comparison operators such as greater than (>), less than(<), etc. For example, you can find customer whose payment is greater than the average payment. A subquery is used to calculate the average payment by using the AVG aggregate function. The outer query selects payments that are greater than the average payment returned from the subquery.
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 IN operator in the WHEREclause. For example, you can use a subquery with NOT IN operator to find customer who has not ordered any product as follows:
SELECT customername FROM customers WHERE customerNumber NOT IN( SELECT DISTINCT customernumber FROM orders )
MySQL subquery with EXISTS and NOT EXISTS
When a subquery is used with EXISTS or NOT EXISTS operator, a subquery returns a Boolean value of TRUE or FALSE. The subquery acts as an existence check.
In the following example, we select a list of customers who have at least one order with total sales greater than 10K.
First, we build a query that checks if there is at least one order with total sales greater than 10K:
SELECT priceEach * quantityOrdered FROM orderdetails WHERE priceEach * quantityOrdered > 10000 GROUP BY orderNumber
The query returns 6 records so that when we use it as a subquery, it will return TRUE; therefore the whole query will return all customers:
SELECT customerName FROM customers WHERE EXISTS ( SELECT priceEach * quantityOrdered FROM orderdetails WHERE priceEach * quantityOrdered > 10000 GROUP BY orderNumber )
If you replace the EXISTS by NOT EXIST in the query, it will not return any record at all.
MySQL subquery in FROM clause
When you use a subquery in the FROM clause, the result set returned from a subquery is used as a 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:
SELECT max(items), min(items), floor(avg(items)) FROM (SELECT orderNumber, count(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS lineitems
Notice that the subquery returns the following result set that is used as a derived table for the outer query.
MySQL correlated subquery
In the previous examples, we see the subquery itself is independent. It means that you can execute the subquery as a normal query. However a correlated subquery is a subquery that uses the information from the outer query, or we can say that a correlated subquery depends on the outer query. A correlated subquery is evaluated once for each row in the outer query.
In the following correlated subquery, we select products whose buy price is greater than the average buy price of all products for a particular product line.
SELECT productname, buyprice FROM products AS 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.
In this tutorial, we have shown you how to use MySQL subquery and correlated subquery to write more complex queries.
随时随地看视频
