Skip to main content
Practice

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.


Syntax​

The basic syntax for a CTE is as follows:

CTE syntax
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

Example: Users who selected 2 or more courses​

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

CTE example
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;

Output:​

namecourse_count
Aisha2
Sofia2

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.

Benefits of Using CTEs​

  • 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

Multiple CTEs​

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

Multiple CTEs
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.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.