SovranCode
SQL: Query, Model, and Analyze Data Subqueries and correlated subqueries
This device
Course contentsSubqueries and correlated subqueries · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONSelf joins and many-to-many data
NEXT LESSONUNION, INTERSECT, and EXCEPT
Combining related data · Lesson 15 145 min

Subqueries and correlated subqueries

A subquery is a query inside another query. It lets SQL answer a small question first—such as “what is the average?” or “does this customer have an order?”—and then use that answer in the main query.

The simple mental model: get an answer, then use it

Think of the inner query as a small answer slip. It runs to produce one value, a list of values, or a yes/no result. The outer query reads that slip and decides what to show. Read nested SQL from the deepest parentheses outward.

First, identify the answer shape

You do not need to memorise every kind of subquery. Start with one question: what does the inner query return? The answer shape tells you where it can safely go.

  • One value, such as an average or count, fits beside a value with =, >, or in the SELECT list.
  • A list of values, such as customer IDs, fits with IN.
  • Yes or no fits with EXISTS or NOT EXISTS.
  • A small temporary table fits in FROM and needs an alias.
Before you run any query

Cover the outer query and predict the inner result first. Then ask: “How will the outer query use that result?” This one habit makes subqueries much easier to debug.

A scalar subquery returns one value

A scalar subquery returns exactly one cell. In the example below, the inner query calculates the average paid order: (3500 + 1800 + 6000) / 3. The outer query places that same benchmark beside every customer name.

The average is not copied into the query by hand. SQL keeps the calculation and the report together, so they always use the same data.

SQL BROWSER RUNNER

Show a shared paid-order benchmark

Use one scalar subquery to calculate the average paid amount.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer_name,
  (SELECT AVG(amount_cents) FROM orders WHERE status = 'paid') AS average_paid_cents
FROM customers
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change AVG to MAX. Which number should every customer see?

Common error: a scalar subquery returns more than one row

amount_cents = (SELECT amount_cents FROM orders) fails if there are several orders because SQL cannot choose one amount for you. Add an aggregate such as MAX, narrow the query to one row, or use IN when many values are valid.

Use a subquery as a filter value

The inner query finds the average paid order. The outer query keeps only paid orders higher than that average. This pattern works whenever the rule depends on a value calculated from the same data: above average, latest date, highest score, or a customer-specific limit.

Read it in two steps: “Find the average paid amount.” Then: “Return paid orders greater than that amount.”

SQL BROWSER RUNNER

Find paid orders above average

Compare each paid order with a value calculated by a nested query.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT order_id, customer_id, amount_cents
FROM orders
WHERE status = 'paid'
  AND amount_cents > (SELECT AVG(amount_cents) FROM orders WHERE status = 'paid')
ORDER BY amount_cents DESC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Predict the average of 3,500, 1,800, and 6,000 before running it.

Use IN for a list; use EXISTS for a relationship

IN asks whether one value appears in a returned list. SQL first makes a list of customer IDs that have a paid order, then keeps customers whose ID is in that list. It is a readable choice when the inner query naturally returns one column.

SQL BROWSER RUNNER

Filter customers with IN

Use a returned set of customer IDs to filter the outer table.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer_name, city
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'paid')
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change paid to pending. Which customer should remain?

EXISTS does not need a list. It checks whether at least one matching row exists for the current outer row. The inner query can use SELECT 1 because SQL only needs a yes/no answer—not a specific column value.

SQL BROWSER RUNNER

Filter customers with EXISTS

Check whether a related paid order exists for each outer customer.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer_name
FROM customers AS customer
WHERE EXISTS (
  SELECT 1 FROM orders AS order_row
  WHERE order_row.customer_id = customer.customer_id AND order_row.status = 'paid'
)
ORDER BY customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why does the subquery use SELECT 1 rather than SELECT order_id?

A correlated subquery runs in the context of the outer row

A correlated subquery refers to a column from the outer query. order_row.customer_id = customer.customer_id is the connection: for each customer being examined, the inner query counts only that customer's paid orders.

Imagine SQL reading the customer list one row at a time. For Amina, it asks “how many paid orders belong to customer 1?” For Bilal, it asks the same question with customer 2. This is expressive, even though a grouped join or CTE may be easier to read for a larger report.

SQL BROWSER RUNNER

Count paid orders for each customer

Correlate the inner order query to the current customer row.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer.customer_name,
  (SELECT COUNT(*) FROM orders AS order_row WHERE order_row.customer_id = customer.customer_id AND order_row.status = 'paid') AS paid_order_count
FROM customers AS customer
ORDER BY customer.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Explain why Celia has zero even though she has a pending order.

NOT EXISTS is the direct way to ask for missing relationships. It finds customers for whom the inner query cannot find even one order.

