Imagine you want to find students whose marks are greater than the average marks of all students.
You could write a query with a subquery. However, when queries become complex, they can become difficult to read and understand.
PostgreSQL provides Common Table Expressions (CTEs) to make complex SQL queries easier to organize and understand.
A CTE allows us to create a temporary named result that can be used within another SQL query.
CTE stands for Common Table Expression.
A CTE is a temporary named result set that exists only during the execution of a single SQL statement.
In simple words:
A CTE allows us to divide a complex SQL query into smaller, understandable parts.
CTEs are created using the WITH keyword.
WITH cte_name AS (
SELECT column1, column2
FROM table_name
)
SELECT *
FROM cte_name;
Understanding the syntax:
| Keyword | Purpose |
|---|---|
WITH |
Starts the CTE |
cte_name |
Name of the temporary result |
AS |
Introduces the query |
SELECT |
Retrieves data for the CTE |
Final SELECT |
Uses the CTE result |
We will use a students table for practical examples.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
marks INT
);
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science', 85),
(2, 'Sara', 'Mathematics', 72),
(3, 'Ahmed', 'Computer Science', 65),
(4, 'Usman', 'Physics', 78),
(5, 'Hina', 'Mathematics', 90),
(6, 'Bilal', 'Physics', 55);
SELECT *
FROM students;
Expected output:
| student_id | student_name | department | marks |
|---|---|---|---|
| 1 | Ali | Computer Science | 85 |
| 2 | Sara | Mathematics | 72 |
| 3 | Ahmed | Computer Science | 65 |
| 4 | Usman | Physics | 78 |
| 5 | Hina | Mathematics | 90 |
| 6 | Bilal | Physics | 55 |
Suppose we want to display students who have obtained more than 75 marks.
SELECT *
FROM students
WHERE marks > 75;
WITH high_marks AS (
SELECT *
FROM students
WHERE marks > 75
)
SELECT *
FROM high_marks;
Expected output:
| student_id | student_name | department | marks |
|---|---|---|---|
| 1 | Ali | Computer Science | 85 |
| 4 | Usman | Physics | 78 |
| 5 | Hina | Mathematics | 90 |
Understanding the query:
WITH starts the CTE.high_marks is the name of the CTE.We do not always need to retrieve all columns.
For example, display only student names and marks of students who scored at least 80.
WITH high_marks AS (
SELECT student_name, marks
FROM students
WHERE marks >= 80
)
SELECT *
FROM high_marks;
Expected output:
| student_name | marks |
|---|---|
| Ali | 85 |
| Hina | 90 |
We can use aggregate functions inside a CTE.
Suppose we want to calculate the average marks of all students.
WITH average_marks AS (
SELECT AVG(marks) AS avg_marks
FROM students
)
SELECT *
FROM average_marks;
Expected output:
| avg_marks |
|---|
| 74.1667 |
The CTE calculates the average marks and gives the result a name: average_marks.
We can use the result of a CTE in the WHERE clause of the final query.
Suppose we want to find students whose marks are greater than the average marks.
WITH average_marks AS (
SELECT AVG(marks) AS avg_marks
FROM students
)
SELECT *
FROM students
WHERE marks > (
SELECT avg_marks
FROM average_marks
);
Expected output:
| student_id | student_name | department | marks |
|---|---|---|---|
| 1 | Ali | Computer Science | 85 |
| 2 | Sara | Mathematics | 72 |
| 4 | Usman | Physics | 78 |
| 5 | Hina | Mathematics | 90 |
Correction to observe: The average is approximately 74.17, so Sara’s 72 marks are not greater than the average. The correct output is:
| student_id | student_name | department | marks |
|---|---|---|---|
| 1 | Ali | Computer Science | 85 |
| 4 | Usman | Physics | 78 |
| 5 | Hina | Mathematics | 90 |
CTEs can also be used with GROUP BY.
Suppose we want to calculate the average marks department-wise.
WITH department_average AS (
SELECT
department,
AVG(marks) AS average_marks
FROM students
GROUP BY department
)
SELECT *
FROM department_average;
Expected output:
| department | average_marks |
|---|---|
| Computer Science | 75 |
| Mathematics | 81 |
| Physics | 66.5 |
This query first calculates department-wise averages and then displays the result.
We can use HAVING inside a CTE to filter grouped results.
Suppose we want to display departments whose average marks are greater than 70.
WITH department_average AS (
SELECT
department,
AVG(marks) AS average_marks
FROM students
GROUP BY department
HAVING AVG(marks) > 70
)
SELECT *
FROM department_average;
Expected output:
| department | average_marks |
|---|---|
| Computer Science | 75 |
| Mathematics | 81 |
We can also use a CTE with JOIN to combine data from multiple tables.
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
INSERT INTO departments
VALUES
(1, 'Computer Science'),
(2, 'Mathematics'),
(3, 'Physics');
Suppose we want to display students along with their department information.
WITH student_data AS (
SELECT
student_id,
student_name,
department,
marks
FROM students
)
SELECT
student_data.student_name,
departments.department_id,
student_data.department,
student_data.marks
FROM student_data
INNER JOIN departments
ON student_data.department = departments.department_name;
The CTE first prepares student data. The final query joins that result with the departments table.
PostgreSQL allows us to define multiple CTEs in a single query.
For example, calculate the average marks and then retrieve students who scored above that average.
WITH average_marks AS (
SELECT AVG(marks) AS avg_marks
FROM students
),
high_performers AS (
SELECT *
FROM students
WHERE marks > (
SELECT avg_marks
FROM average_marks
)
)
SELECT *
FROM high_performers;
Understanding the query:
average_marks calculates the average marks.high_performers selects students above that average.Multiple CTEs are separated by commas.
Both CTEs and subqueries can help solve complex SQL problems.
However, their structure and readability are different.
SELECT *
FROM students
WHERE marks > (
SELECT AVG(marks)
FROM students
);
WITH average_marks AS (
SELECT AVG(marks) AS avg_marks
FROM students
)
SELECT *
FROM students
WHERE marks > (
SELECT avg_marks
FROM average_marks
);
| CTE | Subquery |
|---|---|
Uses the WITH keyword |
Written inside another query |
| Has a temporary name | Usually has no independent name |
| Can improve readability | Can become difficult to read when nested |
| Can define multiple named results | Can use nested queries |
| Exists for one SQL statement | Exists within the query in which it is written |
A recursive CTE is a special type of CTE that refers to its own result.
It is useful for working with hierarchical data, such as:
WITH RECURSIVE number_list AS (
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM number_list
WHERE number < 5
)
SELECT *
FROM number_list;
Expected output:
| number |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Understanding the query:
A recursive CTE has two main parts.
1. Initial Query (Anchor)
SELECT 1 AS number
This starts the process with the number 1.
2. Recursive Query
SELECT number + 1
FROM number_list
WHERE number < 5
This adds 1 to the previous result until the condition becomes false.
3. UNION ALL
Combines the initial result with the results generated by the recursive query.
4. WITH RECURSIVE
Tells PostgreSQL that the CTE can refer to itself.
A CTE can also be used with INSERT statements.
For example, insert students with marks greater than or equal to 80 into a separate table.
CREATE TABLE top_students (
student_id INT,
student_name VARCHAR(50),
marks INT
);
WITH selected_students AS (
SELECT student_id, student_name, marks
FROM students
WHERE marks >= 80
)
INSERT INTO top_students
SELECT *
FROM selected_students;
SELECT *
FROM top_students;
Expected output:
| student_id | student_name | marks |
|---|---|---|
| 1 | Ali | 85 |
| 5 | Hina | 90 |
A CTE can also help identify records that need to be updated.
For example, increase marks by 5 for students whose marks are below 70.
WITH low_marks AS (
SELECT student_id
FROM students
WHERE marks < 70
)
UPDATE students
SET marks = marks + 5
WHERE student_id IN (
SELECT student_id
FROM low_marks
);
This query:
We can also use a CTE to identify records for deletion.
For example, delete students whose marks are below 60.
WITH low_marks AS (
SELECT student_id
FROM students
WHERE marks < 60
)
DELETE FROM students
WHERE student_id IN (
SELECT student_id
FROM low_marks
);
This query first identifies students with marks below 60 and then deletes their records.
Important: Always check the records selected by a CTE before using it with UPDATE or DELETE.
WITH keyword.WITH RECURSIVE is used for recursive CTEs.high_marks to display students with marks greater than 80.WITH keyword?WITH RECURSIVE?| Concept | Description |
|---|---|
| CTE | Common Table Expression |
WITH |
Starts a CTE |
AS |
Defines the CTE query |
| CTE Name | Name of the temporary result |
| Multiple CTEs | Multiple named results separated by commas |
| Recursive CTE | CTE that refers to itself |
WITH RECURSIVE |
Creates a recursive CTE |
| Anchor Query | Initial result of a recursive CTE |
| Recursive Query | Generates subsequent results |
UNION ALL |
Combines recursive results |
Next Topic: Window Functions in PostgreSQL
Students will learn how to use ROW_NUMBER(), RANK(), DENSE_RANK(), OVER(), and PARTITION BY to perform calculations across related rows without grouping them into a single result.