Just a small correction, using the with statement makes indeed things easier to read and debug but the program won't be any faster. It's just a thing you should do to help other humans understand your code.
Thanks for the video. I was looking for more info about caching, but noticed (after some reading) that the caching doesn't happen because of the CTE, but instead, it's decided arbitrarily by Postgres. So for instance, either doing subqueries or CTEs might result in the same performance in the end, even though CTEs makes code more readable.
3:29 is the main purpose of CTE : OPTIMISATION An EXPLAIN of both syntaxe would have revealed it. Over code simplification, when you need to interprete subqueries results CTEs are way faster than subqueries since de BDD engine build the CTE result (in bdd server memory not cloud lol) ONCE en start with it for interpretation.
I've been studying SQL now for about 6 months....I think the CTEs are really a good place to start but the more knowledge you gain the more you tend to move away from them and use subqueries
Congrats on your learning Larry. Like all tools, CTEs, subqueries and cursors all have their appropriate use cases. While it is true that you could program almost everything using subqueries, other methods (such as CTEs) could be used as well to make your code easier to follow for those coming behind you to maintain it. Like a painting, everyone has their own style and choice of tools 😉
hello there, god bless your efforts..I have a simple enquiry as new sql learner. How are CTEs different from temporary tables? Thanks for taking care of this.
A temporary table will exist during the entire duration of the session whereas a CTE is a "named result set" that exists for the duration of the statement immediately following it. If you would like to persist the results of the CTE, then use the CTE to populate a temporary table for processing later. Hope this clears things up
Excellent explanation . How dose work when you're working with 10 tables and 5 has millions of records in them? Is this viable considering this is all done in memory? Thanks
In most database systems, the 'name' of the CTE simply references an internal temporary table that is invisible to you (unless you select from it). So, in effect, the results of the CTE will create a temporary table behind the scenes in order to hold the results of the CTE. It is deleted automatically when you go out of scope of the CTE.
@@paradix847 scope, memory and performance issues. if a temp table isn’t dropped and the client makes another request to the server, the temp table that was not disposed correctly can throw ambiguous errors
Thank you! This video helped me understand CTEs a little bit better.
Dude! thank you for taking the time to break down cte's simplified (subquery >> cte). Bro, you rock!!! 😎🤟
You explained it so well and fast I'm now fully prepared for what may come
Just a small correction, using the with statement makes indeed things easier to read and debug but the program won't be any faster. It's just a thing you should do to help other humans understand your code.
U ROCK. im going through your channel and god you couldnt get less helpful. thanks man
Simple and easy to understand explanation. It really made understanding the WITH statement so simpler. Thanks much.
The way you describe what cte's are made all the difference. It's so simple when you know yhe why for the how.
This is clearer than my textbook explanation lol thanks for the video!
Thank you for the video, very clear, I like the format, it's straightforward and very well explained!
Thanks, mate! Your explanation is very easy to understand.
Best explanation ever!! 👍
This was excellent - thank you.
Very helpful..... Love from India
Hi Nathan, thanks for explaining me CTE Objects. It's know very clear to me. Appriciate
Great explanation
Thanks a lot for simple explanation
Thank you. It was wonderful explanation
Great video! This helped me run a complex query at work. Thanks 😀
You are a sensation! Please, keep going :)
Thanks for the video. I was looking for more info about caching, but noticed (after some reading) that the caching doesn't happen because of the CTE, but instead, it's decided arbitrarily by Postgres. So for instance, either doing subqueries or CTEs might result in the same performance in the end, even though CTEs makes code more readable.
Your video helped me a lot, thank You my friend!
Nice man! subscribing....
Greetings from Colombia!
I don't understand what the ctdorders have inside...
Thank you for this very useful video!
Thanks! Liked, Commented and Subscribed!
Thanks, easy and straight forward!
conceptual and easy way to describe
Finally!! Whoa! Thanks man.
great example and explanation!
in the 3rd example nothing changed when you joined another cte. So why did you join them?
How can I union two cte together, there is one sum on a budget, column are exactly the same I have group by , send me video please
Good explanation. God bless you. AMEN.
what is the difference between cte:s and temp tables? temp tables are only stored temporarly and cant be accessed as an cte?
Sweet and simple: thanks!
That was super helpful!
why did you use "using" in your join statement and not "on"?
thanks bro, you saved a lot of time
ahhh That makes it much clearer. Thank you!
Great video!
thankyou sir, it really helped me.
3:29 is the main purpose of CTE : OPTIMISATION
An EXPLAIN of both syntaxe would have revealed it.
Over code simplification, when you need to interprete subqueries results CTEs are way faster than subqueries since de BDD engine build the CTE result (in bdd server memory not cloud lol) ONCE en start with it for interpretation.
Super helpful - thanks a bunch!
Very clear, Thank You Sir !
Nice video, thank you
Can I pass list of acctnumbers in with and it will traverse through each one of them to fetch acctid from diff table
Thanks! That was very helpful :)
Super sir 👍👍👍
clear and concise. ty
This is great!
I've been studying SQL now for about 6 months....I think the CTEs are really a good place to start but the more knowledge you gain the more you tend to move away from them and use subqueries
Congrats on your learning Larry. Like all tools, CTEs, subqueries and cursors all have their appropriate use cases. While it is true that you could program almost everything using subqueries, other methods (such as CTEs) could be used as well to make your code easier to follow for those coming behind you to maintain it. Like a painting, everyone has their own style and choice of tools 😉
Thankful for your videos - super helpful! Do you have to use a GROUP BY clause when using CTE's?
It's not a requirement but you certainly could
Hvala puno!
Why the pc screen is so blurred?
hello there, god bless your efforts..I have a simple enquiry as new sql learner.
How are CTEs different from temporary tables?
Thanks for taking care of this.
A temporary table will exist during the entire duration of the session whereas a CTE is a "named result set" that exists for the duration of the statement immediately following it. If you would like to persist the results of the CTE, then use the CTE to populate a temporary table for processing later. Hope this clears things up
Thank you
hats off!!
Definitely helpful! Thank you for this. Can you also do a video on recursive CTEs?
i know Im kinda off topic but does anyone know a good site to watch new movies online?
@Anthony Edison I use FlixZone. Just google for it =)
@Kameron Deacon yea, I've been watching on FlixZone for since april myself =)
@Kameron Deacon thanks, I signed up and it seems to work =) I really appreciate it !
@Anthony Edison happy to help xD
You have a crypto channel too?! I have seen your YF vids
Thanks!
Excellent explanation . How dose work when you're working with 10 tables and 5 has millions of records in them? Is this viable considering this is all done in memory?
Thanks
In most database systems, the 'name' of the CTE simply references an internal temporary table that is invisible to you (unless you select from it). So, in effect, the results of the CTE will create a temporary table behind the scenes in order to hold the results of the CTE. It is deleted automatically when you go out of scope of the CTE.
I didn't think CTE's are stored anywhere... temp tables are
and, boom!
why no disclaimer to be careful/avoid using temp tables
Could you elaborate on why one should be careful using temp tables?
@@paradix847 scope, memory and performance issues. if a temp table isn’t dropped and the client makes another request to the server, the temp table that was not disposed correctly can throw ambiguous errors
@@christiangrundemann9843 Hey that's good to have in mind. Thanks for the explanation.
Cte isn't tepm table. It works like a temp table. That's what I know. So no disclaimer.
I don't see the point of using WITH for a single query. If you would use the same WITH for multiple queries, I can understand how it helps.