SQL Subquery Interview Questions

Subquery questions test whether you can use one query to answer another. They often appear when a filter depends on a calculated value, a set of matching keys, or the existence of related rows.

The first thing to say is what the inner query returns. A scalar subquery returns one value. An IN subquery returns one column of possible values. An EXISTS subquery answers yes or no for each outer row.

Common subquery interview prompts

1. Find products priced above the average product price.
2. Return customers who have at least one shipped order.
3. Find customers who have never placed a cancelled order.
4. Return suppliers whose products are below reorder level.
5. Explain when NOT EXISTS is safer than NOT IN.

select product_name,
       price
from products
where price > (
  select avg(price)
  from products
)
order by price desc

What matters

The average-price example needs a scalar subquery because the outer query compares each product to one calculated value. If the inner query returned several rows or several columns, the comparison would not make sense.

Existence checks are different. EXISTS is usually clearer when the question is "does a related row exist?" It also avoids the NULL trap that can make NOT IN return no rows.

select c.customer_id,
       c.first_name,
       c.last_name
from customers as c
where exists (
  select 1
  from orders as o
  where o.customer_id = c.customer_id
    and o.status = 'shipped'
)

How to talk it through

Name the inner query's job: "The outer query returns customers. For each customer, the EXISTS subquery looks for at least one matching shipped order. I do not need to select real columns inside EXISTS; the match is what matters."

If a join would also work, say that. Many subquery questions have a join equivalent. The interview signal is knowing which version reads closer to the business question and which one avoids duplicate rows.

Practice next

Work through scalar subqueries,IN subqueries,EXISTS and NOT EXISTS, andcorrelated subqueries. Keep thesubquery reference open, and review why NOT IN with NULL returns nothing.