When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.

Consider the following SQL statement.

SELECT * FROM (

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID) E

WHERE E.Gender = ‘Male’

ORDER BY T.EmpName

The syntax of CTE is as follow

- The CTE Name (followed by WITH keyword)

- The Column List (Optional)

- The Query (Appears within parentheses after the AS keyword)

If we write the above messy query using CTE it would be like

With E(EmpName, Department, Gender)

AS

(

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID

)

SELECT * FROM E

WHERE E.Gender = ‘Male’

ORDER BY E.EmpName

This way the query can be made more readable and easy to understand.