Non-Recursive CTEs with WITH Clause0%

Chaining Multiple CTEs in a Single Query

Non-Recursive CTEs with WITH Clause

Intermediate12 min read•Updated: 2026-09-12

Non-Recursive CTEs with WITH Clause

A Common Table Expression (CTE) is a temporary, named result set defined within the execution scope of a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). Introduced in MySQL 8.0, CTEs use the WITH keyword and dramatically enhance query readability, modularity, and maintainability compared to nested derived tables (subqueries).

The Problem with Nested Derived Subqueries

Consider a query that calculates department salary metrics and joins those metrics back to employee details:

1-- Nested derived table approach: Hard to read and maintain
2SELECT e.first_name, e.salary, dept_stats.avg_sal
3FROM employees e
4JOIN (
5    SELECT department_id, AVG(salary) AS avg_sal
6    FROM employees
7    GROUP BY department_id
8) AS dept_stats ON e.department_id = dept_stats.department_id
9WHERE e.salary > dept_stats.avg_sal;

When subqueries are nested 3 or 4 layers deep, code readability plummets. CTEs eliminate this nesting by declaring intermediate result sets at the top of the statement.

Non-Recursive CTE Syntax

1WITH cte_name [(column_alias1, column_alias2, ...)] AS (
2    -- Inner query definition
3    SELECT column1, column2, ...
4    FROM source_table
5    WHERE condition
6)
7-- Main query consuming the CTE
8SELECT *
9FROM cte_name
10WHERE filter_condition;

Refactoring with a Clean CTE

Here is the exact same employee salary comparison rewritten with a CTE:

1WITH DeptSalaryMetrics AS (
2    SELECT 
3        department_id,
4        AVG(salary) AS avg_sal,
5        MAX(salary) AS max_sal,
6        MIN(salary) AS min_sal
7    FROM employees
8    GROUP BY department_id
9)
10SELECT 
11    e.emp_id,
12    e.first_name,
13    e.salary,
14    ROUND(d.avg_sal, 2) AS dept_avg_salary,
15    ROUND(e.salary - d.avg_sal, 2) AS diff_from_avg
16FROM employees e
17JOIN DeptSalaryMetrics d ON e.department_id = d.department_id
18WHERE e.salary > d.avg_sal
19ORDER BY diff_from_avg DESC;

Notice how logical the flow becomes:

1
Define the named CTE: DeptSalaryMetrics.
2
Reference DeptSalaryMetrics in the main SELECT query just like a standard table or view.

Column Aliasing in CTE Headers

You can define explicit column aliases directly in the CTE header:

1WITH RegionalRevenue(region, total_sales, order_count) AS (
2    SELECT 
3        customer_region,
4        SUM(order_amount),
5        COUNT(order_id)
6    FROM orders
7    GROUP BY customer_region
8)
9SELECT region, total_sales, order_count
10FROM RegionalRevenue
11WHERE total_sales > 100000;

CTE Lifecycle & Scope

Single Query Scope: A CTE exists only during the execution of the statement that defines it. As soon as the query returns results, the CTE is discarded from memory.
DML Compatibility: CTEs can be used preceding INSERT INTO ... SELECT, UPDATE ... JOIN CTE, and DELETE ... USING CTE statements in MySQL 8.0+.
Optimization: MySQL 8.0's optimizer determines whether to merge the CTE inline or materialize it into an in-memory temporary table for optimal execution speed.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL keyword introduces a Common Table Expression?

Question 2

In which MySQL version were Common Table Expressions (CTEs) officially introduced?

Question 3

What is the lifespan of a Common Table Expression?

Question 4

What is a major readability advantage of CTEs over nested derived subqueries?

Question 5

Can a CTE be referenced in an INSERT or UPDATE statement?

Next Lesson

Chaining Multiple CTEs in a Single Query

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Security & Data Abstraction with Views	Chaining Multiple CTEs in a Single Query

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Chaining Multiple CTEs in a Single Query

Non-Recursive CTEs with WITH Clause

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Non-Recursive CTEs with WITH Clause

The Problem with Nested Derived Subqueries

Consider a query that calculates department salary metrics and joins those metrics back to employee details:

1-- Nested derived table approach: Hard to read and maintain
2SELECT e.first_name, e.salary, dept_stats.avg_sal
3FROM employees e
4JOIN (
5    SELECT department_id, AVG(salary) AS avg_sal
6    FROM employees
7    GROUP BY department_id
8) AS dept_stats ON e.department_id = dept_stats.department_id
9WHERE e.salary > dept_stats.avg_sal;

When subqueries are nested 3 or 4 layers deep, code readability plummets. CTEs eliminate this nesting by declaring intermediate result sets at the top of the statement.

Non-Recursive CTE Syntax

1WITH cte_name [(column_alias1, column_alias2, ...)] AS (
2    -- Inner query definition
3    SELECT column1, column2, ...
4    FROM source_table
5    WHERE condition
6)
7-- Main query consuming the CTE
8SELECT *
9FROM cte_name
10WHERE filter_condition;

Refactoring with a Clean CTE

Here is the exact same employee salary comparison rewritten with a CTE:

1WITH DeptSalaryMetrics AS (
2    SELECT 
3        department_id,
4        AVG(salary) AS avg_sal,
5        MAX(salary) AS max_sal,
6        MIN(salary) AS min_sal
7    FROM employees
8    GROUP BY department_id
9)
10SELECT 
11    e.emp_id,
12    e.first_name,
13    e.salary,
14    ROUND(d.avg_sal, 2) AS dept_avg_salary,
15    ROUND(e.salary - d.avg_sal, 2) AS diff_from_avg
16FROM employees e
17JOIN DeptSalaryMetrics d ON e.department_id = d.department_id
18WHERE e.salary > d.avg_sal
19ORDER BY diff_from_avg DESC;

Notice how logical the flow becomes:

1
Define the named CTE: DeptSalaryMetrics.
2
Reference DeptSalaryMetrics in the main SELECT query just like a standard table or view.

Column Aliasing in CTE Headers

You can define explicit column aliases directly in the CTE header:

1WITH RegionalRevenue(region, total_sales, order_count) AS (
2    SELECT 
3        customer_region,
4        SUM(order_amount),
5        COUNT(order_id)
6    FROM orders
7    GROUP BY customer_region
8)
9SELECT region, total_sales, order_count
10FROM RegionalRevenue
11WHERE total_sales > 100000;

CTE Lifecycle & Scope

Single Query Scope: A CTE exists only during the execution of the statement that defines it. As soon as the query returns results, the CTE is discarded from memory.
DML Compatibility: CTEs can be used preceding INSERT INTO ... SELECT, UPDATE ... JOIN CTE, and DELETE ... USING CTE statements in MySQL 8.0+.
Optimization: MySQL 8.0's optimizer determines whether to merge the CTE inline or materialize it into an in-memory temporary table for optimal execution speed.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL keyword introduces a Common Table Expression?

Question 2

In which MySQL version were Common Table Expressions (CTEs) officially introduced?

Question 3

What is the lifespan of a Common Table Expression?

Question 4

What is a major readability advantage of CTEs over nested derived subqueries?

Question 5

Can a CTE be referenced in an INSERT or UPDATE statement?

Next Lesson

Chaining Multiple CTEs in a Single Query

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Security & Data Abstraction with Views	Chaining Multiple CTEs in a Single Query

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next