Common Table Expression (CTE) in SQL Server

CTE -Common Table Expression are used to make your complex joins and subqueries easier, it is a one-time result set and only exist for duration of query.

In this article we’ll learn what is CTE, Recursive CTE, Non-Recursive CTE, Advantages and Dis-Advantages of CTE.

WHAT IS CTE-(Common Table Expression)

CTE is a temporary-named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement.

CTE Syntax —

WITH cte_name (column_names)

AS (query)

SELECT * FROM cte_name;

Example —

Here, we are going to use a table “[dbo].[tbl_cust_data]” for a demonstration.

Fig.1

In this example, We’ll name the CTE as salary_cust and the subquery columns -name, phone number and salary.

This CTE will return all the data of customer with salary greater than 2000. After defining CTE, we will use SELECT statement to return the data.

Query —

WITH salary_cust ([ID],[Name],[PhoneNumber],[Salary])

AS (select * from [dbo].[tbl_cust_data] where Salary>2000)

SELECT * FROM salary_cust ;

After executing the above statement, it will give the following output.

Fig.2

Not that difficult, right?! You can also use Multiple CTE and use joins and Unions to query the data.

Syntax for Multiple CTE —

WITH

cte_name1 (column_names) AS (query),

cte_name2 (column_names) AS (query)

SELECT * FROM cte_name

UNION ALL

SELECT * FROM cte_name;

Now that we know CTE and Multiple CTE, We will see —

Types of CTE in SQL Server

1. Recursive CTE

Recursive CTE that references itself, in simple words a query that calls itself, it should have an END statement so that it doesn’t go on infinitely.

A recursive CTE must have a UNION ALL statement and a second query definition that references the CTE itself in order to be recursive.

2. Non-Recursive CTE

A CTE doesn’t reference itself is known as a non-recursive CTE. It does not use the concept of recursion.

Advantages Of CTE

• CTE facilitates code maintenance easier.
• CTE increases the readability of the code.
• It increases the performance of the query.
• CTE makes it possible to implement recursive queries easily.

Disadvantages of CTE

• CTE are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
• Since it’s just a shortcut for a query or subquery, it can’t be reused in another query.
• The number of columns in the CTE arguments and the number of columns in the query must be the same.

Conclusion

Common table expressions are a powerful resource of the SQL language. They let us create more readable queries and manage format differences between table data and report data. Finally, a CTE is easy and simple for code maintainability.

Fig 3.

That’s it for this Article , I hope you liked it. I would really appreciate if you would share this Article and Drop your Thoughts, Suggestions and Recommendations, always looking to Improve, Learn and Grow.

Until Next Time,

STAY SHARP. STAY CURIOUS.

CIAO