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:
WITH CTEName (Column aliases)
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:
AS (SELECT SID,Name,City,State FROM Students1),
AS (SELECT SID,Cell_No FROM Students2),
AS (SELECT P_Id,Address,M_Id FROM Persons)
SELECT CTESTUD1.SID, Name, Address, Cell_No, City, State
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:
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!!
Its good one, can u use CTE in stored procedure
Well thanks Subir;
Ya definitely it can be used instead of temp table or table variables in the stored procedures.
Thanks for the post buddy.. And moreover, can we use joins inside the cte's?
inner join tbl ...
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.
@Harsha Yes you can use Joins inside the CTE.
@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".
what is the diff between cte and table variable??? why v go for cte instead of table variable??
wat is the advantage of cte??
CTE is used for removing duplicate values from the table
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
delete from cte where RN>1
Sir can u explan brieflly
whats cast and convert? with exmple
whats use of these?
type of casting and convertion
CTE will help to delete the duplicate very easily.. Try something like this..
WITH CTE_Dup AS
ROW_NUMBER()OVER (PARTITION BY UPC, STORE, TRANDATE ORDER BY UPC, STORE, TRANDATE)
DELETE FROM CTE_Dup WHERE ROW_NO > 1;
Leave a comment