thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Intermediate

Common Table Expression (CTE)

A named temporary result set defined with WITH that can be referenced in the statement that follows.

Definition

CTEs make complex queries readable by splitting them into logical steps and can be referenced several times in the same statement. Most engines do not materialize CTEs by default, so a CTE that is referenced twice may be computed twice.

Example

WITH regional AS (SELECT Region, SUM(Sales) s FROM Orders GROUP BY Region) SELECT * FROM regional WHERE s > 1e6;

Exam tip

A CTE exists only for the single statement that follows it; it is not a temporary table.

Related terms