What is a CTE?

Write a named subquery with WITH.

What is a CTE?

A CTE (Common Table Expression) is a temporary result set. Defined with WITH.

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
)
SELECT * FROM active;

This query makes active customers a temporary table.

WITH defines a temporary result set (CTE) named active; the query after it uses it like a table. All columns are selected from active.

What is a CTE? — details

A CTE makes a query readable. It replaces subqueries and can be referenced multiple times.

For example, if you need the same active-customer list in both an orders and an invoices report, you define it once as a CTE and refer to it by name twice.

Exercise What is a CTE?

Define a CTE named active and SELECT from it.

Show example solution

WITH active AS (
  SELECT * FROM customers WHERE status = 'active'
)
SELECT * FROM active;

Test yourself

Which keyword starts a CTE?
- WITH
- AS
- FROM
- SELECT
Show answer

WITH — A CTE starts with WITH.

Add WITH

____ active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

Show answer

WITH active AS (SELECT * FROM customers WHERE status = 'active')
SELECT * FROM active;

What is a CTE: match the terms

CTE

temporary table
WITH

definition
AS

name
readable

clean

Next lesson: Using WITH

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies