It is just a general question regarding the concept of CTE. I learnt how to make them but I am still unclear about "WHERE" to use them.

Could anyone summarize to me of where I should be using them?

Thanks

CodePudding user response：

There are 3 main cases to use Common Table Expressions:

• when you have to (hierarchical query / recursion);
• to make the query easier to understand;
• to show off to colleagues.

CodePudding user response：

There are two times you MUST use a CTE:

• To implement a recursive query. No other way to do it.
• To use the same subquery multiple times in your query. For example, let's say you wanted to look for bank accounts doing large internal transfers. You could summarize inflows and outflows by account by day and then join the result to itself (inflow total to outflow total) to come up with the candidates. In the old (pre-CTE) days, you would have to repeat the summarization query twice and hope the optimizer would see they were identical. Now, summarize them in a CTE and then reference the intermediate results twice; the optimizer nails it every time.

Beyond that, it's purely a matter of taste. Personally, I only use a CTE for these two scenarios.