SQL Subqueries and EXISTS: Answer Questions in Two Steps

Some questions contain another question inside them. “Which orders are above the average amount?” first needs an average. SQL subqueries and EXISTS let you express those connected questions clearly without manually copying a result from one query into another.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Solve the inner question first

Find the average order amount on its own:

SELECT AVG(amount) AS average_amount
FROM orders;

The result is 1025.0. Now ask which orders have an amount above that figure. You could type 1025 into a filter, but that copied number would become stale when orders changed. A subquery calculates it as part of the statement.

SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY order_id;

Orders 101 at 1200 and 104 at 1500 qualify. The parenthesised query returns a single value for the comparison. This is often called a scalar subquery. It is useful when the inner question naturally has one answer, such as a grand average or maximum.

Keep the scope of the inner calculation clear

The inner average above covers all orders. The outer query's later conditions do not automatically change that scope. If you need an average for a specific period or customer, write those conditions inside the inner query too, or use a different design that makes the grouping explicit.

For an ordinary scalar comparison, make sure the inner query is intended to return one value. Other databases may reject a subquery returning multiple rows in that position. SQLite can take the first row, which can conceal a mistake rather than reveal it. An aggregate without GROUP BY is a clear way to get one summary value here.

Ask whether a matching row exists

Now find customers who have at least one order worth 1000 or more:

SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.amount >= 1000
)
ORDER BY c.customer_id;

The result is Amina and Chao. EXISTS checks whether the inner query has any qualifying row. SELECT 1 is a conventional way to make it clear that the particular output values are unimportant; existence is what matters.

The reference to c.customer_id connects the inner question to the current outer customer. This is a correlated subquery. If you remove that relationship, the inner question becomes “does anyone have a large order?” Since the answer is yes, every customer could pass.

Choose EXISTS when you need one customer row

A join can produce one row per matching order. EXISTS lets you ask a yes-or-no question while keeping the outer result at one row per customer. Amina would appear once even if she had ten qualifying orders.

Do not assume EXISTS is always faster than a join. Performance depends on the database, indexes, data volume and query plan. For this beginner lesson, choose the expression that matches the question clearly, then measure performance when you have a real workload.

Find the opposite with NOT EXISTS

NOT EXISTS finds customers for whom no matching qualifying order exists. This is often easier to reason about than NOT IN when the inner values might include NULL. NULL inside a NOT IN list can lead to surprising unknown comparisons.

In this sample, every customer has some order. But Brian has no order worth at least 1000. “No orders” and “no orders meeting this threshold” are different questions. Keep the amount condition visible when explaining your result.

Try it yourself

Write a NOT EXISTS query that lists customers with no order worth at least 1000. Show customer_id and name. Then change the inner amount condition to at least 500 and predict whether anyone remains.

SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.amount >= 1000
)
ORDER BY c.customer_id;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.