Articles → SQL SERVER → Common Table Expression (CTE) In SQL Server

Common Table Expression (CTE) In SQL Server

In this article, we will discuss Common Table Expression (CTE) in SQL servers.

Purpose

The CTE is the temporary result set used with the select, insert, update, and delete statements. The CTE simplifies complex queries into smaller and more manageable pieces.

Syntax

WITH <CTE_Name> AS (
<Select query>
)

Example

Consider the sample Product table: -

Picture showing the data in the Product table

Click to Enlarge

And the Orders table: -

Picture showing the data in the Orders table

Click to Enlarge

Use the following query: -

;WITH sales AS (
    SELECT product_id, SUM(quantity * price) AS revenue
    FROM orders
    GROUP BY product_id
)
SELECT product_id, revenue
FROM sales
WHERE revenue > 20;

Output

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Friday, April 7, 2023

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**