Subqueries and CTEs – SQL Server | Lesson 19

Published / updated4 Oct 2026

Introduction

Some questions need an answer to another question first. "Which students scored above the class average?" needs the average before it can compare anything.

A subquery is a query inside another query. A CTE (Common Table Expression) is a named query you write first and use below, like a step in a recipe. Both let you build hard reports from simple pieces.

By the end of this lesson, you will be able to:

Use a subquery that returns one value, or a list of values.
Choose between IN and EXISTS.
Avoid the NOT IN trap with NULL, which silently returns nothing.
Write a correlated subquery that runs once per row.
Break a long report into named steps with one or more CTEs.

Why this matters

The fee office asks: "Which invoices still have money due, and how much?" The balance is the invoice total minus the payments. The payments must be added up per invoice first, then compared with each invoice.

You could write that as one tangled query. With a CTE, it becomes two readable steps: "total paid per invoice", then "invoices where total minus paid is above zero". Readable queries are queries that the next developer can fix at 2 AM.

The concept

Example: three shapes of subquery

Shape	Returns	Used with	SMS example
Scalar subquery	One value	`=`, `>`, `<`	Marks above the section average
List subquery	One column, many rows	`IN`, `NOT IN`	Guardians whose Id is in the users list
Existence subquery	True or false	`EXISTS`, `NOT EXISTS`	Students with at least one absence

A scalar subquery must return exactly one value. If it returns more, the query fails:

Example
SELECT FirstName
FROM dbo.Students
WHERE Id = (SELECT StudentId FROM dbo.StudentGuardians WHERE GuardianId = 1);

Output
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Rajesh (guardian 1) has two children, so = cannot compare with two values. IN can.

Example: IN and EXISTS

IN asks "is this value in that list?". EXISTS asks "does that query return at least one row?". For "students with at least one absence", EXISTS reads naturally, and SQL Server can stop searching at the first matching row.

Think of checking a guest list at a school function. IN reads the whole list of names and looks for yours. EXISTS only asks the guard, "Is there any entry for this family?" and stops at the first yes.

Example: the NOT IN trap

This is one of the most famous SQL traps. Of the 22 guardians, only Rajesh has a login, so 21 have none.

Let us count them with NOT IN. Note that Users.GuardianId is NULL for every user who is not a guardian:

Example
SELECT COUNT(*) AS GuardiansWithoutLogin_NotIn
FROM dbo.Guardians
WHERE Id NOT IN (SELECT GuardianId FROM dbo.Users);

The answer is 0. It should be 21.

Why? NOT IN means "not equal to every value in the list". The list contains NULL, and "is 2 not equal to NULL?" is unknown, never true.

One NULL makes every row unknown, so no row qualifies. NOT EXISTS does not have this problem:

Example
SELECT COUNT(*) AS GuardiansWithoutLogin_NotExists
FROM dbo.Guardians AS g
WHERE NOT EXISTS (SELECT 1 FROM dbo.Users AS u WHERE u.GuardianId = g.Id);

The answer is 21, the correct one. Rule: use NOT EXISTS, not NOT IN, with a subquery.

If you ever must keep a NOT IN, remove the NULLs inside the subquery with WHERE GuardianId IS NOT NULL. Then the list holds only real values and the answer is 21 again. NOT EXISTS is still the better habit, because it cannot be broken by a NULL that appears in the data next year.

Example: subquery, join, or CTE?

Many questions can be answered all three ways. Choose by readability first:

Tool	Best when	Watch out for
Subquery	A single value or a simple "exists" check	Deep nesting becomes hard to read
Join	You need columns from both tables in the result	Multiplied rows on one-to-many links
CTE	A report has several steps	Exists only for one statement

SQL Server often turns all three into the same plan, so speed is usually equal. When it is not, Module 5 shows how to read the plan and choose.

Example: a CTE

A CTE starts with WITH name AS ( query ). The query below it can then use the name like a table:

