In what cases CTEs will perform better than intermediate tables ?
As we all know, CTEs are a useful SQL feature that can help you design cleaner, more maintainable queries and better handle recursive data structures.
🚀 A Common Table Expression (CTE) is a temporary SQL result set that can be used in a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are very beneficial for simplifying difficult searches, increasing readability, and enabling recursive queries.
🚀A query with a CTE can execute predicate push-down (moving a filter closer to where a table is being read) and apply operators in a streaming manner.
🔍If you build an intermediate table and filter later, you must read more data, and the second half of the processing cannot begin until the first part is completed.
🔍Queries with CTEs, on the other hand, might become so complicated that the optimizer may struggle to identify a decent query plan (because to the sheer number of alternatives) and settle on a plan that is inferior to the combined plans of two distinct, simpler queries.
⭐ Consider a CTE that selects * from big_table followed by a subsequent step (in the same query) that narrows the results significantly. Does the query processor really need to scan the full table? No*…
