Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Overview to Common Table Expressions(CTE) in SQL SERVER :

    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 166
    Comment on it

    An Overview to Common Table Expressions(CTE) in SQL SERVER :

    Common Table expressions or CTE helps in making complex sql statements easier to understand and maintainable.         It is very difficult to read or write a complex Sql query using number of joins. While dealing with sub-queries it is often required that we have to select some data from the sub-query or we have to make a join over the sub-query and as our requirement goes on increasing the sub-queries will increase & hence the query will become very complex.
            By using CTE we can define our sub-query once with an alias name and can access it with the same name whenever required, this helps in reducing the complexity of the query even if the requirement goes on increasing.

    Let us understand it with the help of an example:

    SELECT * FROM  (
            SELECT A.Address, E.Name, E.Age From Address A
            Inner join Employee E on E.EID = A.EID) T
    WHERE T.Age > 50
    ORDER BY T.NAME
    

    In the above example we have made a sub-query (SELECT A.Address, E.Name, E.Age From Address A Inner join Employee E on E.EID = A.EID) with alias name T now if we have to use this query again further in the same query the whole query will get messed up and will become very complex to understand. What we can do is that we can use CTE, in which we will declare the sub-query once with an alias name and can then use it with the same name whenever required.

    The CTE of the above example can be written as:

    With T(Address, Name, Age)  --Column names for Temporary table
    AS
    (
    SELECT A.Address, E.Name, E.Age from Address A
    INNER JOIN EMP E ON E.EID = A.EID
    )
    SELECT * FROM T  --SELECT or USE CTE temporary Table
    WHERE T.Age > 50
    ORDER BY T.NAME
    

    NOTE:-> In this example the CTE was not required as we are not using the sub-query more than once but even then the use of CTE has made the readability of the query more clear.

    Syntax of CTE:

    1. The CTE name followed by the WITH keyword.
    2. The parameters (optional).
    3. The query within the round paranthesis followed by the AS keyword.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: