Common Table Expression (CTE) in SQL Server
We will use the SQL Server's Common Table Expressions or CTEs to make complex joins and subqueries easier. It also provides a way to query hierarchical data, such as an organizational hierarchy. This article gives a complete overview of CTE, types of CTE, advantages, disadvantages, and how to use them in SQL Server.
What is CTE in SQL Server?
A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be stored anywhere and will be lost as soon as a query's execution is completed. It first came with SQL Server 2005 version. A DBA always preferred CTE to use as an alternative to a Subquery/View. They follow the ANSI SQL 99 standard and are SQL-compliant.
CTE Syntax in SQL Server
The CTE syntax includes a CTE name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, DELETE, and MERGE query.
The following is the basic syntax of CTE in SQL Server:
In this syntax:
It should keep in mind while writing the CTE query definition; we cannot use the following clauses:
The below image is the representation of the CTE query definition.
Here, the first part is a CTE expression that contains a SQL query that can be run independently in SQL. And the second part is the query that uses the CTE to display the result.
Let us understand how CTE works in SQL Server using various examples. Here, we are going to use a table "customer" for a demonstration. Suppose this table contains the following data:
In this example, the CTE name is customers_in_newyork, the subquery that defines the CTE returns the three columns customer name, email, and state. As a result, the CTE customers_in_newyork will return all customers who live in New York State.
After defining the CTE customers_in_newyork, we have referenced it in the SELECT statement to get the details of those customers who are located in New York.
After executing the above statement, it will give the following output. Here, we can see that the result returns only that customer information who are located in New York State.
In some cases, we'll need to create multiple CTE queries and join them together to see the results. We may use multiple CTEs concept in this scenario. We need to use the comma operator to create multiple CTE queries and merge them into a single statement. The "," comma operator must be preceded by the CTE name to distinguish multiple CTE.
Multiple CTEs help us in simplifying complex queries that are eventually joined together. Each complex piece had its own CTE, which could then be referenced and joined outside the WITH clause.
NOTE: The multiple CTE definition can be defined using UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT.
The below syntax explains it more clearly:
Let us understand how multiple CTE works in SQL Server. Here, we are going to use the above "customer" table for a demonstration.
In this example, we have defined the two CTE names customers_in_newyork and customers_in_california. Then the result set of subqueries of these CTEs populates the CTE. Finally, we will use the CTE names in a query that will return all customers who are located in New York and California State.
New York and California State.
Why do we need CTE?
Like database views and derived tables, CTEs can make it easier to write and manage complex queries by making them more readable and simple. We can accomplish this characteristic by breaking down the complex queries into simple blocks that can reuse in rewriting the query.
Some of its use cases are given below:
Some of its advantages are given below:
Types of CTE in SQL Server
SQL Server divides the CTE (Common Table Expressions) into two broad categories:
A common table expression is known as recursive CTE that references itself. Its concept is based on recursion, which is defined as "the application of a recursive process or definition repeatedly." When we execute a recursive query, it repeatedly iterates over a subset of the data. It is simply defined as a query that calls itself. There is an end condition at some point, so it does not call itself 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.
Let us understand how recursive CTE works in SQL Server. Consider the below statement, which generates a series of the first five odd numbers:
When we execute this recursive CTE, we will see the output as below:
The below example is the more advanced recursive CTE. Here, we are going to use the "jtp_employees" table for a demonstration that contains the below data:
This example will display the hierarchy of employee data. Here table provides a reference to that person's manager for each employee. The reference is itself an employee id within the same table.
This CTE will give the following output where we can see the hierarchy of employee data:
A common table expression that doesn't reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a "With" clause followed by the CTE name and column list, then AS with parenthesis.
Disadvantages of CTE
The following are the limitations of using CTE in SQL Server: