5.3 — Subquery Fundamentals

What You'll Learn

• What subqueries are and why they’re useful
• How to use subqueries for dynamic filtering
• Excel analogy for subqueries

What is a Subquery?

A subquery is a query inside another query. It lets you use the result of one query as input for another—like using a helper table or formula in Excel.

Sample Tables

Table sales:

Product	Amount	SaleDate
Apples	120	2024-05-01
Oranges	80	2024-05-01
Bananas	50	2024-05-02
Pears	60	2024-05-02
Grapes	90	2024-05-03

Table featured_products:

Product
Apples
Oranges

Table discontinued_products:

Product
Bananas
Grapes
NULL

Example: Filter with a Subquery

Show sales for products with above-average sales:

SELECT *
FROM sales
WHERE Amount > (
  SELECT AVG(Amount)
  FROM sales
);

• The subquery calculates the average Amount.
• The main query returns rows where Amount is above that average.

Example: IN with Subquery

Show sales for products in a specific list:

SELECT *
FROM sales
WHERE Product IN (
  SELECT Product
  FROM featured_products
);

• The subquery returns a list of products from another table.

Example: NOT IN with Subquery

Show sales for products that are not in a specific list:

SELECT *
FROM sales
WHERE Product NOT IN (
  SELECT Product
  FROM discontinued_products
);

• The subquery returns a list of discontinued products.
• The main query returns only sales for products not in that list.

⚠️ Warning about NULLs:
If the subquery returns any NULL values, NOT IN will return no rows at all!
This is because Product NOT IN (list with NULL) is never TRUE for any value.

How to avoid issues:
Always filter out NULL values in the subquery:

SELECT *
FROM sales
WHERE Product NOT IN (
  SELECT Product
  FROM discontinued_products
  WHERE Product IS NOT NULL
);

OR set NULL to a default value with COALESCE:

SELECT *
FROM sales
WHERE Product NOT IN (
  SELECT COALESCE(Product, '???')
  FROM discontinued_products
);

Key Points

• Subqueries make your filters dynamic and powerful.
• They can be used in WHERE, IN, EXISTS, and even in the SELECT clause.

Next Steps

Next, you’ll learn how to migrate your Excel workflows to SQL for more efficient analysis.