What is a Common Table Expression (CTE) and how do you use them?

[@vpnath75]

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.

[@jonbaylis2203]

I second this.

[@IsaacJrDevelops]

From my understanding, CTE's lifecycle ends once the query is executed and can not be used throughout the scripts execution session. So, it's best to use temp tables when applying multiple queries on the data set. I have found temp tables to be more performant when joining large datasets.

[@oscardiggs246]

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.

[@dominiquez5643]

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!

[@SteveJonesIndeed]

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.

[@davestorm6718]

That last example was nice. I've been doing a lot of this the hard way! Thanks!

[@dominiquez5643]

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!

[@ferasalramli4509]

Thanks for starting SQL Tutorial videos , hope you create a playlist.

[@Eysh2009]

Thanks for the quick and perfectly explained video! Now I got it!

[@Milhouse77BS]

Always thought of CTEs as like Power Query in that you can break SQL problem into smaller parts.

[@yveshermann]

Nice one my brother

[@llewellynemmanuel9907]

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

[@cubiclehero1761]

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?

[@balakumaranajan3966]

CTE's, Views and Temps which one is more efficient? when to use what, a comparison would be really helpful.

[@jonbaylis2203]

Great question, as I'm always doing tmp tables instead of CTEs.

[@clairerovic]

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.

[@sherifffruitfly]

the final example was good: cte's are used all the time, and the most typical use is to manufacture a granularity match.

[@GuyInACube]

BAM!

[@MrSparkefrostie]

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

[@jonthornton-dibb2915]

Just found your channel, super helpful information, thank you 👍

[@SOURCEOFFICIELLE]

The next one should be about the Recursive CTEs

[@carlsagan4802]

How to replicate a CTE in SQL Server but do it in DAX in power bi?

[@mehr4195]

hey, what happened. where did you go from power bi to sql. there is so much in power bi

[@zoranmilokanovic6229]

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

[@GuyInACube]

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.

[@KillaDBA]

Nice! What up Patrick! #KillaDBA

[@GuyInACube]

Yooo!

[@11bftw]

I understand how they work, but I rarely know when to use them and why it's better than another method

[@CRWork-p9z]

Thank you, I learning some stuff from that, but your very fast...

[@danielslagter]

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

[@jennielear]

@@danielslagter depends on your dataset. I happily use both but generally find CTE to be better performing - during a build temp tables can help if you want to examine / validate individual elements of the build without having to rerun the full query

[@pratik2998]

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

[@ashishtiwari87]

Nice and concise.

[@GuyInACube]

Appreciate that! 👊

[@janakchetri7731]

Bring about new Window dax function video and along with that showcase it in SQL too. Just a suggestion.

[@ash1983]

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! :-(

[@how3recordings]

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

[@jamilyashokanova3350]

Didn't like this tutorial, very fast paced considering that this is for someone who is not familiar with cte.

[@_indrid_cold_]

You don’t think that this was just a smidge on the too fast side?

[@myslimeorg]

I am using self referencing CTEs for multi level Bill Of Materials.

[@mtavassoti]

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)

[@ProjectCTE]

Oooh... Well this might get confusing... Should have checked if it stands for anything first...

[@mani2220]

When I use multiple cte's degrading performance

[@princedave6457]

Too fast couldn't even understand a thing

[@sai3327]

Annoying background music