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.

The basic syntax for a CTE is as follows:

Copy
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

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

• 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

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

Next, you'll explore Recursive CTEs, a powerful way to work with hierarchical or self-referencing data.