Lesson 13 of 20

In progress

Course progress

SQL

Subqueries and CTEs

Use a query inside another query, prefer EXISTS to IN for large sets, and name the steps of a complex query with a CTE.

A subquery is a query used as a value, a list or a table. It lets you build an answer in steps without leaving SQL, and it is often clearer than a join with a filter bolted on.

What you will learn

  • Use scalar, list and table subqueries.
  • Choose between IN and EXISTS.
  • Name intermediate steps with a CTE.

The data

SQL
Editable. Runs in your browser.





Press Run to see the output.

A scalar subquery

A scalar subquery returns one value and can be used anywhere a value is allowed.

SQL
Editable. Runs in your browser.





Press Run to see the output.

The inner query computes 1,625,000. The outer query compares each salary with it. The subquery runs once, not once per row, because it does not refer to the outer query.

Scalar subqueries are handy in the SELECT list too:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Here the subquery is correlated: it references t.id, so the engine evaluates it per team. That is fine for a handful of teams and expensive for a large table, where a join and GROUP BY is better.

A list subquery

SQL
Editable. Runs in your browser.





Press Run to see the output.

IN compares each row with the list the subquery returns. It reads well and is a natural translation of the question.

NOT IN is where NULLs bite: if the subquery can return a NULL, every comparison becomes unknown and the outer query returns nothing. A NOT NULL column or a WHERE ... IS NOT NULL in the subquery prevents that.

EXISTS

SQL
Editable. Runs in your browser.





Press Run to see the output.

EXISTS is true as soon as one matching row is found. It stops early, so it is usually faster than IN on a large set, and it is immune to the NULL problem because it never compares values.

NOT EXISTS is the reliable way to write "has none":

SQL
Editable. Runs in your browser.





Press Run to see the output.

That returns no teams here, since every team has at least one member, and it stays correct when NULLs appear in the correlated columns.

Table subqueries

A subquery in FROM behaves like a table and must have an alias.

SQL
Editable. Runs in your browser.





Press Run to see the output.

This is how you filter on an aggregate without HAVING, and it is a clean way to pre-aggregate before a join so the join does not multiply rows.

CTEs name the steps

WITH puts the subquery before the main query and gives it a name, which reads far better once there are two or three steps.

SQL
Editable. Runs in your browser.





Press Run to see the output.

Read top to bottom: compute per-team totals, then join them to teams and subtract. The same query as nested subqueries is a wall of parentheses.

A CTE can be referenced more than once in the main query, which is impossible for a plain subquery in FROM. That makes it the tool for a summary used in two places.

Two caveats: a CTE is not automatically faster, and in some databases it is materialised. Write it for clarity first, then check the plan if the query is slow.

Choosing between the forms

  • One value compared once: scalar subquery.
  • A small, static list: IN.
  • "At least one" or "none at all" on a large table: EXISTS / NOT EXISTS.
  • Several steps or reuse: CTE.
  • A filtering relationship you also need columns from: a join.

Name each CTE after the thing it produces — per_team, monthly_revenue — rather than temp or q1. The name is read twice, once where it is defined and once where it is used, and a good one makes the second reading unnecessary work.

Using NOT IN against a subquery that can return NULL. The result is empty and the query looks correct. Use NOT EXISTS instead.

Find teams whose payroll is above 3,000,000 paise, using a CTE, and list their name, people and payroll.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

Platform is the only team above the threshold, with 4,000,000 across two people. The CTE aggregates once, and the filter applies to the aggregated total rather than to individual salaries.

Recap

  • Subqueries can produce a value, a list or a table; correlated ones run per outer row.
  • EXISTS stops at the first match and avoids the NOT IN NULL trap.
  • CTEs name each step and can be reused; they do not guarantee speed.

Next, you will change data with INSERT, UPDATE and DELETE, carefully.

Are CTEs slower than subqueries?
Sometimes, because some engines materialise them. Measure if it matters; clarity is the default reason to use one.
Can a subquery return several columns?
Only as a table subquery in `FROM` or with `EXISTS`. A scalar context requires exactly one column and one row.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.