Back to course

Common Table Expressions (CTEs) using WITH

Database Fundamentals: From Zero to Hero

Lesson 31: Common Table Expressions (CTEs) using WITH

Common Table Expressions (CTEs), defined using the WITH clause, provide a way to write complex queries in a much cleaner, more readable, and structured manner. Think of a CTE as a temporary, named result set that exists only for the duration of the current query execution.

Basic Syntax

sql WITH [cte_name] AS ( -- Define the SELECT statement for the temporary result set SELECT column1, aggregate_function(column2) FROM table GROUP BY column1 )

-- Now reference the CTE in the main query SELECT * FROM [cte_name] WHERE [condition];

Example: Finding High Earners by Department

This is the same logic as the subquery example, but broken down into clearer steps.

  1. Calculate the average salary per department (DeptAvg).
  2. Use this temporary result to find employees who earn more than their department average.

sql WITH DepartmentAverages AS ( SELECT Department, AVG(Salary) AS AvgSalary FROM Employees GROUP BY Department )

SELECT E.Name, E.Salary, DA.AvgSalary FROM Employees E JOIN DepartmentAverages DA ON E.Department = DA.Department WHERE E.Salary > DA.AvgSalary; -- Filtering based on the CTE result

CTEs improve code readability and allow you to reuse complex logic within a single query.