Creating Temporary Tables with WITH

Temporary tables are an awesome way to save intermediate results so you can mess with them later. It's just like in programming: instead of copying the same chunky code everywhere, you stash it in a variable once and use it whenever you want. In SQL, these "variables" are often temporary tables.

But CTEs make things even easier — they let you create temporary tables without all the extra hassle:

• No worries about deleting.

  A CTE only lives for the duration of the query. As soon as SQL is done — the CTE vanishes, like an invisible assistant who did all the work and disappeared without a trace.

• Your code gets way clearer.

  Making a CTE is way simpler than messing around with creating and dropping a physical temp table. Everything's right there, nothing hidden.

• Perfect for "one-off" tasks.

  If you just need to process some data in one step — a CTE is perfect. Fast, clean, no side effects.

CTE Syntax as a Temporary Table

Let's refresh the CTE syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Here, WITH creates a temporary "table expression" that's available throughout the query where it's defined. It acts like a table, but only lives until the query finishes running.

Example: Counting Top Students

Let's make a temporary table using a CTE to find students whose average grade is above 90. Then we'll list them out.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Here's what we're doing:

• using WITH to create a temporary table called high_achievers.
• inside the CTE, we're grouping grades (grades) for each student (student_id) and calculating the average grade.
• in the SELECT * query, we just reference the temporary table high_achievers like any regular table.

Comparing CTEs and Temporary Tables

Sometimes people ask: what's the difference between using a CTE and a temp table created with CREATE TEMP TABLE?

Here's an example of a traditional temp table (CREATE TEMP TABLE) for the same data:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Don't forget to drop the table!

And the same query using a CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

When to Use CTEs vs. Temp Tables

CTEs are awesome when you want to quickly prep some intermediate data and work with it right away — no extra fuss. You don't have to worry about deleting anything: the CTE disappears as soon as the query is done. It's right there in your SQL code, not hiding somewhere else like a temp table. That makes your queries cleaner, simpler, and easier to read, especially if you're chaining together a bunch of data processing steps. Plus, you can combine CTEs, nest them, and build up some pretty complex logic — we'll talk about that later.

This setup works great when your query is a one-off and you only need the data for that query. But if you plan to use the result multiple times in different parts of your system, or you want to keep intermediate data around for the whole session — temp tables are a more reliable choice. Especially if you're dealing with big data and need top performance: in those cases, physical temp tables are more stable and faster.

It all depends on the job: CTEs are a quick, elegant tool for local data wrangling. Temp tables are the workhorses for more complex, long-running scenarios.

Example: Aggregating Data

Let's say we have an enrollments table that tracks which students are enrolled in which courses. We want to find out how many students are enrolled in each course, but only for courses with more than 5 students.

With a CTE, it looks like this:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Multiple CTEs: Building in Steps

What if you need to break a task into several steps? Like, first pick out students with high average grades, then find their courses? Easy!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
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, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Now the query has two CTEs:

1. high_achievers finds the top students.
2. student_courses matches students to their courses.

The result — a list of students with high average grades and their courses.

Common Mistakes When Using CTEs

Too much data. CTEs get materialized in PostgreSQL's memory. If you create a CTE with a huge result set, it can slow down your query or even blow the memory limit.

Overusing them. Using a CTE where a regular subquery would be simpler can make things confusing.

Forgotten indexes. If the data in your CTE comes from big tables without indexes, your query will be slow.