From the course: SQL Tips and Tricks for Data Science

Unlock the full course today

Join today to access over 23,100 courses taught by industry experts.

Common table expressions

Common table expressions - SQL Tutorial

From the course: SQL Tips and Tricks for Data Science

Start my 1-month free trial Buy for my team

Common table expressions

“

- [Instructor] Okay. Now we're going to take a look at common table expressions. Common table expressions are temporary structures that contain separate data than the main query. And they allow us to reference it again as a self-referencing join. This might be a little confusing. So you can think of a CTE or a common table expression like a sub query, where you define a dataset within another query, and then you can reference it from another part of the query, the outer query. Now CTEs are exactly like that. But they're also self-referencing, meaning you can do things like traverse hierarchies in your data within a single query. These little magical functions allow an incredible amount of expressiveness in your analysis. A really unique way is to use CTEs to do a hierarchy analysis. We'll do this in our demo. But to explain the idea is that when you have a hierarchy such as an employee hierarchy, where employees…

Contents