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

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-

1_SQL_Server_What_is_Common_Table_Expression_ (CTE)

2_SQL_Server_What_is_Common_Table_Expression_ (CTE)

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:

3_SQL_Server_What_is_Common_Table_Expression_ (CTE)

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;

4_SQL_Server_What_is_Common_Table_Expression_ (CTE)

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;

5_SQL_Server_What_is_Common_Table_Expression_ (CTE)

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 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; 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.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

12 Comments on “SQL Server – What is Common Table Expression (CTE)?”

  1. Well thanks Subir;

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

  2. 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 …
    )

  3. 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

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

    wat is the advantage of cte??

  5. @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”.

  6. 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

  7. Sir can u explan brieflly
    whats cast and convert? with exmple
    whats use of these?
    type of casting and convertion

  8. CTE will help to delete the duplicate very easily.. Try something like this..

    WITH CTE_Dup AS
    (
    SELECT *,
    ROW_NUMBER()OVER (PARTITION BY UPC, STORE, TRANDATE ORDER BY UPC, STORE, TRANDATE)
    AS ROW_NO
    FROM #TEMPCTEDUPLIATE
    )
    DELETE FROM CTE_Dup WHERE ROW_NO > 1;

Leave a Reply to Harsha Cancel reply

Your email address will not be published. Required fields are marked *