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

Подписаться 455 тыс.

Просмотров 34 тыс.

50% 1

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

Наука

Опубликовано:

5 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 42

@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.

@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.

@dominiquez5643 11 дней назад

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!

@davestorm6718 5 месяцев назад

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

@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 11 дней назад

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!

@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?

@ferasalramli4509 Год назад

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

@Milhouse77BS Год назад

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

@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

@Eysh2009 3 месяца назад

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

@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!

@SOURCEOFFICIELLE Год назад

The next one should be about the Recursive CTEs

@jonthornton-dibb2915 Год назад

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

@CRWork-p9z 11 месяцев назад

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

@no_name5002 Год назад

In what situations would one use CTEs over Temp Tables?

@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

@janakchetri7731 Год назад

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

@11bftw Год назад

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

@KillaDBA Год назад

Nice! What up Patrick! #KillaDBA

@GuyInACube Год назад

Yooo!

@ash1983 9 месяцев назад

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

@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

@ashishtiwari87 Год назад

Nice and concise.

@GuyInACube Год назад

Appreciate that! 👊

@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

@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.

@carlsagan4802 Год назад

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

@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

@ProjectCTE Год назад

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

@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)