In SQL a common table expression(CTE) is a very useful thing to know. It comes in handy when generating complex queries.

A CTE is a temporary named result set which is used together with an outer query referencing the CTE. The named result set is derived from a simple query and is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.

Syntax

WITH cte_name (column_1, column_2, column_3) AS
(
    ...cte_query_statement
)
...outer_query_statement

Starts off with the WITH keyword followed by the name of the CTE cte_name. The column list is sometimes optional. cte_query_statement is the query that is used to retrieve the result set of the cte. This result set can then be used in the outer_query_statement query.

Example 1: Products with most sales

Say you want to generate the sales performance of different products within the 2023 year as shown in the table below.

Using a CTE that query would look something like.

WITH products_added_this_year AS (
    SELECT
        product_id,
        product_name,
        created_at
    FROM tbl_products
    WHERE created_at BETWEEN '2023-01-01' AND '2023-12-12'
)
SELECT
    products.product_id,
    products.product_name
    products.created_at,
    SUM(sales.quantity) AS sold_count
FROM products_added_this_year AS products
JOIN tbl_sales AS sales ON sales.product_id = products.product_id
WHERE sales.created_at BETWEEN '2023-01-01' AND '2023-12-12'
ORDER BY sold_count DESC

In the query above, we define a CTE called products_added_this_year. This CTE is then used to join to tbl_sales to get how many times that product was sold.

Example 1.2: Retrieve more details about the product using multiple CTEs

Extending the previous example, say we want to retrieve the department(from tbl_departments) that manages the products so that we can rank them too.

We can use multiple CTEs in the same query as follows.

WITH products_added_this_year AS (
    SELECT
        product_id,
        product_name,
        created_at
        -- new column
        department_id
    FROM tbl_products
    WHERE created_at BETWEEN '2023-01-01' AND '2023-12-12'
),
-- new cte
products_with_departments AS
(
    SELECT
        products.*,
        departments.department_id,
        departments.department_name
    FROM tbl_departments AS departments
    JOIN products_added_this_year AS products ON
        departments.department_id = products.department_id
)
SELECT
    products.product_id,
    products.product_name
    products.created_at,
    SUM(sales.quanitity) AS sold_count
    -- new columns
    products.department_id,
    products.department_name
FROM products_with_departments AS products
JOIN tbl_sales AS sales ON sales.product_id = products.product_id
WHERE sales.created_at BETWEEN '2023-01-01' AND '2023-12-12'
ORDER BY sold_count DESC

We start by defining a new CTE products_with_departments that references the previous CTE products_added_this_year. This new CTE retrieves department data from tbl_departments.

In the final query, we retrieve sales data and then join that with the products_with_departments CTE.

The results be as follows:

Part 2 of this article covers recursive CTEs and how you can use them.

References

• Microsoft learn: MSSQL With CTE Transact SQL