Generating Temp Result-set Using WITH

tags: #sql/complex_queries

The WITH clause, also known as Common Table Expression (CTE), is a feature in SQL that allows you to define a temporary named result set within a single SQL statement:

WITH
  cte_name (column_name1, column_name2, ...) AS (
    -- subquery that defines the CTE
  )

// main SQL command
SELECT ...
FROM ...
JOIN cte_name ON ...
WHERE ...

We can also create multiple CTE tables before a query by separate each CTEs with a comma:

WITH cte1 AS (
    -- CTE1 definition
    SELECT column1, column2
    FROM table1
    WHERE condition1
),
cte2 AS (
    -- CTE2 definition
    SELECT column3, column4
    FROM table2
    WHERE condition2
)
-- Main query that uses the CTEs
SELECT cte1.column1, cte1.column2, cte2.column3, cte2.column4
FROM cte1
JOIN cte2 ON cte1.column1 = cte2.column3
WHERE condition3;

When is this used?

It is mainly used to simplify complex SQL commands by allowing the creation of a subquery that can be referenced multiple times within the main SQL command (i.e., can reference within a SELECT, INSERT, UPDATE, or DELETE statement).

Why is this used?

• Simplifying complex queries
• Improve readability

Example:

// generating a temporary subquery of the average salary of departments
WITH department_salaries AS (
  SELECT dept_no, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_no
)

// using temp result set in main query to find dept with avg salary >50000
SELECT departments.dept_name, department_salaries.avg_salary
FROM departments
INNER JOIN department_salaries
ON departments.dept_no = department_salaries.dept_no
WHERE department_salaries.avg_salary > 50000;