SQL queries can feel like magic when you need exactly the right answer from your data.
And subqueries? They’re the quiet superpower behind some of the cleanest, most powerful SQL you’ll ever write.

Think of them as a query inside another query — like slipping a sticky note into a sales log with the exact number you need. Whether you’re filtering high spenders, calculating dynamic thresholds, or updating records with precision, subqueries get the job done.

Let’s walk through what they are, how they work, and when to use them — using a realistic coffee shop dataset to keep things grounded.

What Is a Subquery?

A subquery is a SELECT statement nested inside another query. It runs first, returns a result (or results), and feeds it to the outer query.

They live in parentheses () and appear in places like:

• WHERE
• FROM
• SELECT
• SET (in updates)

Example: Find customers who spent more than average

SELECT CustomerName, Total
FROM Sales
WHERE Total > (SELECT AVG(Total) FROM Sales);

The subquery (SELECT AVG(Total) FROM Sales) calculates the average sale — say $5.50.
The outer query then returns every customer who spent more than $5.50.

Clean. Dynamic. No hard-coded values.

Types of Subqueries (With Coffee Shop Examples)

Let’s use two tables:

Sales

Employees

1. Single-Row Subqueries

Returns one value. Use with =, >, <, etc.

Find the customer with the highest sale:

SELECT CustomerName, Total
FROM Sales
WHERE Total = (SELECT MAX(Total) FROM Sales);

Warning: If the subquery returns more than one row, the query fails. Always use MAX, MIN, AVG, etc. for safety.

2. Multiple-Row Subqueries

Returns multiple rows. Use with IN, ANY, ALL.

Find customers who spent more than any sale on Jan 1, 2023:

SELECT CustomerName, Total
FROM Sales
WHERE Total > ANY (
  SELECT Total 
  FROM Sales 
  WHERE OrderDate = '2023-01-01'
);

If Jan 1 sales were $4, $6, and $8 → this finds anyone above $4 (the lowest).

• > ANY = “greater than the smallest”
• > ALL = “greater than the largest”
• IN = exact match

3. Correlated Subqueries

The subquery references the outer query — runs once per row.

Find baristas earning more than their manager:

SELECT Name, Salary
FROM Employees e1
WHERE Salary > (
  SELECT Salary 
  FROM Employees e2 
  WHERE e2.ID = e1.ManagerID
);

For each e1 row, it looks up the manager’s salary in e2.
Powerful — but slow on large datasets.

Pro Tip: For performance, prefer JOINs over correlated subqueries when possible.

4. Subqueries in FROM (Derived Tables)

Create a temporary table on the fly.

Average daily sales above the overall average:

SELECT OrderDate, AVG(Total) AS AvgHighValueDay
FROM (
  SELECT OrderDate, Total 
  FROM Sales 
  WHERE Total > (SELECT AVG(Total) FROM Sales)
) AS HighValueSales
GROUP BY OrderDate;

The inner query builds a filtered dataset → the outer one analyzes it.

Note: Always alias the subquery (AS HighValueSales) — required in most databases.

5. Subqueries in UPDATE

Dynamically set values.

Set all barista salaries to the company average:

UPDATE Employees
SET Salary = (SELECT AVG(Salary) FROM Employees);

Warning: Subquery must return one value, or the update fails.

Subqueries vs. Joins: When to Use What?

Rule of thumb:

• Small data or simple logic? → Subquery
• Big data or repeated use? → Join or CTE

Limitations (Don’t Get Burned)

1. Single-row subqueries must return 1 value → or error
2. Correlated = slow → runs N times for N rows
3. Not all databases support all types → test in your environment
4. Harder to debug → wrap in CTEs for clarity

Final Thoughts

Subqueries aren’t just syntax — they’re a way of thinking.

They let you:

• Ask dynamic questions
• Write self-contained logic
• Solve problems elegantly

Next time you’re staring at a dataset asking “Who spent more than average?” or “Which employees outperform their boss?” — reach for a subquery.

It’s not magic.
It’s just SQL doing what it does best.

Your turn: Open your database. Try one subquery today.
What will you discover?