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.