Skip to content

Subqueries

Nest queries inside queries: scalar, correlated, IN, EXISTS, and CTEs.

What you'll learn

  • What a subquery is and the problems it solves
  • Scalar subqueries returning a single value
  • Subqueries with IN and NOT IN for set membership
  • Correlated subqueries that reference the outer query
  • Using EXISTS for efficient existence checks

Concept

What Is a Subquery?

A subquery is a SELECT statement nested inside another SQL statement. It lets you compute a value or a set of values on the fly, without a separate query or application code. Subqueries can appear in the SELECT list, the FROM clause (as a derived table), or the WHERE clause.

Scalar Subqueries

A scalar subquery returns exactly one row and one column. It can be used anywhere a single value is expected. For example, SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products) finds products above the average price. The inner query runs first, returns a single number, and the outer query compares each row against it.

IN and NOT IN

IN (subquery) checks if a value is in the set returned by the subquery. SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = TRUE) returns orders from active users only. Be cautious with NOT IN and NULLs — if the subquery returns any NULL, NOT IN returns no rows at all due to three-valued logic.

Correlated Subqueries

A correlated subquery references a column from the outer query, so it is re-evaluated for each row. This can be powerful but slow on large tables. For example, SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users gives each user's order count. Modern SQL prefers JOINs or CTEs for better performance and readability.

EXISTS

EXISTS (subquery) returns TRUE if the subquery returns at least one row. It is often more efficient than IN because the database can short-circuit as soon as it finds a match. SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id) finds users who have at least one order.

Example

              -- Scalar subquery: products above average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- IN subquery: orders from active users
SELECT id, total, created_at
FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE active = TRUE
);

-- Correlated subquery: order count per user
SELECT
  u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
ORDER BY order_count DESC;

-- EXISTS: users who have placed at least one order
SELECT u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);
            

Four subquery patterns: scalar for comparison, IN for set membership, correlated for per-row computation, and EXISTS for existence checks.

Try it

  • SQL Formatter

    Format nested subqueries with proper indentation.

  • JSON Path

    JSONPath is similar to subqueries — extract nested data.

Common mistakes

The mistake

Using NOT IN with a subquery that may return NULL

The fix

NOT IN returns no rows if the subquery contains any NULL. Use NOT EXISTS instead, which handles NULLs correctly.

The mistake

Writing a correlated subquery when a JOIN would suffice

The fix

Correlated subqueries re-run for each row. A LEFT JOIN with GROUP BY is often faster and more readable. Check the execution plan.

Practice Subqueries

5 exercises

Practice Subqueries

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors