What is Common Table Expression(CTE) And How to use

By | May 15, 2017

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.

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(;)

Same Column names Should Have Different Alisas

For example take into consideration following two tables.

And if I write a CTE for the above two tables as shown in the below code.

I will get the error ‘The column ‘Name’ was specified multiple times for ‘CTE_Employee” as shown in the below figure

Common Table Expression

Common Table Expression

 

To circumvent this error we need to have different column name aliases for each of the columns as shown in the below code.

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

Common Table Expression

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.

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.