CodeRang
SQL topics

SQL subquery with EXISTS and correlated queries

A SQL subquery places one query inside another so the outer query can use its result or test a condition against it. This example uses correlated EXISTS and NOT EXISTS checks to determine whether related rows are present.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • EXISTS and NOT EXISTS
  • IN subqueries
  • Correlated subqueries
  • Scalar subqueries
  • Derived tables in FROM

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Customers with paid orders and no refunds

Find customers who have at least one paid order and no refunded orders. Return customer_name and city, and sort the result by customer name.

Query 1PostgreSQLv16
SELECT c.customer_name, c.city
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid'
)
AND NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded'
)
ORDER BY c.customer_name;
Submit
Resultcustomersorders
Answer accepted

How a SQL subquery works with EXISTS

The outer query reads customers one row at a time. The first EXISTS asks whether a paid order exists for the current customer. The condition o.customer_id = c.id connects the inner query to that outer customer row. Because the subquery depends on c.id, it is a correlated subquery rather than an independent query that can be interpreted without the outer row.

SELECT 1 is conventional inside EXISTS because the selected value itself does not matter. EXISTS only cares whether the subquery can produce at least one row. The paid-status condition narrows those rows to paid orders, so customers with no paid order fail the first test.

The second subquery uses NOT EXISTS to express the opposite requirement: there must be no refunded order for the same customer. Bruno fails because one refunded order exists even though he also has a paid order. Daniel fails the positive EXISTS test because his only order is refunded. Alice, Clara, Eva, and Felix satisfy both conditions. This is a natural use of correlated subqueries because each condition directly describes the existence or absence of related rows.

SQLPostgreSQL v16
SELECT c.customer_name, c.city
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid'
)
AND NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded'
)
ORDER BY c.customer_name;
Query result
customer_namecity
AliceBerlin
ClaraPrague
EvaTallinn
FelixVienna

Common SQL subquery mistakes

01

Forgetting the correlation condition

If o.customer_id = c.id is removed, the subquery checks whether any paid order exists anywhere in the table. Once one is found, EXISTS becomes true for every customer, which changes the meaning of the query completely.

02

Using NOT IN without considering NULL

NOT IN can behave unexpectedly when the subquery returns a NULL. SQL's three-valued logic can turn the comparison into UNKNOWN and remove rows you expected to keep. NOT EXISTS is often clearer for anti-join style conditions.

03

Using a multi-row result as a scalar value

A scalar subquery used with operators such as =, >, or < must return at most one row. If it returns several, PostgreSQL raises an error. Use an aggregate, IN, EXISTS, or rewrite the query when multiple values are legitimate.

Practice problems on this topic

SQL from scratch course

Good follow-up SQL subquery practice includes scalar comparisons, IN lists produced by another query, derived tables in FROM, and correlated conditions based on each outer row. SQL Arena includes exercises around maximum values, each user's most expensive purchase, and finding entities with no related records. If you want the concepts in sequence rather than as isolated exercises, continue with the “SQL from Scratch” course, where each lesson combines explanation with a task and the first chapters are free. The trainer has 1,192 tasks, with separate PostgreSQL, MySQL, ClickHouse, and BigQuery sandboxes in the browser. Solutions are checked on hidden data as well as the visible example. The free plan requires no card and includes 292 tasks on all four dialects.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is a SQL subquery?

A SQL subquery is a SELECT statement nested inside another SQL statement. Depending on its position and result shape, it can provide a scalar value, a set of values, a derived table, or a condition for the outer query.

How does a SQL subquery work?

An uncorrelated subquery can be evaluated independently of the outer query. A correlated subquery refers to values from the current outer row and is logically evaluated in relation to that row.

What is a correlated SQL subquery?

A correlated subquery references a column from the outer query. That reference connects the inner query to the row currently being considered by the outer query.

How do you use EXISTS in a SQL subquery?

EXISTS checks whether the subquery can return at least one row. The selected value inside the EXISTS subquery is not important; the condition depends only on whether a matching row exists.

What is the difference between IN and EXISTS in a SQL subquery?

IN compares a value against a returned set of values, while EXISTS checks whether a matching row exists. They can express similar logic in some cases, but NULL handling and correlated conditions can make their behavior and query structure different.

See also

Practice SQL subqueries with exercises

Continue with EXISTS, NOT EXISTS, IN, scalar subqueries, and correlated subquery exercises in SQL Arena. For a structured introduction that builds up to nested queries, use the “SQL from Scratch” course.

Practice SQL subqueries

More practice from CodeRang