Example
WITH SectionTotals AS (
    SELECT SectionId, COUNT(*) AS Students
    FROM dbo.Enrollments
    WHERE AcademicYearId = 2
    GROUP BY SectionId
)
SELECT SectionId, Students
FROM SectionTotals
WHERE Students < 5;

A CTE exists only for the one statement that follows it. You can write several, separated by commas, and each can use the ones before it.

Apply it to the SMS

Save the full script below as queries\019-subqueries-ctes.sql.

Example: above the section average

10-A's Unit Test 1 Mathematics marks are 92, 85, 67, and 95, so the average is 84.75. The subquery calculates that; the outer query compares each mark with it:

Example
SELECT
    s.FirstName,
    m.MarksObtained
FROM dbo.Marks AS m
INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE m.ExamSubjectId = 8
  AND m.EnrollmentId BETWEEN 13 AND 16
  AND m.MarksObtained > (
        SELECT AVG(MarksObtained)
        FROM dbo.Marks
        WHERE ExamSubjectId = 8
          AND EnrollmentId BETWEEN 13 AND 16
      )
ORDER BY m.MarksObtained DESC;

FirstName	MarksObtained
Meera	95.00
Aarav	92.00
Diya	85.00

Kabir's 67 is below the average, so he is not listed.

Example: IN and EXISTS on SMS data

Guardians who have a login, with IN: one row, Rajesh Sharma.

Fixed students with at least one absence this year, with EXISTS: one row, Kabir Singh. The EXISTS subquery refers to s.Id from the outer query, so it is checked once for each student.

Example: a correlated subquery

A correlated subquery uses a column from the outer query, so it runs once per outer row. Each 10-A student's last recorded attendance date:

Example
SELECT
    s.FirstName,
    (
        SELECT MAX(a.AttendanceDate)
        FROM dbo.AttendanceRecords AS a
        WHERE a.EnrollmentId = e.Id
    ) AS LastRecorded
FROM dbo.Enrollments AS e
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE e.SectionId = 19
  AND e.AcademicYearId = 2
ORDER BY e.RollNumber;

All four show 2026-07-03. Correlated subqueries are easy to read, but on large tables they can be slow; a join with GROUP BY often does the same work in one pass.

Example: unpaid invoices with a CTE

Step 1, the CTE: total paid per invoice. Step 2: join it to the invoices and keep only those with a balance. LEFT JOIN keeps invoices with no payments, and ISNULL(x, 0) turns their NULL total into 0:

Example
WITH PaidPerInvoice AS (
    SELECT FeeInvoiceId, SUM(Amount) AS PaidAmount
    FROM dbo.FeePayments
    WHERE IsDeleted = 0
    GROUP BY FeeInvoiceId
)
SELECT
    i.InvoiceNumber,
    s.FirstName,
    i.TotalAmount,
    ISNULL(p.PaidAmount, 0) AS Paid,
    i.TotalAmount - i.DiscountAmount - ISNULL(p.PaidAmount, 0) AS Balance
FROM dbo.FeeInvoices AS i
INNER JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
LEFT JOIN PaidPerInvoice AS p ON p.FeeInvoiceId = i.Id
WHERE i.TotalAmount - i.DiscountAmount - ISNULL(p.PaidAmount, 0) > 0
ORDER BY i.InvoiceNumber;

InvoiceNumber	FirstName	TotalAmount	Paid	Balance
INV-2026-000002	Diya	45500.00	20000.00	25500.00
INV-2026-000003	Kabir	45500.00	0.00	45500.00
INV-2026-000004	Meera	45500.00	0.00	45500.00
INV-2026-000005	Rohan	45500.00	0.00	45500.00
INV-2026-000006	Sana	45500.00	0.00	45500.00
INV-2026-000008	Ananya	39500.00	0.00	39500.00
INV-2026-000009	Vihaan	39500.00	0.00	39500.00
INV-2026-000010	Zara	39500.00	0.00	39500.00
INV-2026-000011	Arnav	39500.00	0.00	39500.00
INV-2026-000012	Kiara	39500.00	0.00	39500.00

