Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.
A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.
Syntax
WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
To view the CTE results, use a select query with the name of the CTE expression.
Select [Column1,Column2,Column3,Column4...] from cte_expression_name
OR
SELECT * FROM cte_expression_name
Types of CTE (Common Table Expression)
There are 2 types of CTEs,
- Recursive CTE.
- Non-Recursive CTE.
Recursive CTE
A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.
A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.
We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.
First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.
Declare @RowNo int =1;
;with ROW_CTE as
(
SELECT @RowNo as ROWNO
UNION ALL
SELECT ROWNO+1
FROM ROW_CTE
WHERE RowNo < 10
)
SELECT * FROM ROW_CTE
Output
ROWNO
1
2
3
4
5
6
7
8
9
10
Non-Recursive CTE
Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.
As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.
Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.
;with ROW_CTE(ROWNO) as
(
SELECT
ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name
WHERE id <= 10
)
SELECT * FROM ROW_CTE
Output:
ROWNO
1
2
3
4
5
6
7
8
9
10
Nested CTE (Common Table Expression)
WITH First_CTE
AS (SELECT 1 EmpId, 'Rajesh Gami' Name)
,Second_CTE
AS (SELECT EmpID, Name, 'Gujarat' State FROM First_CTE)
SELECT * FROM Second_CTE
