SQL - Common Table Expressions (CTEs) - Simplify Complex Queries Using WITH

Introduction

SQL queries can sometimes feel like tangled exam papers — lots of nested subqueries, unreadable code, and logic that’s hard to follow. Enter Common Table Expressions (CTEs) — a feature that brings clarity, structure, and elegance to your SQL queries. Especially helpful when analyzing complex data like student rankings or term-wise averages in a school database.

What is a CTE?

A CTE (Common Table Expression) is a temporary result set defined using the WITH clause that you can reference within a SELECT, INSERT, UPDATE, or DELETE query. It's like giving a name to a subquery and treating it like a table.

Syntax of CTE

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

School Example – Student Ranking

Consider the following marks table:

CREATE TABLE marks (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO marks VALUES
(1, 'Aarav', 'Maths', 95),
(2, 'Diya', 'Maths', 85),
(3, 'Sneha', 'Maths', 92),
(4, 'Karan', 'Maths', 78);

Using CTE to Rank Students

WITH ranked_students AS (
  SELECT
    roll_no,
    name,
    marks,
    RANK() OVER (ORDER BY marks DESC) AS rank
  FROM marks
  WHERE subject = 'Maths'
)
SELECT * FROM ranked_students;

roll_no | name   | marks | rank
--------+--------+-------+------
1       | Aarav  | 95    | 1
3       | Sneha  | 92    | 2
2       | Diya   | 85    | 3
4       | Karan  | 78    | 4

Why Use CTEs?

• Readability: Replaces long nested subqueries
• Reusability: Can use the same CTE in multiple places in your main query
• Modularity: Makes code easier to debug and maintain

Chaining Multiple CTEs

WITH maths_marks AS (
  SELECT * FROM marks WHERE subject = 'Maths'
),
top_scorers AS (
  SELECT name, marks FROM maths_marks WHERE marks > 90
)
SELECT * FROM top_scorers;

name   | marks
--------+-------
Aarav  | 95
Sneha  | 92

Recursive CTE Example (Advanced)

Let’s say we want to generate a sequence of roll numbers:

WITH RECURSIVE seq AS (
  SELECT 1 AS roll_no
  UNION ALL
  SELECT roll_no + 1 FROM seq WHERE roll_no < 5
)
SELECT * FROM seq;

roll_no
--------
1
2
3
4
5

Real-World School Use Cases

• Generating class toppers using RANK()
• Creating filtered views like pass/fail lists
• Building roll number series using recursive CTEs
• 📚 Comparing subject-wise performance across terms

CTE vs Subquery

Aspect	CTE	Subquery
Readability	High	Low with nesting
Reusability	Yes	No
Recursive Support	Yes	No
Performance	Same or better	Depends

Best Practices

• Use meaningful CTE names
• Keep logic within CTEs modular
• Avoid deeply nested subqueries; use chained CTEs instead
• Use recursive CTEs for tree-like or sequence generation

Summary

CTEs help you write cleaner SQL that’s easier to read, maintain, and extend. Especially in educational systems where data is often complex and hierarchical — such as results, attendance, or term reports — CTEs simplify your logic without sacrificing performance.

What’s Next?

Coming up: Window Functions — learn how to use RANK, DENSE_RANK, LEAD, and LAG to supercharge your analytics in SQL.