CTE Basics

Overview

A Common Table Expression (CTE) is a temporary, named result set defined using the WITH clause that exists only within the scope of a single query execution.

Learning Objectives

• Define CTEs using the WITH keyword.
• Simplify complex queries by replacing deeply nested subqueries.
• Improve query readability and maintainability.

Detailed Concept Explanation

CTEs act like temporary virtual tables or inline views. They allow you to break down long, complex SQL queries into modular, logical steps that can be referenced by downstream SELECT, INSERT, UPDATE, or DELETE statements.

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * 
FROM cte_name;

Code Examples

SQL

-- Calculate high-earning departments using a CTE
WITH DeptPayroll AS (
    SELECT 
        department_id,
        SUM(salary) AS total_payroll,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.dept_name,
    dp.total_payroll,
    dp.emp_count
FROM DeptPayroll dp
JOIN departments d ON dp.department_id = d.department_id
WHERE dp.total_payroll > 500000;

Best Practices

• Use Descriptive CTE Names: Give CTEs meaningful names (e.g. FilteredOrders, MonthlyAggregates) rather than generic ones (cte1, temp).
• Simplify Nested Subqueries: Replace nested subqueries in FROM clauses with CTEs to make code easier to read from top to bottom.

Interview Perspective

Interactive Challenges

Summary

CTEs construct named temporary result sets using WITH, organizing complex SQL into readable building blocks.