Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set created with the WITH clause.

CTEs make your SQL easier to read, organize, and reuse.

In this lesson, we'll look at non-recursive CTEs.

This means the CTE does not call itself — it is used once for simple logic or filtering.

The basic syntax for a CTE is as follows:

Copy
WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name;

• The WITH clause defines the CTE block
• The main query can then reference it as if it were a regular table

The following query returns users who selected two or more courses.

Copy
WITH multi_course_users AS (
  SELECT user_id, COUNT(*) AS course_count
  FROM user_courses
  GROUP BY user_id
  HAVING COUNT(*) >= 2
)
SELECT u.name, m.course_count
FROM multi_course_users m
JOIN users u ON u.user_id = m.user_id
ORDER BY u.name;

This query:

1. Creates a CTE (multi_course_users) to find users with at least two courses.
2. Joins it with the users table to display names and course counts.

• Readability: Breaks a large query into smaller, manageable parts
• Modularity: Allows you to reuse and test query components
• Maintainability: Makes it easier to update logic when separated

You can define multiple CTEs in a single query by separating them with commas:

Copy
WITH active_users AS (
  SELECT DISTINCT user_id FROM user_courses
),
recent_users AS (
  SELECT * FROM users WHERE name LIKE 'S%'
)
SELECT *
FROM active_users a
JOIN recent_users r ON a.user_id = r.user_id;

This query joins the two CTEs on the user_id column.