SQL WITH Clause

Last Updated : 21 Aug, 2026

The SQL WITH clause (Common Table Expression or CTE) defines a temporary result set that can be used within a query. It simplifies complex SQL statements, making them easier to read, manage and reuse.

SQL-WITH-clause
  • WITH: Starts the CTE definition, creating a temporary result set.
  • QueryName: User-defined name to reference the CTE later.
  • AS: Connects the CTE name with its subquery.
  • ( ) : Encloses the subquery (e.g., a SELECT statement).
  • Subquery: Provides data for the CTE, improving readability and reuse.
  • Flow: Syntax, WITH QueryName AS (Subquery) followed by the main query.

Syntax

WITH cte_name (column1, column2, ...)
AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;
  • cte_name is the name of the Common Table Expression.
  • The query inside parentheses defines the temporary result set.
  • The main query uses this CTE as if it were a table.

Examples of SQL WITH Clause

First, we will create a demo SQL database and table, on which we will use the WITH Clause command.

sql_output

Example 1: Finding employees with Above-Average Salary

This example finds employees whose salary is above the overall average salary.

Query: 

WITH AvgSalaryCTE (average_salary) AS (
    SELECT AVG(salary)
    FROM employees
)
SELECT employee_id, name, salary
FROM employees
WHERE salary > (
    SELECT average_salary
    FROM AvgSalaryCTE
);

Output:

Screenshot-2026-08-20-175735

Example 2: Finding employees with the Lowest Salary

In this example, we find the employee or employees who earn the lowest salary in the company.

Query: 

WITH MinSalaryCTE (min_salary) AS (
    SELECT MIN(salary)
    FROM employees
)
SELECT employee_id, name, salary
FROM employees
WHERE salary = (
    SELECT min_salary
    FROM MinSalaryCTE
);

Output:

Screenshot-2026-08-20-180005
  • CTE: Calculates the lowest salary using MIN(salary).
  • Main SELECT: Retrieves employee details.
  • WHERE Filter: Returns employees whose salary equals the lowest salary.

Nested (Chained) WITH Clauses

A Nested or Chained WITH Clause defines multiple CTEs in one query, where each CTE can use the result of the previous one to simplify complex calculations.

WITH DeptAvg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
ranked_employees AS (
    SELECT e.employee_id,
           e.name,
           e.department,
           e.salary,
           d.avg_salary,
           RANK() OVER (
               PARTITION BY e.department
               ORDER BY e.salary DESC
           ) AS salary_rank
    FROM employees e
    JOIN DeptAvg d
        ON e.department = d.department
)
SELECT *
FROM ranked_employees
WHERE salary_rank = 1;

Output:

Screenshot-2026-08-20-180427
  • DeptAvg: Calculates the average salary for each department.
  • Rankedemployees: Ranks employees by salary within each department.
  • Final SELECT: Returns the highest-paid employee(s) from each department, including ties.

Note: When a query with a WITH clause runs, the subquery inside it is executed first to create a temporary result set, which is then used by the main query.

Comment

Explore