SQL Server – What is Common Table Expression (CTE)?

Who is online?  130 guests and 0 members
home  »  articles  »  SQL Server – What is Common Table Expression (CTE)?

SQL Server – What is Common Table Expression (CTE)?

change text size: A A A
Published: 7/13/2011 1:03:18 PM by  Piyush Bajaj  - Views:  [78204]

Hello Folks,

You must have heard CTE before. If you don’t know much about it, then don’t worry at all. I will be giving you some heads up:

  • The Common Table Expression (CTE) was introduced earlier in the SQL Server 2005.
  • The CTE defines about a temporary view, which can be referenced in the same query just as a view .
  • The CTE’s can be used and compiled in exactly the same ways that simple Subqueries are being used.
  • It can be used instead of temp table or table variables in the stored procedures in the circumstances.
  • CTE’s can also recursively refer to the same table using a union or union all, and this works great for searching an adjacency pairs pattern hierarchy.
  • The CTE uses the WITH clause, so the syntax can be shown as:
WITH CTEName (Column aliases)
AS (Subquery)
SELECT statement
FROM CTEName;
  • Here the Select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.
  • The WITH keyword not only begins a CTE, it also adds a hint to a table reference. This is why the statement before a CTE must be terminated with a semicolon.
  • The following example will make you understand what actually is the CTE;

I have two tables; Students1 and Students2-

 

Now, we will create a CTE named as ‘CTE1’ which will select SID column from Students2 table. So, the CTE1 can be called anywhere in the outer query where it needs. Like it is being called in the WHERE clause here, because we need to retrieve the Names of Students1 table, where SID is being equal to CTE1:

 

WITH CTE1 (SID)
AS (SELECT SID FROM Students2 WHERE Name = 'Robin')
SELECT Name FROM Students1
WHERE SID = (SELECT SID FROM CTE1);

 

The result can be seen as:

 

Suppose, if want to retrieve data from multiple tables, then it’s better to use CTE with multiple references. This will become clearer, if you follow up the example:

For this, we will also have to include the third table Persons;

Now, if I want to retrieve all the details from these three tables about all the name of the students from the Students1 table, then write the query like this:

 

WITH CTESTUD1
AS (SELECT SID,Name,City,State FROM Students1),
CTESTUD2
AS (SELECT SID,Cell_No FROM Students2),
CTEPER
AS (SELECT P_Id,Address,M_Id FROM Persons)
SELECT CTESTUD1.SID, Name, Address, Cell_No, City, State
FROM CTESTUD1 
LEFT JOIN CTESTUD2 ON CTESTUD1.SID = CTESTUD2.SID
LEFT JOIN CTEPER ON CTESTUD2.SID = CTEPER.P_Id;

 

The result set will be;

There are two disadvantages involved with this:

  • Firstly, CTEs cannot be nested like Subqueries.
  • Secondly, CTEs cannot reference the main query; they are self-contained like the simple Subqueries. They may reference to any of the CTEs defined before it or even to itself.

 

Well this was all about Common Table Expression (CTE), so if you would have any queries do comment!

Hope you got it understood well :)

If you liked this post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

And also comments on this!!

 

Regards

Piyush Bajaj

@piyushbajaj007

piyush.bajaj.2007@gmail.com

www.sqlservergeeks.com/blogs/piyush.bajaj.2007

tags : Common Table Expression (CTE) in SQL Server, CTE, How to create CTE in SQL Server, How to use CTE in SQL Server
  To rate this article please  register  or  login

Author

Piyush Bajaj Piyush Bajaj (Member since: 5/23/2011 11:25:15 AM)
I am very passionate about SQL Server. I also did certification on MCTS - SQL Server 2008, Database Development, and MCTS - SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field. Please feel free to drop me any question online or offline, i will try to give you the best possible answer from my side. Right now i am working as a SQL Server developer in TCS. I have an experience of just 2 years, well i can only say that "if you have an interest and passion, experience might become a very small thing".

Comments (10)

Subir
7/15/2011 10:34:22 AM Subir said:

Hi Piyush..

 

Its good one, can u use CTE in stored procedure

Subir..

by
piyush.bajaj.2007
7/15/2011 10:42:46 AM Piyush Bajaj said:

Well thanks Subir;

Ya definitely it can be used instead of temp table or table variables in the stored procedures.

by
Harsha
11/13/2012 7:16:21 PM Harsha said:

Hi Piyush,

Thanks for the post buddy.. And moreover, can we use joins inside the cte's?

like

with cte()

as

(select ....

inner join tbl ...

 )

by
Parthasaradhi
12/3/2012 11:44:18 AM Parthasaradhi V said:

I think we can run queries faster on a view when compared to CTE (correct me if am wrong).

Please help me in finding the best advantages of CTE over view.

-Parth

 

 

by
piyush.bajaj.2007
12/7/2012 7:01:21 PM Piyush Bajaj said:

@Harsha Yes you can use Joins inside the CTE.

by
piyush.bajaj.2007
12/7/2012 7:35:39 PM Piyush Bajaj said:

@Parth I like your question. 

Yes i think we can run queries faster in view than CTE most of the time because we can also use Indexed View with it, while CTE is just a temporary view.

And one of the best advantages which i can see of CTE over view is "CTE can reference itself means to the same table using union or union all".

by
yashaswini
1/11/2013 3:53:29 AM yashaswini said:

what is the diff between cte and table variable??? why v go for cte instead of table variable??

wat is the advantage of cte??

by
SOURABH
7/27/2013 7:46:48 AM SOURABH said:

CTE is used for removing duplicate values from the table

by
SOURABH BHARDWAJ
7/27/2013 7:54:25 AM SOURABH BHARDWAJ said:
CTE is used fro for removing duplicate row from the table like an example as give below:
WITH cte as(
  SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
                            ORDER BY  quicklabdumpid DESC ) RN
  FROM   quicklabdump)
delete from cte where RN>1
by
yogeesh
2/22/2014 12:42:51 PM yogeesh said:

Sir can  u explan brieflly

whats cast and convert? with exmple

whats use of these?

type of casting and convertion

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Articles RSS Feed

Most Recent Articles