Subjects and lessons to explore.

Law

Databases & SQL · Foundations

Subqueries

8 min read 5 practice questions
7 key termsSourcesReport an error
Want it in plain words first? Jump to Eli explains — the same idea, no jargon.
On this page 9 sections
  1. In 30 seconds
  2. Why this matters
  3. The college version
  4. Eli explains
  5. Worked example
  6. Key takeaway
  7. Quick check
  8. Study tools
  9. Sources & references

In 30 seconds

A is a SELECT statement enclosed in inside another query — the working definition from SQLite's documentation. Microsoft Learn calls the the and the statement containing it the . The inner query runs first and hands its result to the outer query, which uses it as a value or a list. The most common placement is inside a , where the condition is built from another query's result — such as products priced above the average price.

Why this matters

Some questions cannot be answered in one step. “Which products cost more than average?” first needs the average, then a comparison against it. A subquery lets you write both steps in a single statement, so the database does the whole job in one run. That matters because real questions are rarely flat: you compare against a value, check membership in a list, or ask whether anything exists. Subqueries also reuse everything you already know from SELECT and WHERE, and learning to spot when a question needs two steps carries into any data tool, not just SQL.

The college version

A query inside a query

SQL's own documentation states the idea directly. SQLite's language reference states it in one sentence: a SELECT statement enclosed in parentheses is a subquery. Microsoft Learn adds the shape around it: a subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery, and it names the two parts — the nested query is the inner query, and the statement containing it is the outer query. Two ideas do all the work here. First, a subquery is still just a SELECT: everything you know about reading tables applies. Second, the parentheses are what make it a subquery; PostgreSQL's documentation notes that subqueries used as derived tables must be enclosed in parentheses. Take the parentheses away and the database no longer knows where the inner question ends and the outer question begins.

Why use one: answering questions in steps

Cedar & Vine Books keeps a books table with columns for title and price. The owner wants to know which books cost more than the store's average price — a question that has no single-step answer, because the average has to exist before anything can be compared with it. Run the steps by hand and it looks like this: first SELECT AVG(price) FROM books; returns 14.30, then SELECT title, price FROM books WHERE price > 14.30; returns the books above it. A subquery fuses the two steps into one statement: SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);. The database computes the average, keeps it for itself, and compares every price against it in the same run. That is the whole reason subqueries exist: they let one statement answer a question that needs a preliminary result.

The basic shape: an inner query feeding an outer query

Look at the fused statement again and the shape is plain. The outer query is the familiar SELECT title, price FROM books WHERE price > ... — a normal read with a gap in the condition. The inner query is the SELECT AVG(price) FROM books written inside parentheses, and it fills that gap. The database evaluates the inner query first, obtains a value (14.30 in this case) or a list of values, and then runs the outer query with that result in hand. SQLite's documentation describes the behavior directly: an uncorrelated subquery is evaluated only once and the result reused as necessary. Nothing is stored and nothing is repeated — the inner query runs, its answer feeds the outer query, and the statement is finished.

Subqueries with WHERE: the most common use

The most common place to find a subquery is inside a WHERE clause, and the mechanism is simple: the condition is built from another query's result. Instead of comparing a column against a number you typed, you compare it against whatever the inner query returns. W3Schools shows the pattern with IN: you can use IN with a subquery in the WHERE clause, returning all records from the main query that are present in the result of the subquery. At Cedar & Vine, the owner wants the names of customers who have placed at least one order: SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);. The inner query produces the list of customer ids that appear in orders, and the outer query keeps only the customers whose id is on that list. Two more WHERE-companions appear in the same family: EXISTS, which W3Schools describes as checking whether a subquery returns any rows, and which PostgreSQL says is evaluated only to determine whether at least one row comes back. One detail matters for IN: PostgreSQL documents that its right-hand side is a parenthesized subquery that must return exactly one column — the shape of the inner result has to match what the condition compares.

Subqueries versus joins: two ways to combine data

A combines tables side by side; a subquery nests one query inside another. That one-line contrast is worth keeping sharp, because the two tools often answer the same question. Microsoft Learn says it plainly: many statements that include subqueries can alternatively be formulated as joins. Even PostgreSQL's own examples blur the line — its EXISTS example, the documentation notes, is like an inner join on the column in question. Joins are a full topic of their own, so this lesson does not teach them; the point here is only the boundary. When you see two tables in a question, both routes exist, and choosing between them is about which one reads clearly.

Readability, and the honest framing

Nesting hides logic. A subquery inside a condition is easy to follow; a subquery inside a subquery starts to read like a riddle, and each level adds a place for a mistake to hide. The honest guidance is to keep subqueries short and shallow, and to remember the tool's purpose: subqueries exist to answer two-step questions, not to make queries look clever. Microsoft Learn balances the picture in both directions — many statements with subqueries can be rewritten as joins, yet other questions can be posed only with subqueries. So the framing is simple: a subquery is a tool, not a goal. When a join states the question more clearly, use the join; when the subquery does, use the subquery. Readability decides.

Eli, the EliExplains learning guide

Eli explains

The same idea, in plain words

Explain it like I’m 10

A subquery is a query tucked inside another query, in parentheses. The inner one runs first and hands its answer to the outer one. Ask “which books cost more than the average price?” and you can write the whole thing as one statement: the inner query works out the average, and the outer query keeps the books above it. The most common spot for a subquery is in the WHERE part of a statement, where the condition compares against the inner query's result — like checking which customers are in the list of people who have placed an order.

Picture it like this