SQL BROWSER RUNNER

Find customers with no orders

Use NOT EXISTS to express an anti-relationship safely.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer.customer_name
FROM customers AS customer
WHERE NOT EXISTS (SELECT 1 FROM orders AS order_row WHERE order_row.customer_id = customer.customer_id)
ORDER BY customer.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is Dina the only result? Add an order for Dina and rerun it.

A subquery in FROM creates a temporary table

A subquery in FROM is often called a derived table. First it makes a small result with one row per paying customer and that customer's total. Then the outer query joins that temporary result to customers to show readable names.

The alias totals is required because the outer query needs a name for the temporary table. Give derived tables names that describe what one row represents—here, one row is one customer's paid total.

SQL BROWSER RUNNER

Join to per-customer totals

Aggregate in a derived table, then join readable customer names.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
SELECT customer.customer_name, totals.paid_cents
FROM customers AS customer
JOIN (
  SELECT customer_id, SUM(amount_cents) AS paid_cents
  FROM orders WHERE status = 'paid' GROUP BY customer_id
) AS totals ON totals.customer_id = customer.customer_id
ORDER BY totals.paid_cents DESC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Compare its result grain with the earlier order-level query.

Choose the clearest tool

  • Use a scalar subquery for one calculated value, such as an average or maximum.
  • Use IN when you have a simple list of one-column values.
  • Use EXISTS or NOT EXISTS when the question is “does a related row exist?”
  • Use a join when you need columns from both tables in the result.
  • Use a CTE when a calculation has several named steps or you want to reuse an intermediate result.
Clarity matters more than clever nesting

Databases can often optimise a subquery and a join into similar plans. Start with the version a teammate can explain. Check the query plan only when performance becomes a real concern.

Independent lab: find above-average customers

Now combine the ideas. First make one total per paying customer. Next calculate the average of those totals. Finally keep only customers whose own paid total is higher than that average. Take it one layer at a time—the query is longer, but each layer answers a simple question.

SQL BROWSER RUNNER

Compare customer totals to their peer average

Combine correlated and derived subqueries in one report.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, amount_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Amina','Rabat'),(2,'Bilal','Casablanca'),(3,'Celia','Fes'),(4,'Dina','Rabat');
INSERT INTO orders VALUES (101,1,'paid',3500),(102,1,'paid',1800),(103,2,'paid',6000),(104,3,'pending',2200);
-- Keep customers whose paid total is above the average paid-customer total.
SELECT customer.customer_name,
  (SELECT SUM(order_row.amount_cents) FROM orders AS order_row WHERE order_row.customer_id = customer.customer_id AND order_row.status = 'paid') AS paid_cents
FROM customers AS customer
WHERE (SELECT SUM(order_row.amount_cents) FROM orders AS order_row WHERE order_row.customer_id = customer.customer_id AND order_row.status = 'paid') >
  (SELECT AVG(customer_total) FROM (SELECT SUM(amount_cents) AS customer_total FROM orders WHERE status = 'paid' GROUP BY customer_id))
ORDER BY paid_cents DESC;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Explain why a customer with no paid orders does not appear.

Common mistakes to avoid

  • Using = when the inner query returns many rows. Use IN, EXISTS, or reduce it to one value instead.
  • Forgetting the connection in a correlated subquery. Without it, every outer row gets the same answer.
  • Using NOT IN with a list that may contain NULL. Prefer NOT EXISTS for missing-relationship checks.
  • Building several deeply nested steps when a CTE would give each step a clear name.

Lesson review

  • I can identify whether an inner query returns one value, a list, a yes/no answer, or a temporary table.
  • I can read a subquery from the innermost parentheses outward.
  • I can explain how a correlated subquery receives the current outer row.
  • I can choose between IN, EXISTS, a join, and a CTE for clarity.
  • I can recognise the common error of using a many-row result where SQL expects one value.
KNOWLEDGE CHECK

Check your subquery reasoning

Answer all ten questions, then use the explanations to revisit the example that needs another pass.

01What is a subquery?
02What does a scalar subquery return?
03Which operator is usually appropriate when an inner query returns several customer IDs?
04Why can SELECT 1 appear inside EXISTS?
05What makes a subquery correlated?
06What happens if a scalar subquery returns several rows?
07What does NOT EXISTS express clearly?
08What is a derived table?
09When is a join often clearer than a subquery?
10Which reading order makes nested SQL easier to understand?
PREVIOUS LESSONSelf joins and many-to-many data
NEXT LESSONUNION, INTERSECT, and EXCEPT
ON THIS PAGEAnswer shapesScalar subqueriesFilter valuesIN and EXISTSCorrelated subqueriesDerived tablesChoosing a toolIndependent labCommon mistakesKnowledge check
Course contents