What are Common Table Expressions?
Common Table Expressions (CTEs) are a valuable feature in SQL that lets you create temporary result s...
For further actions, you may consider blocking this person and/or reporting abuse
Great stuff as always. 🔥
I'm always waiting for your new posts.
Recursive Table Expressions 😢 Well explained.
Hey @karishmashukla, Nice post!
I was not fully aware about these CTEs.
It opened my curiosity 😁
Thanks!
Glad it was helpful 😁
🔥 🔥
For me you have left out the key benefits of CTEs. Namely 1) Ease of testing. You can test the logic of each CTE in isolation of the main query. This is especially useful if you have complex rules that require multiple steps
2) reusability. If you need the same logic in multiple places (e.g. you need to know the start date of a contract so that you can calculate days running, days to next review and days to end) you can calculate the values once and use them multiple times.
3) code readability. By moving complex joins, subqueries and co-related queries out of the main query you keep the query narrow and easier to read because you don't need to think about the lower level edge case logic that it held in the CTEs
I read the post again after seeing your comment and cannot understand where are these points left out. In the section "When to use CTEs" the author literally mentions "Break down complex operations into simpler steps", "Have reusable code if you need the same logic at multiple places", "Improve code readability and maintainability if your query involves subqueries, multiple joins, or intricate filtering conditions".
These are really simple things and I see no need to write them in paragraphs. As a reader it saves a lot of time and also helps me understand things quicker.
Rest of the things can be researched by the reader if really interested 😄
Having read your reply and re-read the post, yes they are mentioned in the article preface but were not explored. What is a complex query and why is it 'bad'. How does rewriting it in CTEs make it easier to comprehend, test and fix edge cases. The example given for summing dept salaries works fine as an example case to understand CTE syntax but could easily have been achieved with SUM and GROUP BY or even using SUM() OVER(), both of which are shorter code using familiar constructs. If the logic needed to exclude managers and allow for apportionment of an employee to multiple departments then lifting this to a CTE makes more sense.
Maybe a section on when NOT to use CTEs would be a valuable addition
Better to be appreciative of people who teach. Takes efforts and time on their end. :D
Where does the e in e.employees come from?
SELECT e.employee_id, e.name, e.manager_id, oh.level + 1FROM employees e
Here it comes from. It’s convenient to alias a table in sql where you have to chose multiple properties.
This is why I always use the longhand syntax of 'employee AS e'
It makes it clear what is going on and can avoid syntax error when you accidentally mistype the alias
True. And that is the standard way generally accepted by everyone as it provides more concise readability. Especially in larger schemas.
@shifi @wakywayne @aarone4 Totally agree. I tend to write things quickly when experimenting. Updated. Thank you :D
Nothing wrong on your part. Your example was concise as is. Complying with loose standards such as code formatting or keyword selection is pretty subjective. Good post tho. Thank you.
Thanks, embarrassing that I couldn't see that lol