Ten invoices. Aarav's and Advika's are missing because they are fully paid. Grouping the payments before joining is also how you avoid the doubled totals from Lesson 17.

Example: CTEs in a chain

The Class 6 Unit Test 1 report with a result per paper uses two CTEs: SectionMarks gathers the marks with each paper's pass mark, and Results turns them into Pass, Fail, or Absent with CASE. The final query only adds names:

FirstName	Subject	MarksObtained	Result
Advika	Mathematics	89.00	Pass
Advika	Science	94.00	Pass
Ananya	Mathematics	76.00	Pass
Ananya	Science	69.00	Pass
Vihaan	Mathematics	64.00	Pass
Vihaan	Science	70.00	Pass
Zara	Mathematics	81.00	Pass
Zara	Science	77.00	Pass
Arnav	Mathematics	33.00	Fail
Arnav	Science	48.00	Pass
Kiara	Mathematics	90.00	Pass
Kiara	Science	86.00	Pass

Arnav's 33 is below the pass mark of 35. Each CTE does one job, so each can be tested alone by running its query on its own.

Show queries/019-subqueries-ctes.sql

queries/019-subqueries-ctes.sql
-- Lesson 19: subqueries and CTEs. Read-only: it changes nothing.
USE SchoolDb;
GO

-- 1. A subquery that returns one value: 10-A students above their section's
--    Unit Test 1 Mathematics average (enrollments 13-16 are 10-A, paper 8 is Maths).
SELECT
    s.FirstName,
    m.MarksObtained
FROM dbo.Marks AS m
INNER JOIN dbo.Enrollments AS e ON e.Id = m.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE m.ExamSubjectId = 8
  AND m.EnrollmentId BETWEEN 13 AND 16
  AND m.MarksObtained > (
        SELECT AVG(MarksObtained)
        FROM dbo.Marks
        WHERE ExamSubjectId = 8
          AND EnrollmentId BETWEEN 13 AND 16
      )
ORDER BY m.MarksObtained DESC;

-- 2. IN with a list from a subquery: guardians who have a login.
SELECT FirstName, LastName
FROM dbo.Guardians
WHERE Id IN (SELECT GuardianId FROM dbo.Users);

-- 3. EXISTS: the 12 students with at least one absence this year.
SELECT s.FirstName, s.LastName
FROM dbo.Students AS s
WHERE s.Id <= 12
  AND EXISTS (
        SELECT 1
        FROM dbo.Enrollments AS e
        INNER JOIN dbo.AttendanceRecords AS a ON a.EnrollmentId = e.Id
        WHERE e.StudentId = s.Id
          AND e.AcademicYearId = 2
          AND a.Status = 'Absent'
      );

-- 4. The NOT IN trap: Users.GuardianId contains NULLs, so NOT IN returns no rows at all.
SELECT COUNT(*) AS GuardiansWithoutLogin_NotIn
FROM dbo.Guardians
WHERE Id NOT IN (SELECT GuardianId FROM dbo.Users);

-- 5. NOT EXISTS gives the right answer.
SELECT COUNT(*) AS GuardiansWithoutLogin_NotExists
FROM dbo.Guardians AS g
WHERE NOT EXISTS (SELECT 1 FROM dbo.Users AS u WHERE u.GuardianId = g.Id);

-- 6. A correlated subquery: each 10-A student's last recorded attendance date.
SELECT
    s.FirstName,
    (
        SELECT MAX(a.AttendanceDate)
        FROM dbo.AttendanceRecords AS a
        WHERE a.EnrollmentId = e.Id
    ) AS LastRecorded
FROM dbo.Enrollments AS e
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
WHERE e.SectionId = 19
  AND e.AcademicYearId = 2
ORDER BY e.RollNumber;

