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-

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 🙂

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