Think of planning a party and needing to know which of your friends live within ten minutes of the venue. First you find the addresses within ten minutes — that is a list. Then you check each friend against that list. A subquery is the first step, done inside the same note as the second: “the friends whose homes are in the nearby-addresses list.” The database runs the inner step first, then uses its answer for the outer step.

Where the picture stops working

The analogy makes the steps sound like separate pieces of paper. In SQL the two steps live in one statement, and the database keeps the inner result to itself — you never see it unless you run the inner query on its own. And the result is not a saved list: it is computed fresh each time the statement runs, so it always matches the table's current contents.

Worked example

Cedar & Vine Books stores its titles in a books table with columns for title and price. The owner wants a shelf report: every book priced above the store's average price. She first asks for the average — SELECT AVG(price) FROM books; — and the database returns 14.30. That value becomes the comparison in the second step: SELECT title, price FROM books WHERE price > 14.30;. Rather than keep two queries in sync by hand, she writes one statement with the average query nested inside the comparison: SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);. The database runs the inner query first, works out 14.30, and returns the two books above it: Salt Roads at $16.50 and The Cartographer's Daughter at $19.25. If a new book changes the average, the same statement stays correct — the inner query recomputes it every time.

Key takeaway

A subquery is a SELECT statement nested in parentheses inside another query: the inner query runs first and feeds its result to the outer one, most often as the values a WHERE condition compares against — a tool for two-step questions, not a substitute for choosing the wording that reads clearest.

Quick check

3 questions here, of 5 in this lesson’s practice set. Answers stay hidden until you check.

Question 1 of 3foundational

What is a subquery, according to SQLite's documentation?

Choose an answer, then check it.
Question 2 of 3intermediate

Cedar & Vine Books runs SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);. What does the part in parentheses do?

Choose an answer, then check it.
Question 3 of 3advanced

A nested statement returns the same answer as two separate queries run by hand. Which statement best describes the advantage of the subquery version?

Choose an answer, then check it.
Practice all 5

Keep learning

Ready to build on this? Continue to the next lesson.

Practice this lesson
Study tools & related lessonsYou’ll learn to · Common mistakes · Easily confused · Key vocabulary · Related

You’ll learn to

  • Define a subquery as a SELECT statement enclosed in parentheses inside another query, using the working definitions of SQLite and Microsoft Learn.
  • Explain why subqueries exist: some questions are answered in steps, and a subquery packs both steps into one statement.
  • Identify the inner query and the outer query in a nested statement, and describe how the inner result feeds the outer query.
  • Recognize the most common placement of a subquery: inside a WHERE clause, where it supplies the values a condition compares against.
  • Contrast subqueries with joins as two ways to combine data, and state when joins often do the same job more clearly.

Common mistakes

  • Forgetting the parentheses around the inner query.

    The parentheses mark where the subquery begins and ends. PostgreSQL's documentation notes that subqueries must be enclosed in parentheses, and without them the database cannot tell the inner question from the outer one.

  • Expecting the outer query to run first.

    The inner query runs first and produces the value or list; the outer query then uses that result. SQLite's documentation says an uncorrelated subquery is evaluated once and its result reused — the nesting is the point.

  • Writing an inner query that returns more than the condition can use.

    The comparison has to match the inner result's shape. PostgreSQL documents that the right-hand side of IN is a parenthesized subquery that must return exactly one column; a multi-column result needs a different form.

  • Stacking several subqueries and expecting the statement to stay readable.

    Each level of nesting hides more logic. Keep subqueries short, and remember Microsoft Learn's note that many statements with subqueries can alternatively be formulated as joins — when a join reads more clearly, use it.

Easily confused

A subquery vs. A join

A subquery nests one query inside another and uses the inner result as a value or a list; a join combines tables side by side on a matching column. For many questions either route gives the same answer, and joins have their own lesson.

One combined statement vs. Two queries run by hand

A subquery answers a two-step question in one statement, so the database always compares against the current value; running two queries by hand risks typing a stale result into the second one.

The inner query's result vs. A stored table

A subquery's result is computed fresh each time the statement runs and is never saved; a stored table lives in the database and stays there between queries.

Key vocabulary

subquery
A SELECT statement written in parentheses inside another query; the inner query runs first and its result is used by the outer query.
inner query
The nested SELECT that sits inside the outer statement; Microsoft Learn also calls it the inner select.
outer query
The statement that contains a subquery; it receives the inner query's result and finishes the question.
parentheses
The round brackets that mark where a subquery begins and ends inside a statement.
nested query
Another name for a subquery, describing how one query sits inside another query.
WHERE clause
The part of a statement that keeps only the rows meeting a condition; a subquery often supplies the values that condition compares against.
join
A way to combine two tables side by side using a matching column; an alternative to a subquery for many questions, covered in its own lesson.

Sources & references

  1. SQLite Query Language: Expressions (Section 11, Subquery Expressions) — SQLite Consortium
  2. Microsoft Learn: Subqueries (SQL Server) — Microsoft Learn
  3. SQL IN Operator — W3Schools
  4. SQL EXISTS Operator — W3Schools
  5. PostgreSQL Documentation: 9.24. Subquery Expressions — The PostgreSQL Global Development Group
  6. PostgreSQL Documentation: Table Expressions (Section 7.2, including 7.2.2 The WHERE Clause) — PostgreSQL

EliExplains lessons are original prose written from the open, credible references above. See Copyright & Licensing.

Researched 2026-08-21

Educational content only. It is not medical, legal or professional advice. Found an error? Tell us.