-- 7. A CTE: unpaid and part-paid invoices with their balance.
WITH PaidPerInvoice AS (
    SELECT FeeInvoiceId, SUM(Amount) AS PaidAmount
    FROM dbo.FeePayments
    WHERE IsDeleted = 0
    GROUP BY FeeInvoiceId
)
SELECT
    i.InvoiceNumber,
    s.FirstName,
    i.TotalAmount,
    ISNULL(p.PaidAmount, 0) AS Paid,
    i.TotalAmount - i.DiscountAmount - ISNULL(p.PaidAmount, 0) AS Balance
FROM dbo.FeeInvoices AS i
INNER JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
LEFT JOIN PaidPerInvoice AS p ON p.FeeInvoiceId = i.Id
WHERE i.TotalAmount - i.DiscountAmount - ISNULL(p.PaidAmount, 0) > 0
ORDER BY i.InvoiceNumber;

-- 8. Two CTEs in a row: the Class 6 (6-A and 6-B) Unit Test 1 report with pass or fail per paper.
WITH SectionMarks AS (
    SELECT
        m.EnrollmentId,
        es.SubjectId,
        m.MarksObtained,
        m.IsAbsent,
        es.PassMarks
    FROM dbo.Marks AS m
    INNER JOIN dbo.ExamSubjects AS es ON es.Id = m.ExamSubjectId
    WHERE es.ExamId = 1
      AND m.EnrollmentId BETWEEN 19 AND 24
),
Results AS (
    SELECT
        EnrollmentId,
        SubjectId,
        MarksObtained,
        CASE
            WHEN IsAbsent = 1 THEN 'Absent'
            WHEN MarksObtained >= PassMarks THEN 'Pass'
            ELSE 'Fail'
        END AS Result
    FROM SectionMarks
)
SELECT
    s.FirstName,
    sub.Name AS Subject,
    r.MarksObtained,
    r.Result
FROM Results AS r
INNER JOIN dbo.Enrollments AS e ON e.Id = r.EnrollmentId
INNER JOIN dbo.Students AS s ON s.Id = e.StudentId
INNER JOIN dbo.Subjects AS sub ON sub.Id = r.SubjectId
ORDER BY e.SectionId, e.RollNumber, sub.Name;
GO

Common mistakes

NOT IN with a subquery that can return NULL. It silently returns no rows. Fix: use NOT EXISTS.
= with a subquery that returns several rows. Error 512. Fix: use IN, or make sure the subquery returns one value.
Correlated subqueries over huge tables. They run once per row. Fix: rewrite as a join with GROUP BY, or a CTE, and compare speed.
Ending the previous statement without a semicolon before WITH. SQL Server cannot tell where the CTE starts. Fix: end every statement with ;.
Expecting a CTE to be stored. It exists only for one statement. Fix: use a view (Lesson 21) when many queries need it.

In real projects

From 16 years of .NET projects

The NOT IN trap has bitten every team I have worked with. A nightly job sent "unpaid fee" reminders to parents "not in the paid list".

The day someone added a payment row with a NULL invoice, the job sent nothing at all, and nobody noticed for a week. The fix was one word: NOT EXISTS.

For long reports, I write CTEs the way I would explain the report to a colleague: first this, then that. When a number looks wrong, I run each CTE on its own and find the broken step in minutes, instead of staring at one 80-line query.

Best practices

Use EXISTS and NOT EXISTS for "is there any matching row" questions.
Never use NOT IN with a subquery; if you must, filter out NULLs inside it.
Aggregate in a CTE first, then join, to avoid multiplied totals.
Give CTEs names that describe what they hold, such as PaidPerInvoice.
Test each CTE on its own when a result looks wrong.
Start every query that begins with WITH on a new statement, after a semicolon.
Prefer the simplest tool that answers the question; nesting three subqueries deep is a sign to use CTEs.

Recap

