Hello, This is my first article about the SQL on my blog. In this article I will discuss about the Common Table Expression(CTE) and its use. I will also discuss many restrictions and usage scenarios as well.
What is Common Table Expression?
- CTE are similar to view or derived tables
- CTE are Temporary query definitions that can be predefined and then referenced within the same statement execution scope\
- Increases T SQL code readability
- Makes a big query more human readable
- Same CTE can be referenced multiple time but same is not the same case with derived table type
- No need to be persisted to the DB as object unlike view
- Multiple CTE definition can be defined separated by comma. This I will show in the later part of the article.
WITH [expression_name] -- is the identifier for CTE and --need to be unique within same WITH clause [Column Names] -- optional column names to be returned from the CTE, --unique and same number as CCTE_Query_Definition, assumes that query definition uses distinct column names AS [CTE_Query_definition,..]
Why do we Need of SemiColon(;) before defining CTE?
Right prior to the WITH statement if we have any other statement we need to have a semicolon for the previous statement.
Please find below a very simple example of common table expression. I have a table named MyTable and I am selecting Col1, Col2 from the table.
But before the CTE definition I have one more statement which gets the server name. In this case I have started the CTE with semicolon(;)
Select @@Servername ;WITH [CTE_New] (Col1, Col2) AS ( Select Col1, Col2 from MyTable ) Select Col1, Col2 from CTE_New
Same Column names Should Have Different Alisas
For example take into consideration following two tables.
Create Table Employee( EmployeeID int, Name varchar(50), DepartmentID int, Address nvarchar(100)) Create Table Department( DepartmentID int, Name varchar(50) )
And if I write a CTE for the above two tables as shown in the below code.
;WITH CTE_Employee as (Select EmployeeID, E.Name, D.Name from Employee E inner join Department D on E.DepartmentID = D.DepartmentID) Select * from CTE_Employee
I will get the error ‘The column ‘Name’ was specified multiple times for ‘CTE_Employee” as shown in the below figure
To circumvent this error we need to have different column name aliases for each of the columns as shown in the below code.
;WITH CTE_Employee (EmployeeID, EmployeeName, DepartmentName) as (Select EmployeeID, E.Name, D.Name from Employee E inner join Department D on E.DepartmentID = D.DepartmentID) Select * from CTE_Employee
Execution Scope of Common Table Expression
The common table expression definition and its usage should be in the same batch execution. If they are separated by the GO batch execution, it will throw an error.
Again there can be only one select statement for a single CTE in SQL. Please check the below figure
As shown in the figure. Multiple select statements will result in error.Once the first query is executed, the CTE expression goes out of scope and next select statement will throw an error.
The above statement can be written in a better way as shown below.
;WITH CTE_Employee (EmployeeID, EmployeeName, DepartmentName) as (Select EmployeeID, E.Name, D.Name from Employee E inner join Department D on E.DepartmentID = D.DepartmentID) Select * from CTE_Employee where EmployeeID = 1 UNION ALL Select * from CTE_Employee where EmployeeID <> 1 GO
Multiple Common Table Expression(CTE) in Single SQL Query
In this part of the article I will show how we can work with multiple CTE in single sql execution block. There can be chances that you have to use multiple CTE in single SQl query.
Please have a look at the below figure for a good example.
