Sign in to save your place.

MODULE 9 OF 12

Subqueries & CTEs

Use subqueries in WHERE and FROM, and name result sets with WITH ... AS (CTEs).

Subqueries

A subquery is a SELECT inside another query. It can appear in WHERE (e.g. WHERE Id IN (SELECT ...)) or in FROM (e.g. FROM (SELECT ...) AS sub).

Subquery in WHERE

Use it to compare against a set or single value: WHERE CustomerId IN (SELECT Id FROM Customers WHERE Country = 'UK') or WHERE Amount > (SELECT AVG(Amount) FROM Orders).

Subquery in FROM

Use a subquery as a derived table: SELECT * FROM (SELECT CustomerId, SUM(Amount) AS Total FROM Orders GROUP BY CustomerId) AS t. You must give it an alias (e.g. t).

CTE — WITH ... AS

A Common Table Expression (CTE) names a result set with WITH name AS (SELECT ...), then you query that name. Makes complex queries easier to read and reuse. Example: WITH TopCustomers AS (SELECT ...) SELECT * FROM TopCustomers.

Key Takeaway

Subqueries nest one query inside another. CTEs define a named result set first, then reference it in the main query.

Structure

Subquery: outer query wraps inner query (in WHERE or FROM). CTE: WITH defines a "virtual table" by name, then the main SELECT uses it.

Subquery and CTE examples

-- Subquery in WHERE: orders above average amount
SELECT * FROM Orders
WHERE Amount > (SELECT AVG(Amount) FROM Orders);

-- CTE: name the result set, then use it
WITH OrderTotals AS (
    SELECT CustomerId, SUM(Amount) AS Total
    FROM Orders GROUP BY CustomerId
)
SELECT c.Name, ot.Total
FROM OrderTotals ot
INNER JOIN Customers c ON c.Id = ot.CustomerId
ORDER BY ot.Total DESC;

Practice

Write: (1) SELECT customers WHERE Id IN (subquery returning IDs of customers with at least one order). (2) A CTE that computes per-product total quantity, then SELECT from it joined to Products.

Quiz

1. What is a subquery?

A SELECT statement nested inside another query
A new table
A type of JOIN
An index

Correct! A subquery is a SELECT inside another query.

A subquery is a SELECT statement used inside another query (e.g. in WHERE or FROM).

2. What does WITH ... AS define?

A CTE—a named result set you can reference in the query
A new physical table
A backup
A login

Correct! WITH ... AS defines a Common Table Expression (CTE).

WITH ... AS defines a CTE: a named result set that the following SELECT can use like a table.

October 2026: a CTE is a name, not a permission

WITH names a query so the statement can read it. A CTE can be recursive for a tree. It does not hide rows from someone who can query the base tables. Permissions do that, and they are the security lesson.

If the inner query is used once, a CTE and a subquery are the same idea. Pick the spelling that the next reader can follow.

WITH totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM sales
    GROUP BY customer_id
)
SELECT * FROM totals WHERE total > 100;

Try this. Rewrite one subquery from this lesson as a CTE. The result set should stay the same.

Knowledge check

What does a CTE change about who may read the base table?