Custom Search
 


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


Inside This Tutorial Series
  1. Using subquery to return a single value
  2. Using subquery to return a list of values
  3. Using subquery to return one ore more rows of values
  4. Using correlated subqueries
  5. 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.

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 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.

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 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.

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 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.





Other tutorials in this category

1. What is subquery

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

3. Using subquery to return one ore more rows of values (known as row 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


Copyright © 2014 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy