What is a Common Table Expression (CTE) and how do you use them?
Vložit
- čas přidán 22. 08. 2024
- We've had some questions about what a Common Table Expression (CTE) is. Patrick describes what they are and how you can use them in SQL Server or Azure Synapse Analytics.
WITH common_table_expression (Transact-SQL)
learn.microsof...
Sample Scripts - github.com/guy...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#AzureSynapse #AzureSQL #GuyInACube
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.
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.
That last example was nice. I've been doing a lot of this the hard way! Thanks!
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.
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?
Thanks for the quick and perfectly explained video! Now I got it!
Thanks for starting SQL Tutorial videos , hope you create a playlist.
Always thought of CTEs as like Power Query in that you can break SQL problem into smaller parts.
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
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.
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
Just found your channel, super helpful information, thank you 👍
Thank you, I learning some stuff from that, but your very fast...
Nice and concise.
Appreciate that! 👊
Nice! What up Patrick! #KillaDBA
Yooo!
I understand how they work, but I rarely know when to use them and why it's better than another method
Didn't like this tutorial, very fast paced considering that this is for someone who is not familiar with cte.
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! :-(
Bring about new Window dax function video and along with that showcase it in SQL too. Just a suggestion.
hey, what happened. where did you go from power bi to sql. there is so much in power bi
Oooh...
Well this might get confusing...
Should have checked if it stands for anything first...
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
In what situations would one use CTEs over Temp Tables?
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)
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 am using self referencing CTEs for multi level Bill Of Materials.
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
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
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.
How to replicate a CTE in SQL Server but do it in DAX in power bi?
Annoying background music