Please explain When to use CTE(Common Table Expression)?

Question

Please explain When to use CTE(Common Table Expression)? Please Elaborate with real world expamle?

Pravat Kumar · Answer

Hi Navjot, below some point related to CTE. 1. The CTE is not the replacement of the Temp Table or Temp Variable Table. 2. The scope of the CTE is limited to the very first SELECT statement. 3.CTE Performing the same calculation multiple times over across multiple query components. 4.CTE enable users to more easily write and maintain complex queries via increased readability and simplification.

Suraj Kumar · Answer

Hello Navjot, Below post url can give you idea when to use CTE? CTEs are a powerful feature in SQL Server. The syntax is easy to use and we can build any kind of hierarchical tables and data based on our needs using a CTE. It executed very fast. You can create a view or a table like temp table and use it in join in your sql query. If want to retrieve data from multiple tables, then it’s better to use CTE with multiple references . Advantages CTEs cannot be nested like Subqueries. Secondly, CTEs cannot reference the main query; they are self-contained like the simple Subqueries. They may reference to any of the CTEs defined before it or even to itself. https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017