Using subqueries in the WHERE clause

Using subqueries in the WHERE clause

Subqueries in the WHERE clause work as part of the row selection process. You use a subquery in the WHERE clause when the criteria you use to select rows depend on the results of another table.

Example

Find the products whose in-stock quantities are less than double the average ordered quantity.

SELECT name, description
FROM product WHERE quantity <  2 * (
   SELECT avg(quantity)
   FROM sales_order_items)

This is a two-step query: first, find the average number of items requested per order; and then find which products in stock number less than double that quantity.

The query in two steps

The quantity column of the sales_order_items table stores the number of items requested per item type, customer, and order. The subquery is

SELECT avg(quantity)
FROM sales_order_items

It returns the average quantity of items in the sales_order_items table, which is 25.851413.

The next query returns the names and descriptions of the items whose in-stock quantities are less than twice the previously-extracted value.

SELECT name, description
FROM product
WHERE quantity < 2*25.851413

Using a subquery combines the two steps into a single operation.

Purpose of a subquery in the WHERE clause

A subquery in the WHERE clause is part of a search condition. The chapter Queries: Selecting Data from a Table describes simple search conditions you can use in the WHERE clause.