From the course: Advanced SQL for Query Tuning and Performance Optimization

Unlock the full course today

Join today to access over 24,800 courses taught by industry experts.

Using common table expressions to avoid repetitive computation

Using common table expressions to avoid repetitive computation - SQL Tutorial

From the course: Advanced SQL for Query Tuning and Performance Optimization

Start my 1-month free trial Buy for my team

Using common table expressions to avoid repetitive computation

“

- [Instructor] Common table expressions are often used as an alternative to subqueries 'cause many of us find them often more readable and more easy to reuse than using subqueries. So common table expressions can be computed once, which is really optimal if you have say, a long-running calculation or a long-running query that will generate a single list in that same list of values or a result set is used across all of the correlated queries that that is going to work with. Now, common table expressions can also be computed multiple times or each time or in line with the primary query. Now, that's optimal when each invocation of the query can use information from the parent query to make whatever that subquery or that common table expression, make that query more efficient. So if the values that you're looking up in the correlated query change over the course of the dataset that's calculated for the parent query, then you…

Contents