A subquery is a query inside a query: one value, a list, or an existence check.
IN checks membership; EXISTS checks whether any row matches.
NOT IN returns nothing when the list contains NULL; NOT EXISTS is safe.
A correlated subquery uses outer columns and runs once per outer row.
A CTE names a query step with WITH; several CTEs can be chained.

Homework

Library module task: Write two reports. First, the overdue issues on 25 July 2026, with days late and the fine so far at 2 per day. Second, the books that have never been issued, using NOT EXISTS.

A correct result looks like: Advika's "The Jungle Book", 5 days late, fine 10; and "Malgudi Days" as the only book never issued.

Show the solution

library-assignment/019-library-subqueries.sql
-- Library homework, Lesson 19. Read-only.
USE SchoolDb;
GO

-- Overdue issues on 25 July 2026, with the fine at 2 per day late.
WITH OpenIssues AS (
    SELECT
        i.Id,
        i.BookCopyId,
        i.StudentId,
        i.DueOn,
        DATEDIFF(DAY, i.DueOn, CAST('2026-07-25' AS DATE)) AS DaysLate
    FROM dbo.BookIssues AS i
    WHERE i.ReturnedOn IS NULL
)
SELECT
    b.Title,
    s.FirstName,
    o.DueOn,
    o.DaysLate,
    o.DaysLate * 2 AS FineSoFar
FROM OpenIssues AS o
INNER JOIN dbo.BookCopies AS c ON c.Id = o.BookCopyId
INNER JOIN dbo.Books AS b ON b.Id = c.BookId
INNER JOIN dbo.Students AS s ON s.Id = o.StudentId
WHERE o.DaysLate > 0;

-- Books that have never been issued.
SELECT b.Title
FROM dbo.Books AS b
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.BookCopies AS c
    INNER JOIN dbo.BookIssues AS i ON i.BookCopyId = c.Id
    WHERE c.BookId = b.Id
);
GO

DATEDIFF(DAY, a, b) returns the number of days from date a to date b. A fixed date keeps the result the same every time; in the API, you will use today's date instead.

Quiz

A subquery used with = returns two rows. What happens?
1. The first row is used
2. Error 512: subquery returned more than 1 value
3. The average is used
4. No rows are returned
Show answer
Answer: B. Error 512: subquery returned more than 1 value Comparison operators need exactly one value; use IN for lists.
Why did WHERE Id NOT IN (SELECT GuardianId FROM dbo.Users) return 0 rows?
1. No guardian lacks a login
2. The list contains NULL, so every comparison is unknown
3. NOT IN is not supported
4. Users is empty
Show answer
Answer: B. The list contains NULL, so every comparison is unknown One NULL in a NOT IN list makes the condition unknown for every row.
What is a correlated subquery?
1. A subquery that returns one value
2. A subquery that uses a column from the outer query
3. A subquery inside a CTE
4. A subquery with GROUP BY
Show answer
Answer: B. A subquery that uses a column from the outer query It depends on the outer row, so it runs once per outer row.
How long does a CTE exist?
1. Until SQL Server restarts
2. Until the session ends
3. Only for the statement that follows it
4. Forever, like a table
Show answer
Answer: C. Only for the statement that follows it A CTE is part of one statement; use a view to reuse it.
Why sum payments per invoice in a CTE before joining to invoices?
1. CTEs are faster than tables
2. To avoid multiplied rows and get one total per invoice
3. Because JOIN cannot use SUM
4. To sort the results
Show answer
Answer: B. To avoid multiplied rows and get one total per invoice Aggregating first gives one row per invoice, so the join cannot double anything.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a subquery? concept

A subquery is a query inside another query, in brackets. It can return one value (used with = or >), a list (used with IN), or be used with EXISTS to test whether any row matches.

See the example · Permalink

F What is a CTE? concept

A CTE (Common Table Expression) is a named query written with WITH name AS ( ... ) before a statement. That statement can use the name like a table. It makes long queries readable as steps, and it exists only for that one statement.

See the example · Permalink

F Find the students who scored above their section's average. coding

