Common Table Expression or CTE in SQL Server

What is CTE in SQL Server?

CTE in SQL Server is an advanced topic as well as a new feature of SQL. It was introduced in 2005 and 2018 respectively in PostgresSQL and MySQL.

CTE stands for Common Table Expression. Before the CTEs, we were using the derived table to define a large and complex query. The WITH clause in SQL provides a better way to write the auxiliary/helper statement which can be later used in larger queries.

These statements are referred to as common table expressions which are nothing but defining a temporary relational table to be used later in a SQL statement. The table is being called temporary because it exists only during the scope of the SQL statement written after CTEs.

CTEs are typically the result of several sub-queries(nested queries to get some information needed in the main query).

CTE Syntax in SQL Server

• <common_table_expression> : is the name of expression which we can use later in the SQL statement.
• [column names] : are List of comma-separated columns after the <common_table_expression> indicates the set of columns stores in that temporary result. The number of these columns must be the same as in <cte_query_definition>.
• AS : keyword is used to provide an alias, here a select statement is written as <cte_query_definition> to generate the temporary result set.
• <sql_statement> : In the end, we can refer to the common table expression in a query such as SELECT, INSERT, UPDATE, DELETE, or MERGE.

Why Do We Need CTE in SQL Server?

Using sub-queries in SQL, we can join the records or filter the records from a sub-query. We face maintainability issues whenever we refer to the same data or use the same sub-query to join the same records.

Additionally, there are Derived Tables, Temporary Tables, and Temporary Variables approaches in SQL that are somewhat similar to CTE, although each has its own set of cons compared to CTE. Common Table Expression proves its worth with improved readability and easier maintenance.

Example of CTE in SQL Server

After the discussion of theory and syntax, let's see some examples of CTE in SQL server.

First of all, we need to have some data for these examples, so let's define them.

CREATE DATABASE

SWITCH DATABASE

CREATE TABLES

Output :

INSERT DATA

After inserting some data our table will look something like this,

Now our data is ready so we can perform our query.

Scenario : 1

University provides an honors degree to those who secure more than $7.5$ CGPA, let's say academic cell wants to get records according to condition whether the student has secured more than $7.5$ CGPA or not.

Details Required :

• Unique ID of Student
• Full Name of Student
• CGPA of Student

Output :

Explanation:

• This is the simple CTE where we have just filtered the data of two tables with the help of join and based on a condition of having CGPA more than 7.5.

Scenario : 2

Let's say there is an urgent need for blood for a student having blood group B+, so how we can find all students having blood groups from B+, B-, O+, O- category.

Details Required :

• Unique ID of Student
• Full Name of Student
• Blood Group
• Contact Number
• Address

Output :

Explanation:

• We have created a CTE using the WITH keyword, which is having the name CTE_TABLE1.
• In the column list we are defining some required columns, also these columns must be the same as the ones written inside the AS() block.
• Later we are selecting student_id from the student, Concat function is used to concatenate the first name and last name.
• The phone, address, and blood group are selected from the student_details table according to the inner join.
• At the end, the where the statement is filtering the record according to the required blood group.

Scenario : 3

What if we want to calculate the average CGPA of M.Tech and B. Tech both as well as the number of students associated with these courses. Here we will use Multiple CTE.

Details Required :

• Course Name
• Number of Students
• Average CGPA

Output :

Explanation :

• Here we have two CTEs, one is fetching the total count and average CGPA of all M. Tech students and the other for B. Tech.
• JOIN is being used here to find the cgpa detail of students.
• In the end we have used both CTEs by selecting all columns.
• UNION ALL is being used here to combine the result of two SELECT Statement

Scenario : 4

Consider there is placement season in university and one of the recruiters wants the details of all students,

Details Required :

• Unique ID of Student
• First Name and Last Name of Student
• A column stating eligible and not eligible based on CGPA i.e., if cgpa is below 7 then the student is not eligible.
• Also a column having information about cgpa categories like Exceptional for more than 8.5, Excellent for more than 8, and good for more than 7.
• Email ID of Student

Output :

Explanation :

• Here we are fetching student id, first name, last name from student table, and cgpa, email from student_details.
• After that, we have 2 cases to define our new column of the table. One is for eligibility and the other is for cgpa category
• Subsequently, we are performing inner join with two tables respectively student_details and student_cgpa with the common field of student_id.
• In the end, we are just selecting the rows of our temporary table cte_student.

Types of CTE in SQL Server

1. Recursive CTE
2. Non-Recursive CTE

Recursive CTE

A recursive common table expression (CTE) references itself. As a result, the CTE executes repeatedly, and returns subsets of data, until it obtains the complete result set. This makes it an excellent choice for dealing with hierarchical and tree-structured data.

Recursive CTEs use repeated procedural loops. The recursive query calls itself repeatedly until the condition is satisfied. In a recursive CTE, we should provide a where condition to stop the recursion.

Syntax :

Non-recursive CTE

The non-recursive CTE uses no recursion or repeated processing. Previously, we discussed non-recursive CTEs and their examples.

When to Use CTE in SQL Server And When Not to?

Whenever we need to organize complex and long queries, CTEs are very useful. Though there is no difference between a subquery and a CTE from a functionality and performance point of view, using CTEs will improve the readability of your code as it breaks the code into separate steps. With this, errors become easier to fix and the code can be changed more easily.

A CTE is an excellent choice if recursive data access is required.

If we need to fetch the data multiple times, then we should not use CTE due to its limited scope of execution, because we will have to define CTE each time and that can be expensive. In this case, a temporary table might be the most suitable option.

Advantages of CTE in SQL Server

• The major advantage of CTE in SQL server is that it enhances code readability as compared to the subquery approach.
• Code maintenance becomes easier with CTEs.
• Because of recursive programming in CTE, these are especially effective at querying data for tree structures.
• The functionality of CTE is similar to view in the database but it doesn't store the definition in metadata.

Disadvantages of CTE in SQL Server

• It is not possible to reuse CTE in another query like view or function.
• Although it has an advantage over view, it cannot be nested as a view.
• Performance issues will be there if the table is being referenced frequently because it will require equivalent cost to construct that table again.