Mastering SQL: Understanding CTEs, Views, and Temporary Tables for Cleaner, More Efficient Queries

SQL offers powerful tools like CTEs, Views, and Temporary Tables to simplify complex queries and manage data efficiently. Though they may seem similar, each serves a unique purpose. Understanding their differences helps you write clearer, faster, and more maintainable SQL. This article explains what they are, how they differ, and when to use each.

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a named temporary result set or virtual table that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause and lasts only for the duration of the query it’s part of.

Think of a CTE as a temporary view or subquery that exists only during query execution, allowing you to:

• Break down complex queries into simpler parts.
• Reference intermediate result sets multiple times.
• Write recursive queries.

Why Use CTEs?

CTEs bring several benefits over traditional subqueries or derived tables:

1. Improved Readability and Maintainability: By naming intermediate result sets, your SQL becomes easier to read, debug, and maintain. Instead of embedding complex logic directly inside your main query, you separate it clearly.
2. Avoid Repetition: If you need to use the same subquery multiple times in a query, you can define it once as a CTE and reuse it, making your SQL DRY (Don’t Repeat Yourself).
3. Support for Recursion: CTEs support recursive queries, which allow you to traverse hierarchical data structures such as organizational charts, bill of materials, or folder trees.
4. Simplify Complex Queries: Breaking down complicated joins, aggregations, or transformations into multiple named steps makes it easier to understand and modify your queries.

Basic Syntax of a CTE

The general syntax for a CTE is:

WITH cte_name [(column1, column2, ...)] AS (
    -- Define the CTE query here
    SELECT ...
    FROM ...
    WHERE ...
)
-- Use the CTE in a query
SELECT *
FROM cte_name
WHERE ...;

• cte_name is the name you give to the temporary result set.
• You can optionally specify column aliases after the CTE name.
• The CTE query can be any valid SQL query.
• After the WITH clause, you write the main query that uses the CTE.

Example: Using a Simple CTE

Imagine a table employees with columns employee_id, name, and salary. You want to find employees earning more than the average salary.

Without a CTE, you might write:

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Using a CTE:

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)
SELECT *
FROM employees, avg_salary
WHERE employees.salary > avg_salary.avg_sal;

Here:

• The CTE avg_salary calculates the average salary once.
• The main query compares each employee’s salary to that average.
• The query is clearer and easier to expand or reuse.

Multiple CTEs

You can define multiple CTEs by separating them with commas:

WITH
cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ...
)
SELECT *
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;

Recursive CTEs: Working with Hierarchical Data

A recursive CTE is a special type of CTE that references itself. This is useful for traversing hierarchical data like employee-manager relationships, folder structures, or bill of materials.

Syntax of Recursive CTE

