Subqueries: Queries Inside Queries

Introduction

Sometimes you cannot write the answer in one step. "Which products cost more than the average?" first needs the average, then needs a comparison. A subquery is a SELECT inside another query, written in brackets. The inner query runs first and hands its result to the outer one.

A subquery that returns one value

That is the average price. You could copy that number into a second query, but then it would go stale. Instead nest it:

The inner query is calculated once, and the outer query compares each product to the result.

You can also show the value as a column, to see the comparison:

A subquery that returns a list: IN

If the inner query returns one column of many rows, use IN.

Be careful: NOT IN returns nothing at all if the list contains a NULL. If the column can be empty, add WHERE column IS NOT NULL to the inner query, or use NOT EXISTS.

EXISTS

EXISTS asks "does the inner query return any row at all?". The inner query can look at the current outer row, which is called a correlated subquery.

This lists customers who have at least one shipped order. The SELECT 1 is a convention: EXISTS only cares whether a row exists, not what it contains.

A subquery in FROM

A subquery can act like a temporary table. Give it an alias.

First the inner query calculates the value of each line item. Then the outer query averages them per category. The advanced course shows a cleaner way to write this with a WITH clause.

Try it yourself

List the products whose price is below the average price of their own category. Hint: use a correlated subquery that compares category to the outer product's category.

Summary

• A subquery is a SELECT in brackets inside another query.
• A single-value subquery works with >, = and the other comparisons.
• IN checks a list and EXISTS checks whether any row exists. Prefer NOT EXISTS when NULLs are possible.
• A subquery in FROM acts like a temporary table and needs an alias.