Interview practice

SQL Subquery Interview Questions

Practice scalar, IN, EXISTS, NOT EXISTS, and correlated subquery prompts.

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 shape the inner query is expected to return. A scalar subquery is used where one scalar value is expected; in DuckDB, zero rows becomes NULL and more than one row errors by default. An IN subquery returns one column of candidate values. EXISTS asks whether at least one matching row exists for the outer row.

Common subquery interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

Find products priced above the average product price.
Your answer Try it in the playground →
Reveal a model answer
```
select product_name, price
from products
where price > (select avg(price) from products)
order by price desc, product_name
```
A scalar subquery returns the one value to compare against. It must return one row and one column, which an aggregate guarantees.
Return customers who have at least one shipped order.
Your answer Try it in the playground →
Reveal a model answer
```
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'
)
order by c.customer_id
```
EXISTS asks yes or no per customer, so a customer with three shipped orders still appears once. A join would need DISTINCT to get the same result.
Find customers who have never placed a cancelled order.
Your answer Try it in the playground →
Reveal a model answer
```
select c.customer_id, c.first_name, c.last_name
from customers as c
where not exists (
  select 1
  from orders as o
  where o.customer_id = c.customer_id
    and o.status = 'cancelled'
)
order by c.customer_id
```
Clarify first: this includes customers who never ordered at all, since they have never cancelled either. If the interviewer means active buyers, add an EXISTS for any order.
Return suppliers whose products are below reorder level.
Your answer Try it in the playground →
Reveal a model answer
```
select s.supplier_name
from suppliers as s
where s.supplier_id in (
  select p.supplier_id
  from products as p
  where p.quantity_on_hand < p.reorder_level
)
order by s.supplier_name
```
The subquery builds the set of suppliers with at least one low-stock product. Ask whether 'products are below' means any product or all of them; all would need NOT EXISTS for a product that is not low.
Explain when NOT EXISTS is safer than NOT IN.
Your answer

Reveal a model answer

Whenever the subquery can return a NULL. One NULL makes every NOT IN comparison unknown, so the query returns nothing, with no error. NOT EXISTS only asks whether a matching row exists, so NULLs cannot poison it.

Scalar subquery pattern

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. A multi-column result cannot be used as that scalar, and multiple rows are an error by default in DuckDB.

Existence checks are different. EXISTS is often clearer when the question is "does a related row exist?" For missing-match checks, NOT EXISTS also avoids the three-valued-logic trap that a nullable NOT IN subquery can introduce.

EXISTS pattern

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.