Data Science
Master the key differences between CTEs and subqueries to know exactly when and where to use each!
—
Should I use a CTE or a Subquery in SQL?? — you must be asking this question quite often.
I had it too!
A subquery, as its name suggests, is a query within another query, whereas CTE or a Common Table Expression is a temporary result set that you define separately. Both subquery and CTE can be as simple as using some filter and as complex as doing a complex data transformation.
And this similarity is often the source of confusion about why and when to use either of them.
When I started with data science, I always struggled to make this choice. However, as years passed, I realized, it is less about “better”, and more about the right fit to get the job done.
For example, when I work on a project with complex logic, where I need to break down data transformations or work with hierarchical data — I use CTEs. But I use subqueries when I need to do one-off calculations or filtering.
For a detailed discussion on CTE, I highly recommend reading —