How to Write Subqueries in SQL

Use subqueries when one query needs the result of another query to filter, compare, calculate, or build derived datasets. This is one of the cleanest ways to express layered SQL logic.

What you’ll build or solve

You’ll learn how to write subqueries in SQL inside WHERE, FROM, and SELECT. You’ll also know when joins may be clearer.

When this approach works best

This approach is the right choice when one step depends on the output of another step.

Common real-world scenarios include:

• Users above average spend
• Orders from top customers
• Latest record per entity
• Derived reporting tables
• Existence checks

This is a bad idea when the logic becomes hard to read and a JOIN or CTE would communicate intent better.

Prerequisites

You only need:

• Basic SELECT, WHERE, and aggregate knowledge
• Comfort reading nested query logic

Step-by-step instructions

Step 1: Place one query inside another

A common use is comparing against an aggregate.

SELECT *
FROM orders
WHERE total > (
  SELECT AVG(total)
  FROM orders
);

The inner query runs first.

Its result becomes the comparison value for the outer query.

Subqueries also work in IN.

SELECT *
FROM users
WHERE user_id IN (
  SELECT user_id
  FROM orders
  WHERE status = 'paid'
);

What to look for:

• Inner query runs first
• Great for comparisons and filtering
• Can return one value or many rows
• Useful in WHERE, FROM, and SELECT
• Joins may read better for reusable logic

Examples you can copy

Above-average price

SELECT *
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

Users with paid orders

SELECT *
FROM users
WHERE user_id IN (
  SELECT user_id
  FROM orders
  WHERE status = 'paid'
);

Latest login

SELECT *
FROM logins
WHERE created_at = (
  SELECT MAX(created_at)
  FROM logins
);

Common mistakes and how to fix them

Mistake 1: Returning multiple rows for scalar comparison

What the reader might do:

WHERE total > (
  SELECT total
  FROM orders
)

Why it breaks: the subquery returns many rows.

Corrected approach:

Use aggregates or IN.

Mistake 2: Using subqueries where joins are clearer

What the reader might do:

Nest multiple repeated subqueries.

Why it breaks: the query becomes harder to maintain.

Corrected approach:

Switch to joins or CTEs.

Mistake 3: Ignoring performance on correlated logic

What the reader might do:

Use row-by-row correlated subqueries on huge tables.

Why it breaks: this can be expensive.

Corrected approach:

Consider joins or indexed CTEs.

Troubleshooting

If SQL says “more than one row returned,” switch to IN or aggregate.

If the logic feels repetitive, rewrite as a join.

If performance is slow, inspect correlated subqueries.

If readability matters, use CTEs for named steps.

Quick recap

• Subqueries nest one query inside another
• Inner query runs first
• Great for comparisons and filters
• Use IN for multi-row results
• Prefer joins or CTEs for complex reusable logic