1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

CTE (common table expression)

Discussion in 'SQL Server' started by Sagar Jaybhay, Mar 25, 2020.

  1. Sagar Jaybhay

    Sagar Jaybhay New Member

    Joined:
    Jan 28, 2019
    Messages:
    29
    Likes Received:
    17
    Trophy Points:
    3
    Gender:
    Male
    Occupation:
    Sr. Software Developer
    Location:
    Pune
    Home Page:
    CTE Introduced in the SQL server 2005.

    CTE is like a temporary result set which is defined within the execution of the current context or execution scope of single select, insert, update delete and create view statement.

    It is similar to a derived table and it is not stored as an object like other objects in the SQL server.

    Remember CTE table is created with the keyword.

    Code:
    with CTEtable
    as
    (
    select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
    join Employee as e on d.DepartmentID=e.DepartmentID
    group by d.Department_Name
    )
    select * from CTEtable where empcount>100;
    
    
    CTE-1024x591.png


    In the above query, we didn’t mentioned the column name if your inner query is given distinct column name then there is no need to define column name else you need to define like shown below

    Code:
    with CTEtable(deptname,empcount)
    as
    (
    select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
    join Employee as e on d.DepartmentID=e.DepartmentID
    group by d.Department_Name
    )
    select * from CTEtable where empcount>100;
    
    
    CTE-with-column-name-defined-1024x550.jpg

    In the above query, you specify 2 columns so remember you need to specify the columns that select query is returning if our inner select query returning 3 columns then you need to specify these 3 columns in CTE.

    CTE is only referenced by select, insert, update and delete statement immediately follows the CTE expression.

    In this, With clause, you can create multiple CTE tables.

    Code:
    
    with CTEtable(deptname,empcount)
    as
    (
    select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d
    join tblEmp as e on d.deptid=e.deptid
    group by d.deptname
    ),
    tblnew_hr(deptname,id)
    as
    (
    select d.deptname,e.id from tblEmp e join tbldept d on
    e.deptid=d.deptid
    )
    select * from CTEtable
    union
    select * from tblnew_hr
    
    
    Multiple-CTE-1024x550.jpg
     
    shabbir likes this.

Share This Page