SQL WITH Tutorial

Summary: In this tutorial, you’ll learn how to use the SQL WITH statement to improve query readability and break complicated queries into smaller pieces.

Introduction to SQL WITH statement #

The SQL WITH statement creates one or more named result sets called common table expressions (CTEs).

CTEs are temporary results that you define first and then reference in the statement.

Instead of writing a subquery like:

SELECT select_list
FROM 
   ( SELECT ... ) AS t
WHERE condition;Code language: SQL (Structured Query Language) (sql)

A subquery is a query embedded within another query.

You can write:

WITH t AS (
   SELECT ...
) 
SELECT select_list
FROM t
WHERE condition;Code language: SQL (Structured Query Language) (sql)

The WITH statement often makes the query easier to read because it separates logic into smaller pieces. Because of this, we typically use the WITH statement to break complicated queries into simpler parts.

SQL WITH statement syntax #

Here’s the syntax of the SQL WITH statement:

WITH cte_name [(column_list)] AS(
   cte_query_definition
)
SELECT select_list
FROM cte_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

cte_name is the name of the CTE.
column_list is an optional name for columns of the CTE. If you don’t specify it, SQL will use the columns of the query that defines the CTE.
AS (cte_query_definition) is the query that defines the CTE.
SELECT select_list FROM cte_name is a query or statement that references the CTE.

Note that you can define multiple CTEs within a single SQL statement.

SQL WITH example #

We’ll use employees and departments tables from the sample database:

The following example shows how to use the WITH statement to return the min, max, and average salary of all departments:

WITH
  department_salaries AS (
    SELECT
      department_id,
      SUM(salary) total_salary
    FROM
      employees
    GROUP BY
      department_id
  )
SELECT
  MIN(total_salary),
  MAX(total_salary),
  AVG(total_salary)
FROM
  department_salaries;Code language: SQL (Structured Query Language) (sql)

Try it

How it works:

First, define a CTE called department_salaries using the WITH statement. The CTE returns a temporary result set that includes the total salary of each department.

WITH
  department_salaries AS (
    SELECT
      department_id,
      SUM(salary) total_salary
    FROM
      employees
    GROUP BY
      department_id
  )

The output of the CTE is a result set with two columns department_id and total_salary.

Second, the SELECT statement retrieves the min, max, and average of the total salary of that result set as if it were a table:

SELECT
  MIN(total_salary),
  MAX(total_salary),
  AVG(total_salary)
FROM
  department_salaries;Code language: SQL (Structured Query Language) (sql)

Note that we reference the CTE name department_salaries within the main query.

If you don’t use the WITH statement, you have to use a subquery that is more difficult to read:

SELECT
  MIN(total_salary),
  MAX(total_salary),
  AVG(total_salary)
FROM
  (
    SELECT
      department_id,
      SUM(salary) total_salary
    FROM
      employees
    GROUP BY
      department_id
  ) t;Code language: SQL (Structured Query Language) (sql)

Try it

Using multiple CTEs within one query #

You can define multiple CTEs with a single WITH clause. Each CTE can referee the ones before it. This allows you to break a complex query into clear and logical steps.

For example, the following query uses the WITH statement to find employees whose salaries are above their department’s average salary:

WITH
  department_salary_stats AS (
    SELECT
      department_id,
      AVG(salary) AS avg_salary
    FROM
      employees
    GROUP BY
      department_id
  ),
  above_avg_employees AS (
    SELECT
      e.employee_id,
      e.first_name,
      e.last_name,
      e.salary,
      e.department_id,
      dss.avg_salary
    FROM
      employees e
      INNER JOIN department_salary_stats dss ON e.department_id = dss.department_id
    WHERE
      e.salary > dss.avg_salary
  ),
  employee_details AS (
    SELECT
      aae.employee_id,
      aae.first_name,
      aae.last_name,
      d.department_name,
      round(aae.salary, 0) salary ,
      round(aae.avg_salary, 0) avg_salary,
      round(aae.salary - aae.avg_salary, 0) AS salary_diff
    FROM
      above_avg_employees aae
      INNER JOIN departments d ON aae.department_id = d.department_id
  )
SELECT
  employee_id,
  first_name,
  last_name,
  department_name,
  salary,
  avg_salary,
  salary_diff
FROM
  employee_details
ORDER BY
  department_name,
  salary_diff DESC;Code language: SQL (Structured Query Language) (sql)

Try it

How it works

The first CTE returns a result set that includes the average salary for each department.
The second CTE returns employees whose salaries are above that department’s average.
The third CTE joins the result to the departments table and calculates the salary difference.
The final query returns the result in a clean, readable format.

When to use CTE, subquery, and temporary table #

CTE #

Use a CTE when you want:

Better readability
logical step-by-step query structure.

Subquery #

Use a subquery when:

The query logic is simple, small, and self-contained.
Creating a named step would not improve query readability.

Temporary table #

Use a temporary table when you want to:

Reuse the temporary table across multiple statements.
Index intermediate results.

Summary #

CTE stands for common table expression.
Use the WITH statement to define a temporary result set (or a CTE) within a query.
A query can have one or more CTEs.
Use the WITH statement to simplify complicated queries.

Databases #

Was this tutorial helpful ?