Compare each mark with a subquery that returns the average for the same paper and section: WHERE m.MarksObtained > (SELECT AVG(MarksObtained) FROM dbo.Marks WHERE ...). In 10-A Unit Test 1 Mathematics, the average is 84.75, so Meera, Aarav, and Diya qualify.

See the example · Permalink

Mid (3-5 yrs)

M Why can NOT IN return no rows at all, and what should you use instead? concept

If the subquery's list contains a NULL, x NOT IN (...) is unknown for every row, because "not equal to NULL" is never true. So the query returns nothing. Use NOT EXISTS, which is not affected by NULLs, or filter NULLs out inside the subquery.

See the example · Permalink

M What is the difference between IN and EXISTS? concept

IN checks whether a value is in a list returned by the subquery. EXISTS checks whether the subquery returns any row at all, and can stop at the first match. For positive checks they often perform the same; for negative checks, NOT EXISTS is safer than NOT IN because of NULLs.

See the example · Permalink

M What is a correlated subquery? concept

A correlated subquery uses a column from the outer query, so it is evaluated for each outer row. Example: each student's last attendance date with (SELECT MAX(a.AttendanceDate) FROM dbo.AttendanceRecords AS a WHERE a.EnrollmentId = e.Id).

See the example · Permalink

Senior (6-9 yrs)

S Write a query for invoices that still have money due, with the balance. coding

In a CTE, sum the payments per invoice. Then left join the invoices to it, compute total minus discount minus ISNULL(paid, 0), and keep rows where that is above zero. Summing first avoids multiplied totals, and the left join keeps invoices with no payments.

How to answer: Write it in two steps aloud, explaining why each part exists (CTE to aggregate, LEFT JOIN for unpaid invoices, ISNULL for NULL sums). Mention edge cases: deleted payments, discounts, and cancelled invoices, and how you would test the result against one known invoice.

See the example · Permalink

S A nightly job using NOT IN suddenly sends no reminders at all. What happened? scenario

Most likely a NULL appeared in the column the NOT IN subquery returns, so the condition became unknown for every row and nothing qualified. Rewrite it with NOT EXISTS, fix or explain the NULL data, and add an alert when a reminder job processes zero rows.

How to answer: Diagnose from the symptom (zero rows, not an error), explain the three-valued logic briefly, then fix and prevent: NOT EXISTS, a data check, and monitoring that treats "zero processed" as suspicious.

See the example · Permalink

Architect / Lead (10+ yrs)

A When would you choose a CTE, a temporary table, or a view for a multi-step report? design

A CTE for readable steps inside one statement. A temporary table when an intermediate result is reused several times, is large, or benefits from its own index. A view when many queries and reports need the same logic. Each adds reuse or speed at the cost of more objects to manage.

How to answer: Compare scope (one statement, one session, permanent), reuse, and performance. Use SMS examples for each: a report card CTE, a temp table for a year-end calculation, a view for "current enrollments". Mention measuring before switching for speed.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-19-subqueries-ctes (code repository coming soon)
Files: queries/019-subqueries-ctes.sql, library-assignment/019-library-subqueries.sql
Video: coming soon

What's next

The last querying tool ranks and compares rows without grouping them away: class toppers and running totals. Lesson 20: Window functions

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: three shapes of subquery​

Example: IN and EXISTS​

Example: the NOT IN trap​

Example: subquery, join, or CTE?​

Example: a CTE​

Apply it to the SMS​

Example: above the section average​

Example: IN and EXISTS on SMS data​

Example: a correlated subquery​

Example: unpaid invoices with a CTE​

Example: CTEs in a chain​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: three shapes of subquery

Example: IN and EXISTS

Example: the NOT IN trap

Example: subquery, join, or CTE?

Example: a CTE

Apply it to the SMS

Example: above the section average

Example: IN and EXISTS on SMS data

Example: a correlated subquery

Example: unpaid invoices with a CTE

Example: CTEs in a chain

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next