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

Поділитися
Вставка
  • Опубліковано 26 лис 2024

КОМЕНТАРІ • 45

  • @vpnath75
    @vpnath75 Рік тому +31

    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.

  • @SteveJonesIndeed
    @SteveJonesIndeed Рік тому +4

    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.

  • @oscardiggs246
    @oscardiggs246 Рік тому +3

    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
      @dominiquez5643 2 місяці тому

      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!

  • @dominiquez5643
    @dominiquez5643 2 місяці тому

    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!

  • @cubiclehero1761
    @cubiclehero1761 Рік тому +6

    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?

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

  • @Milhouse77BS
    @Milhouse77BS Рік тому +3

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

  • @davestorm6718
    @davestorm6718 7 місяців тому +1

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

  • @sherifffruitfly
    @sherifffruitfly Рік тому +1

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

  • @llewellynemmanuel9907
    @llewellynemmanuel9907 Рік тому +3

    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

  • @ferasalramli4509
    @ferasalramli4509 Рік тому +1

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

  • @balakumaranajan3966
    @balakumaranajan3966 Рік тому +4

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

    • @jonbaylis2203
      @jonbaylis2203 Рік тому

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

  • @SOURCEOFFICIELLE
    @SOURCEOFFICIELLE Рік тому +1

    The next one should be about the Recursive CTEs

  • @Eysh2009
    @Eysh2009 5 місяців тому

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

  • @jonthornton-dibb2915
    @jonthornton-dibb2915 Рік тому

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

  • @11bftw
    @11bftw Рік тому

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

  • @yveshermann
    @yveshermann Місяць тому

    Nice one my brother

  • @how3recordings
    @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

  • @KillaDBA
    @KillaDBA Рік тому +1

    Nice! What up Patrick! #KillaDBA

  • @MrSparkefrostie
    @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

  • @no_name5002
    @no_name5002 Рік тому

    In what situations would one use CTEs over Temp Tables?

  • @CRWork-p9z
    @CRWork-p9z Рік тому

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

  • @ash1983
    @ash1983 11 місяців тому

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

  • @mehr4195
    @mehr4195 Рік тому

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

  • @pratik2998
    @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
    @janakchetri7731 Рік тому

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

  • @zoranmilokanovic6229
    @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
      @GuyInACube  Рік тому +2

      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.

  • @_indrid_cold_
    @_indrid_cold_ 27 днів тому

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

  • @jamilyashokanova3350
    @jamilyashokanova3350 Рік тому +4

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

  • @myslimeorg
    @myslimeorg Рік тому

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

  • @danielslagter
    @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

  • @carlsagan4802
    @carlsagan4802 Рік тому

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

  • @ashishtiwari87
    @ashishtiwari87 Рік тому +1

    Nice and concise.

  • @ProjectCTE
    @ProjectCTE Рік тому

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

  • @mani2220
    @mani2220 2 місяці тому

    When I use multiple cte's degrading performance

  • @mtavassoti
    @mtavassoti Рік тому +1

    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)

  • @princedave6457
    @princedave6457 16 днів тому

    Too fast couldn't even understand a thing

  • @sai3327
    @sai3327 Рік тому

    Annoying background music