How to write subquery in MySQL to return a single value (known as single-value subquery or scalar subquery)

Using subquery in SELECT statement and WHERE clause

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 a single value

When the subquery returns a single value, the subquery is only evaluated once and then the value is returned to outer query to use. This kind of subqueries are also known as single-value subquery or scalar subquery.

The subquery can be used in either SELECT statement or WHERE clause. In either case, an aggregate function or an expression is normally used in the subquery.

When the subquery is used in WHERE clause, a comparison operator is always used.

Practice #1-1: Use subquery in WHERE clause with an aggregate function.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query returns data for all customers and their 
orders where the orders were shipped on the most 
recent recorded day.
 
The execution steps:
 
1. The subquery 
 
   select max(ShippedDate) from orders
 
in the WHERE clause uses aggregate function max to return 
the maximum ship date in the orders table and it returns 
1998-05-06 00:00:00
 
2. Then, 1998-05-06 00:00:00 is used in the outer query to 
compare with ShippedDate.
 
   select OrderID, CustomerID
   from orders
   where ShippedDate = '1998-05-06 00:00:00'
 
*/
select OrderID, CustomerID
from orders
where ShippedDate = (select max(ShippedDate) from orders);

Query result set - 3 rows returned:
The orders shipped on the most recent date.

Practice #1-2: Use subquery in WHERE clause with an aggregate function.

/*
This query returns all products whose unit price
is greater than average unit price.
*/
select distinct ProductName, UnitPrice
from products
where UnitPrice>(select avg(UnitPrice) from products)
order by UnitPrice desc;

Query result set - 25 rows returned:
Products with greater than average unit price.

Practice #2: Use subquery in SELECT statement with an aggregate function.

/*
This query lists the percentage of total units in 
stock for each product.
 
The subquery 
 
    select sum(UnitsInStock) from products
 
works out the total units in stock which is 3119.
 
Then 3119 is used in the outer query to calculate the
percentage of total units in stock for each product.
*/
select ProductID,
       ProductName,
       concat((UnitsInStock / (select sum(UnitsInStock) from products))*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;
 
/*
This query returns the same result as query above.
Here 3119 is hardcoded whereas query above uses
subquery to calculate 3119 on the fly.
*/
select ProductID,
       ProductName,
       concat((UnitsInStock / 3119)*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;

Query result set - 77 rows returned:
Use subquery in SELECT statement with an aggregate function.

Practice #3: Use subquery and join clause.

/*
This query finds the shipping company (or companies)
that charged the highest freight. 
 
The subquery returns the max freight from orders table.
Then the max freight is used in outer query as criteria
to retrieve the shipping company's ID and name in the
joined table shippers.
*/
select a.ShipperID, 
       a.CompanyName,
       b.Freight
from shippers as a
inner join orders as b on a.ShipperID=b.ShipVia
where b.Freight = (select max(Freight) from orders);

Query result set - 1 row returned:
Use subquery and join clause.

Next tutorial: Using subquery to return a list of values
Back to Section Index: Using Subquery
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search