Skip to main content
Practice

Common Table Expressions (CTEs)

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

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

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

That just means the CTE does not call itself, it's only 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;
  • WITH defines the CTE block
  • The main query refers to it as if it's a regular table

Example: Users who selected 2 or more courses​

The following query returns users who selected 2 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 2 courses.
  2. Joins that with the users table to display names and counts.

Benefits of Using CTEs​

  • Readability: Breaks a large query into manageable parts
  • Modularity: Makes it easier to test and reuse pieces
  • Maintainability: Changes to logic are easier when it's separated

Multiple CTEs​

You can define more than one CTE 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.