WITH RECURSIVE cte_name AS (
    -- Anchor member: the base query
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- Recursive member: query that references cte_name
    SELECT ...
    FROM ...
    JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Example: Employee Hierarchy

Suppose employees has:

• employee_id
• name
• manager_id (nullable; NULL if top-level manager)

To find all employees under a top-level manager:

WITH RECURSIVE subordinates AS (
    -- Anchor member: top-level managers (no manager)
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: get employees reporting to previous level
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

This query:

• Starts with top-level managers.
• Recursively finds all employees who report to these managers.
• Builds the entire hierarchy in subordinates.

Advantages and Limitations

Advantages

• Makes SQL more modular and easier to understand.
• Recursion support enables hierarchical queries.
• Simplifies complex joins and transformations.

Limitations

• Some database engines have limitations on recursion depth.
• Performance can vary; in some cases, CTEs are optimized less than equivalent subqueries or temp tables.
• Not all databases support recursive CTEs (most modern ones like PostgreSQL, SQL Server, Oracle, MySQL 8.0+, and SQLite do).

Use Cases for CTEs

• Breaking down complex queries into smaller parts.
• Calculating aggregates once and reusing.
• Recursive queries on hierarchical data.
• Improving query readability.
• Temporary views in complex data transformations.

Views

A view in SQL is a virtual table that is based on the result of a stored query. It does not store data physically, but behaves like a real table when you query it.

In simpler terms

A view is a named query that you can save and reuse, just like a table — but it doesn’t actually store data. When you select from a view, the database runs the underlying query and shows you the result.

Syntax of a View

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

You can then use view_name like a regular table:

SELECT * FROM view_name WHERE column1 > 100;

Does not store data physically?

This means that:

• A view is not like a regular table that stores rows on disk.
• Instead, it’s just a stored SQL query — like a shortcut.
• The data shown when you query a view comes from the underlying tables, not from the view itself.

Think of it like a window:

You look through a window to see what’s inside a house — but the window itself doesn’t hold the furniture. The furniture is in the house (the base table); the window (the view) just lets you see it in a specific way.

Behaves like a real table when you query it

Even though a view doesn’t store any data:

• You can run SELECT * FROM view_name just like a table.
• You can filter, join, and sort the data from a view.
• In many cases, you can even update the data through a view (if it’s simple enough and your database supports it).

To your query, the view looks like a table. The SQL engine handles the rest by internally replacing the view with its underlying query.

Example

# View:

CREATE VIEW it_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'IT';


# Querying the view:
SELECT * FROM it_employees WHERE salary > 100000;

Behind the scenes, SQL engine runs:

SELECT id, name, salary
FROM employees
WHERE department = 'IT' AND salary > 100000;

So:

• it_employees does not store any rows itself.
• The data comes live from employees every time you query the view.

What Happens When Base Table Changes?

If you add or change rows in the employees table,the view re-runs the query on the updated table.

Why This Matters

• Views save space because they don’t duplicate data.
• You always get the latest data.
• But: querying a view can be slower than a real table for large datasets, since it re-executes the logic each time — unless you use a materialized view (which does store data).

CTEs vs Views

Temporary

• A temporary table in SQL is a special kind of table that exists only for the duration of a session or transaction. It does store data physically, but only temporarily.
• A temporary table is a table that exists temporarily for storing intermediate results and is automatically deleted when the session ends (or earlier, depending on how it’s defined).

Syntax

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);



INSERT INTO temp_table_name VALUES (...);
SELECT * FROM temp_table_name;

Example

-- STEP 1: Create a sample base table called 'orders'
CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

-- STEP 2: Insert sample data into 'orders'
INSERT INTO orders (customer_id, amount) VALUES
(1, 2000),
(1, 4000),
(2, 7000),
(3, 15000),
(4, 2500),
(3, 5000),
(2, 4000),
(5, 3000),
(5, 8000),
(6, 1000);

-- STEP 3: Create a TEMPORARY table to hold high-spending customers
CREATE TEMPORARY TABLE top_customers (
    customer_id INT,
    total_spent DECIMAL(10, 2)
);

-- STEP 4: Populate the temporary table with customers spending over 10,000
INSERT INTO top_customers
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

-- STEP 5: View the result
SELECT * FROM top_customers;

Note top_customers will be deleted once session is over

Temporary vs Permanent Tables

When to Use Temporary Tables

• Staging complex calculations before using them in a final query.
• Breaking down complex ETL pipelines.
• Storing intermediate results in stored procedures.
• Avoiding repeated subqueries.
• Sharing data across multiple queries in the same session.

Key Facts

• Temporary tables are real tables: they store data physically (not virtual like views).
• You can index them, join them, update them, etc.
• They are automatically dropped at the end of the session (unless explicitly dropped earlier).
• Some databases (like PostgreSQL) also support ON COMMIT DELETE ROWS to clear data after a transaction.

Comparing CTEs vs Temporary Tables vs Views in SQL

Summary of Use Cases

Conclusion

In SQL, CTEs, Views, and Temporary Tables each play unique and powerful roles in organizing, optimizing, and simplifying your data workflows.

• CTEs are perfect for breaking down complex, often recursive queries into manageable parts within a single execution.
• Views provide a reusable, secure, and abstracted layer over your data, helping maintain consistency and simplifying access for multiple users or applications.
• Temporary Tables offer a flexible, physical storage option for intermediate results, especially useful when you need to reuse or manipulate data multiple times within a session.

Choosing the right tool depends on your specific use case — whether you need temporary logic within a query, reusable abstractions, or session-based intermediate storage. Mastering these SQL constructs will greatly enhance the readability, maintainability, and performance of your database interactions.

Resources

• Essential SQL Queries collection
• Window Function