Getting to Know CTE: WITH

In programming, you can pull out a chunk of code and give it a name — basically, create a function. Same deal with CTEs. You can pull a SELECT subquery out of your main query, give it a name, and then use it in your main SQL query.

CTE (Common Table Expressions) are like a breath of fresh air for any dev tired of endless nested queries. They make your SQL code not just readable, but actually elegant. If you’ve ever stared at a mess of subqueries until your eyes crossed — it’s time to check out the “magic” of CTEs.

Picture building a house. Usually, you want to slap in the windows and screw on the doors ASAP (like tossing in subqueries), even if the walls aren’t up yet. But with CTEs, it’s different: first, you sketch out a neat draft — create a temp table, like you’re planning the house layout. Then, step by step, you build up the floors of your query. Stylish, reliable, technical.

Basically, a CTE is a virtual table you create on the fly with a SELECT query. Kind of like a subquery, but cooler. If in programming you can pull out a chunk of logic into a separate function with a clear name, in SQL that’s what a CTE does. You write a SELECT, give it a name — and use it as part of a big, complex query. Nice, right? Totally.

Example of an SQL query with a subquery:

-- main query
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subquery, got alias filtered_students

Pulled the subquery out separately:

-- CTE/subquery, got alias filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- main query
SELECT *
FROM filtered_students;

Wild fact: subqueries showed up 20 years before CTEs! The SQL-89 standard already had subqueries, but CTEs only landed in SQL-2009.

Syntax of WITH

A CTE starts with the WITH keyword and looks like this:

WITH cte_name AS (
    SELECT ... -- your query here
)

SELECT ...
FROM cte_name;

Here:

• cte_name — this is your CTE’s name. You can pick any meaningful name, like high_scores, filtered_data, or even best_students.
• Inside the parentheses () goes the query that preps the data for later use.
• After you define the CTE, you can use it like a regular table in your main query.

Example 1: Simple CTE

Let’s see how a CTE works with a real example. Imagine you have a students table — a list of students and their grades:

Our goal — pick all students with a grade above 85 and show their info.

Without a CTE:

You can do this with a subquery:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

But with a CTE — way easier on the eyes:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Admit it, it looks cleaner and makes more sense. We clearly separated data prep (WITH) from the main part of the query (SELECT). It’s like tidying up your desk before you start working — suddenly, it’s easier to breathe.

Example 2: Multiple CTEs

You can define several CTEs in one query. Super useful if you need to prep data in stages.

Given: a grades table, where students’ grades for courses are stored:

Task: for each student, find their average grade, then pick those whose average is above 85.

Solution with multiple CTEs:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- using the first CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- using the second CTE - high_achievers

Here:

1. student_averages preps the initial data — students’ average grades.
2. high_achievers uses that data to pick only those with grades above 85.

CTE vs Subqueries

Spoiler: CTEs don’t replace subqueries, but sometimes they’re way more convenient.

A subquery is a query inside a query. They’re handy when you need a quick result, but if you pile on too many, your code turns into chaos.

Example:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Subqueries can be inside SELECT, inside FROM, inside WHERE, and inside HAVING. Plus, they can reference columns from the outer query. CTEs struggle with that last part.

On the other hand, CTEs make your code way more readable, so it’s easier to maintain and less error-prone. Instead of nesting one query inside another, CTEs let you just “name” the result of a subquery and use it later.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTEs are especially handy if you need to use the prepped data more than once in a single query.

When to Use CTEs?

• When you want to break a complex query into several logical steps.
• If your query needs to be readable and maintainable. Nobody wants to untangle spaghetti-code-style nested structures.
• For temporary data prep that’s only used by the current query.

Final Example: Course Analysis

Let’s put it all together:

1. Find students with high average grades.
2. Show their names and the courses they’re enrolled in.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Notice how everything’s structured:

1. First, we prepped the average grades.
2. Then, we picked only the top students.
3. Then, we linked them to their courses.

Now you’re officially ready to start using CTEs to build beautiful, readable, and powerful SQL queries.

Go crush it in your own projects!