CTEs are awesome and are essential for any advanced reporting. Would be interested to see a video comparing CTEs with temp-tables too and when one might be better than the other.
I've used them since 2009 and they are very helpful in keeping SQL tidy. If you have especially complex queries with sub queries and multiple joins to the one table, you can use CTEs to separate out the bits of SQL and test them individually. Oracle does them as well so it's not as if they are SQL server specific. I find most other developers though do NOT use them.
Thanks for the explanation. Coworker told me to use a CTE and then gave me a confusing explanation. This made sense and I feel like I’ve got my head around the concept now.
I've seen and felt that: if you can't explain to a user what a code does, you are a shitty person holding back knowledge or you don't know what you are doing. The best developers and mentors I had, never turned their explanations into "black boxes"...glad we found knowledge here!
A fair warning though; You usually can't use CTE's within native queries in Power BI. Not for direct query, not for tables with incremental refresh, etc. The reason why, I guess, is that PBI uses CTE's to wrap your native query for it to function. Is this the reason Patrick?
CTE little magic box, question though, I want to use a CTE within a CTE to filter out the nulls in a column derived from a case statement. Right now I am importing into my dataflow and removing nulls but it breaks query folding
I have been using SQL for the past 10 years as a GIS engineer and I have never came across CTE:s and still can not understand them and how to use them in practice! :-(
Patrick can you please make a video on Direct query.. I'm having a hard time getting stuff to work there. Things are working well through Import. But we want near real time hence trying direct query
I'm just wondering... Any particular reason why to use coalesce instead of isnull while working with two parameters? Similar question... why to use full join description (full outer join, left outer join, right outer join, inner join) instead of short form (full join, left join, right join, join)? Many thnx in advance, Z
Easy one first, there is no difference between LEFT JOIN and LEFT OUTER JOIN or any variation. When trying we try to be as explicit as possible with the Syntax. In regard to COALESCE and ISNULL with two values, we haven't seen much of a performance difference. However, COALESCE uses data type precedence to the output data type. You can read more here: learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16.
Your content is nice and useful. Thanks. But how much is a silent click mouse/keyboard? Do want us to contribute towards buying one for you? 🙂 the clicky sound is very distracting and unpleasant when watching on phone (tiny speakers)
CTEs are awesome and are essential for any advanced reporting. Would be interested to see a video comparing CTEs with temp-tables too and when one might be better than the other.
I second this.
I've used them since 2009 and they are very helpful in keeping SQL tidy. If you have especially complex queries with sub queries and multiple joins to the one table, you can use CTEs to separate out the bits of SQL and test them individually. Oracle does them as well so it's not as if they are SQL server specific. I find most other developers though do NOT use them.
Thanks for the explanation. Coworker told me to use a CTE and then gave me a confusing explanation. This made sense and I feel like I’ve got my head around the concept now.
I've seen and felt that: if you can't explain to a user what a code does, you are a shitty person holding back knowledge or you don't know what you are doing. The best developers and mentors I had, never turned their explanations into "black boxes"...glad we found knowledge here!
Master class!! Cube guy!! Youi've proven that you don't need to be cryptic to be smart and explain in leyman terms! amazing job...love your style sir!
A fair warning though; You usually can't use CTE's within native queries in Power BI. Not for direct query, not for tables with incremental refresh, etc. The reason why, I guess, is that PBI uses CTE's to wrap your native query for it to function. Is this the reason Patrick?
Enjoyed, Thanks. I use them to build up logic quickly, rather than trying to be trendy with complex SQL. Often reference a CTE from the next CTE.
Always thought of CTEs as like Power Query in that you can break SQL problem into smaller parts.
That last example was nice. I've been doing a lot of this the hard way! Thanks!
the final example was good: cte's are used all the time, and the most typical use is to manufacture a granularity match.
BAM!
Great explanation. I've used CTE's before for taking raw tables and creating a cleaner version and then referencing that cte for other transformations
Thanks for starting SQL Tutorial videos , hope you create a playlist.
CTE's, Views and Temps which one is more efficient? when to use what, a comparison would be really helpful.
Great question, as I'm always doing tmp tables instead of CTEs.
The next one should be about the Recursive CTEs
Thanks for the quick and perfectly explained video! Now I got it!
Just found your channel, super helpful information, thank you 👍
I understand how they work, but I rarely know when to use them and why it's better than another method
Nice one my brother
Use them all the time for complex queries, but notice PowerBI won't allow them in direct query, which is a bit of a pain
Nice! What up Patrick! #KillaDBA
Yooo!
CTE little magic box, question though, I want to use a CTE within a CTE to filter out the nulls in a column derived from a case statement.
Right now I am importing into my dataflow and removing nulls but it breaks query folding
In what situations would one use CTEs over Temp Tables?
Thank you, I learning some stuff from that, but your very fast...
I have been using SQL for the past 10 years as a GIS engineer and I have never came across CTE:s and still can not understand them and how to use them in practice! :-(
hey, what happened. where did you go from power bi to sql. there is so much in power bi
Patrick can you please make a video on Direct query.. I'm having a hard time getting stuff to work there. Things are working well through Import. But we want near real time hence trying direct query
Bring about new Window dax function video and along with that showcase it in SQL too. Just a suggestion.
I'm just wondering... Any particular reason why to use coalesce instead of isnull while working with two parameters? Similar question... why to use full join description (full outer join, left outer join, right outer join, inner join) instead of short form (full join, left join, right join, join)? Many thnx in advance, Z
Easy one first, there is no difference between LEFT JOIN and LEFT OUTER JOIN or any variation. When trying we try to be as explicit as possible with the Syntax.
In regard to COALESCE and ISNULL with two values, we haven't seen much of a performance difference. However, COALESCE uses data type precedence to the output data type. You can read more here: learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16.
You don’t think that this was just a smidge on the too fast side?
Didn't like this tutorial, very fast paced considering that this is for someone who is not familiar with cte.
I am using self referencing CTEs for multi level Bill Of Materials.
whats the difference between CTE to a temp table?
Im using #temp to create the same, which I can join and do the same manipulations
How to replicate a CTE in SQL Server but do it in DAX in power bi?
Nice and concise.
Appreciate that! 👊
Oooh...
Well this might get confusing...
Should have checked if it stands for anything first...
When I use multiple cte's degrading performance
Your content is nice and useful. Thanks. But how much is a silent click mouse/keyboard? Do want us to contribute towards buying one for you? 🙂 the clicky sound is very distracting and unpleasant when watching on phone (tiny speakers)
Too fast couldn't even understand a thing
Annoying background music