Using subquery to return one or more rows of values (known as row subquery)

Inside This Tutorial Series

Using subquery to return a single value
Using subquery to return a list of values
Using subquery to return one ore more rows of values
Using correlated subqueries
Using EXISTS and NOT EXISTS with correlated subqueries

Using subquery to return one ore more rows of values

A subquery that returns one or more rows of values is also known as row subquery. A row subquery is a subquery variant that returns one or more rows and can thus return more than one column value.

When the subquery returns one or more rows of values, the subquery is only evaluated once and then the row(s) of values is returned to outer query to use. Row subquery is MySQL specific. Other major database systems (SQL Server, Oracle, etc) don't have this type of subquery.

Row subquery is normally used in WHERE clause with an IN operator or other comparison operators that tests each value in the returned row from the subquery.

Practice #1: Use subquery to return rows of values.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query finds out all the employees who live
in the same city and country as customers.
 
The subquery returns a table of two columns and
91 rows. It's returned to outer query and City
and Country in employees table are compared with
each row in the table.
*/
select EmployeeID, FirstName, LastName, City, Country
from employees
where row(City, Country) in
(select City, Country from customers);

Query result set - 7 rows returned:
Use subquery to return rows of values.

Practice #2: Use subquery to return rows of values.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
Because a product can be sold at a discount, 
we want to know the highest unit price ever 
sold for each product.
 
This query reveals the highest unit price
for each product sold. 
 
The inner query returns a temporary table that 
contains ProductID and maximum price for the product.
 
This temporary table is then returned to outer query to use.
*/
select distinct ProductID, UnitPrice as Max_unit_price_sold
from order_details
where row(ProductID, UnitPrice) in
(
    select ProductID, max(UnitPrice) 
    from order_details 
    group by ProductID
)
order by ProductID;
 
/*
This query returns the exact same result as the one above. 
It uses inner join to rephrase the query above and 
the query performance is dramatically improved.
*/
select distinct a.ProductID, a.UnitPrice as Max_unit_price_sold
from order_details as a
inner join 
(
    select ProductID, max(UnitPrice) as Max_unit_price_sold
    from order_details 
    group by ProductID
) as b
on a.ProductID=b.ProductID and a.UnitPrice=b.Max_unit_price_sold
order by a.ProductID;

Query result set - 77 rows returned:
Use subquery to return rows of values.

Other tutorials in this category

1. What is subquery

2. Using subquery to return a single value (known as single-value subquery or scalar subquery)

3. Using subquery to return a list of values (known as column subquery)

4. Using correlated subqueries

5. Using EXISTS and NOT EXISTS in correlated subqueries in MySQL

6. Using subquery in SELECT statement in MySQL

7. Using subquery in FROM clause in MySQL

8. JOIN a table with a subquery

Back to Tutorial Index Page

	Custom Search