Introduction
In this article, we will learn about Common Table Expressions in SQL Server with example queries and code examples.
Common Table Expression (CTE)
Common Table Expression or CTE in SQL is a temporary named result set created from a simple SELECT statement. The in-memory result set can be used within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. If we create the CTE in a Stored Procedure, I can’t use it in another Stored Procedure.
Sub-queries in SQL Server
A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.
Example
Select Name,Age, employeeID
From employee
Where employeeID in
(
Select employeeID from salary where salary >=1000 /******Sub Query******/
)
Why do we use a CTE in SQL Server?
In SQL, we will use sub-queries to join the records or filter the records from a sub-query. Whenever we refer to the same data or join the same set of records using a sub-query, code maintainability will be difficult. A CTE makes improved readability and maintenance easier.
Syntax
With aliastablename (column1,colun2,….)
AS
(Query)
We can use another CTE within a CTE but the query using the CTE must be the first query appearing after the CTE.
Example
With salaryCTE(EmployeeID)
AS
(Select employeeID from salary where salary >=1000)
, EmpDetailsCTE( Name, EmployeeID ,salary)
AS
(
Select Name,Age, employeeID
From employee Emp Join salaryCTE sa
on Emp. employeeID = sa. EmployeeID)
In the preceding example, first, we defined the CTE called salaryCTE. The SalaryCTE has a subquery that will select the employeeID from the salary table for the employee's salary greater than or equal to 1000. Then we have created one more CTE called EmpDetailsCTE that will select the name, age, and employeeID from the employee table for the employeeID's of salaryCTE.
Advantages of CTE
Here are some of the advantagss of using CTE.
- CTE improves code readability.
- CTE provides recursive programming.
- CTE makes code maintainability easier.
- Though it provides similar functionality as a view, it will not store the definition in metadata.
- Since data is in-memory, the query execution is much faster and hence increases performance.
Difference between Temp Table and CTE
CTEs often compared to temp tables or derived tables. Here is a detailed comparision on the same:
Difference between CTE, Derived Table, Temp Table, Sub Query, and Temp Variable
Conclusion
In this article, we learned about Common Table Expressions in SQL Server with example queries and code examples.
Still not sure? Want more CTE? Read here: What is CTE in SQL Server.