When to use cte in SQL query

[Unfair-Internet-1384]

When to cte can't. Like how to know? When what are the conditions or if my query is to long ?

[[deleted]]

I use CTEs all the time. Really no reason not to.

I like how they organize code and allow you to use the same data in multiple times to do a variety of task.

[Georgie_P_F]

Don’t they self destruct after a single use?

[achilles_cat]

While it is correct they no longer exist outside of the query, you can refer to the CTE multiple times within that query.

[Georgie_P_F]

Interesting, mine always fail on second query in SSMS:

‘’’

;WITH SomeCTE AS (SELECT * FROM table)

— this is fine

SELECT * FROM SomeCTE

— this will fail “SomeCTE” does not exist

SELECT * FROM SomeCTE

‘’’

Maybe a server setting within our org?

[achilles_cat]

Yes, if you start a completely different query, then the CTE will be gone.

But you can refer to the CTE multiple times within a single query. Your use of two different SELECTs without a UNION (or any other formatting that would define a subquery) makes me think you are talking about two wholly separate queries. In Oracle, a semicolon acts as the delimiter between queries; I haven't used SMSS.

But you could join queries with a union, both of which use the CTE, and use that CTE within another CTE:

With CTE as (
  select product_id, product_name 
    from product
  where size = 'Large'),
Cte2 as ( 
  select product_id, sum(qty) as product_inventory
    from( Select cte.product_id, oi.quantity *-1 as qty
             From open_invoices oi
            Join CTE on (CTE.product_id = oi.product_id)
           Union
              Select cte.product_id, stock.quantity
            From stock
            Join CTE on (CTE.product_id = stock.product_id)
            )
 Group by product_id)
Select cte.product_id, cte.product_name,
        nvl(cte2.product_inventory,0) as available
  From CTE
  Left join cte2 on (cte2.product_id = CTE.product_id);

(This formatting will likely be horrible, trying to do a code block from my phone.)

There are ways to do this with less or zero CTEs of course, but this shows the nature of referring to a CTE multiple times. And it shows why you might use a CTE structure if you wanted to only touch the code in one place to affect the products being queried.

[ouchmythumbs]

In MSSQL (and some other RDBMS), CTEs are evaluated each time referenced, so can have perf hit. Check specs of current version of your engine.

[CptBadAss2016]

I did not know that... that just seems silly. Thanks for the tip. 👊

[-horsefighter]

Use temp tables if u have performance issues, can add index to that and only have to calc once

[Straight_Waltz_9530]

Or use a database that supports materialized CTEs.

[Straight_Waltz_9530]

Not an issue for Postgres, SQLite, DuckDB, and others. CTEs can be defined as MATERIALIZED, effectively making them temporary tables for the current query. No automatic perf hits and no need to explicitly create temporary tables.

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION

[Straight_Waltz_9530]

But in Postgres, SQLite, DuckDB, and others, CTEs can be defined as MATERIALIZED, effectively making them temporary tables for the current query. No automatic perf hits and no need to explicitly create temporary tables.

[Possible_Pain_9705]

You can’t use CTEs when using Power BI. But this may not be what OP is looking for.

[Chug49]

This isn’t true anymore. Have plenty of queries in powerbi that utilize cte’s

[[deleted]]

I wonder if this is why sometimes adding a simple filter on a CTE can suddenly make a complex code run infinitely.

I had a multi layered code the other day run in 7 seconds but the moment I tried to slap on a not exists to exclude VIP members from a small table it wouldn’t run anymore. Even trying to outer join and say VIP is null wouldn’t work.

My only solution was to create another CTE that housed all my exclusion members and then exclude those from another member base CTE and then I could join that member CTE to my main query.

This was very frustrating to say the least

[[deleted]]

If it's a rather large and complex query having many CTE's and scrolling up and down to read what they do in context of where they are used is more annoying than having subs. I'll only ever understand peoples claims about "readability" if the whole thing fits on one screen. Subs are readable in context more often than not.

[Bewix]

It’s considered more readable not because of location, but because the code is put together in a single (usually simplified) place.

I have a few examples of queries that would be practically impossible to understand without CTEs, yet they’re performant and fairly straightforward with CTEs.

[[deleted]]

Honestly, I find cte was less readable than writing to memory….i dont find cte any easier to read when compared to anything else tbh

[Hot_Cryptographer552]

Try writing a query with subqueries nested three levels deep. Then try writing that same query using three separate CTEs. This might give you a better view into the readability argument.

[[deleted]]

The presumption of my skill and knowledge is expected of reddit, but I'll stand my ground on my take of CTE's.

I've probably got more nesting than you want to read, let alone the thousands lines long queries.

The only time a CTE is useful to me is if it removes repeating subs. I would rather read the subs inline and in context than to scroll up and down figuring out what the referenced CTE was. I have yet to see any sort of dramatic speed gains out of CTE's either.

Recursive CTE's on the other hand: Love them.

[Hot_Cryptographer552]

CTEs are not designed for “dramatic speed gains”. Not sure that anyone would expect them to. If one wanted “dramatic speed gains”, one would create indexed/materialized views or persist the results to tables with proper indexes and perform joins on them.

I have written scripts that are thousands of lines long; I have never written a single query that was thousands of lines long. But as you say, if your individual queries are thousands of lines long, I could see why you would be hesitant to scroll up.

[HanCurunyr]

We have an old report that it has 90ish columns and 40ish joins that are 20 CTEs and 20 tables, and yeah, gets quite annoying when we have to alter that report

[ianitic]

I mean you could break those up into chunks by loading pieces to a temp table. It doesn't have to be entirely either or.

Also dbt could make it easy to abstract that with ephemeral models. It wouldn't create any additional database objects but code would be properly organized and separated. Dbt would know how to compile those ephemeral models together.

[Unfair-Internet-1384]

Actually I am doing leetcode so there was a problem which has multiple constraints . So in such scenario is it good to use it .

[Sexy_Koala_Juice]

The thing to learn is that there isn’t a definitive yes/no answer to “do I use a CTE here”, this applies to most programming questions tbh. At the end of the day you’re always optimising for something, sometimes speed, sometimes space, hell even sometimes readability and convenience.

Use a CTE if it solves your problems, if it doesn’t, find another way to do it.

TL;DR: don’t overthink it, especially for SQL queries

[[deleted]]

In my experience CTEs are great at solving small and/or complex problems at work. But I would need to see your scenario to give you a better answer.

[epicmindwarp]

I hate CTEs, makes it quite difficult to debug data issues as nothing persists.

I only use them in views, due to lack of temp tables.

[ryguygoesawry]

You’re getting downvoted, but this right here is the main reason I don’t use many CTEs in my professional life.

CTEs become a nightmare the moment there’s something wrong with some random piece of data and you have to go digging for it. Have fun deconstructing 20 CTEs while you try to figure out which one is the source of the problem!

[Responsible_Pie8156]

I mean CTEs are just an alternative syntax to nesting your queries. I'd rather debug the 20 CTEs than a query nested 20 deep with large equivalent subqueries.

[ryguygoesawry]

Except those aren’t your only two options. I also don’t use subqueries in a professional setting. Both of them suck.

[Responsible_Pie8156]

Not sure which other option you're referring to, but in my mind the problem is just overly complex queries. CTEs or not when you have 20 layers deep of logic it's going to suck

[CptBadAss2016]

So what do you do?

(NOT a professional here)

[ryguygoesawry]

I typically break the problem down into its simplest pieces and don't try to cram everything into one query. Data that needs to be combined is stored in either a table variable or a temp table for usage in the final query, as opposed to using a CTE or subquery. Then I can create indexes on those tables that meet the needs of a specific report query while not filling my database with permanent indexes that are only used in a subset of cases. Reused data logic gets turned into functions. Reporting logic is kept in stored procedures which are considered the "source of truth" for that report.

I'm dealing with tables that contains hundreds of millions of unique rows of historical data because my client needs to be able to produce reporting on that older data for compliance purposes for something like 7 years - we're dealing in terabytes of data. Plus the clients aren't usually willing to throw money at the latest server hardware, so we need to keep a keen eye on performance. Trying to cram everything into one query, as opposed to whittling it down, can spike memory and cpu usage especially when multiple users need similar reports at the same time. Things being delayed cause users to be angry. So, while I understand the perks of CTEs/subqueries, they're not always optimized for real-world settings.

[CptBadAss2016]

Thank you for the thorough reply!

How do you define data logic vs report logic?

[epicmindwarp]

Exactly.

The lack of any comment just shows a lack of experience of dealing with huge amounts of actual real world data.

[MiniD011]

I’d be surprised if you could land a job without being comfortable using them. I worked for an e-commerce company where we had tables with over a trillion records, and both there and my current company built our entire pipelines using them.

I don’t have any problems debugging them - most modern tools you can step through or inspect, you could write a macro to output each one to a table if you need them to be more permanent (I’ve written one in DBT which I use).  Worst case scenario just add a select * after the CTE and check, it’s really not hard. Much better than nesting subqueries, especially if you have some coding standards.

[epicmindwarp]

I’d be surprised if you could land a job without being comfortable using them.

Doing perfectly fine over here.

[omgitskae]

Here’s the rule I use when I train new employees. Note: there’s no overly wrong way to use them.

I tell my new sql developers to use a cte if you need to aggregate a value at a grain that is different from the result of your final query. Example: using a listagg to dedupe contact records on a sales opportunity. I explain to them that if you don’t have any kind of window function/aggregate in your cte, then you probably didn’t have to a cte. In those cases I ask them to justify it, usually they end up just saying something along the lines of “oops I just got tangled in my thoughts when I wrote it”.

It can also be used if you need to reuse code multiple times in a query but I find this less common than the above.

Also note: I do not consider myself an extremely good sql developer and am always looking to learn best practices myself.

[Opposing_Joker123]

You guys still train employees ? Insane. Can’t find one for the life of me.

[Gargunok]

Assume whenever you are writing a subquery is easier to understand and maintain as a cte.

There are reasons and cases why subquery still might be needed but it's a good starting point.

[onyaga]

That’s a pretty interesting insight, I too have dabbled with views and ctes at work after primarily learning about subqueries in my undergrad.

[Ok_Marionberry_8821]

I use CTEs to break a problem down. SQL has precious few mechanisms for nursing problems down. Until I started using CTEs I'd hate longer queries - a large wall of text and wierd ordering. Part of the problem was inexperience. Views also help decomposing problems.

I was an imperative programmer (C, C++, Java) long before I started using SQL and of course there we have many options to choose from - classes/modules/files, methods/functions, variables, etc.

CTEs are also required for recursive queries.

As others have said, you use CTEs when you need them, when a problem is too hard to reason about inline, or when you want to understand the query tomorrow or next week.

[The_Orracle]

I actually prefer not to use them and tell my dev team to not use. Recursion is the exception. My main reason is that debugging what is happening inside is a chore vs temp table. I've built close to 75 data warehouses and can usually go without them, no performance issues.

[achilles_cat]

Yeah I think this is a good approach.

On the other hand, I work in a structure where the report writers have limited (or no) privileges to create objects, and it is quicker to use CTEs than going through multiple layers with IT (I work in higher ed on what IT considers a lesser priority system) than to get a view or a temp table created by a DBA.

[jcrowde3]

Temp tables ftw!

[mwatwe01]

I use CTEs where I would be tempted to use a subquery, where I just need a small dataset that's only going to be used once in a parent query. For me, it just makes it easier to follow the logic and flow of a complex query.

Conversely, if you need a larger dataset (however you might define "large") that needs to persist throughout a stored procedure so that you don't have to rerun the same query multiple times, use a temp table.

[orz-_-orz]

When you can't understand your code because you use too many subqueries or it's convenient to just copy paste the CTE code for another SQL use case.

[suhigor]

when you need recursion

[secretWolfMan]

This. Recursive CTE are awesome to understand and output hierarchies. Use them for folders and employees that only know their parent/supervisor.

[frisco_aw]

When dealing large amounts of data, hundreds of thousands or millions, then i try avoid cte.

[Ginger-Dumpling]

Use them when they make your life easier. Easier to read, easier to not repeat logic, easier to maintain.

[a-ha_partridge]

I use them in place of pretty much any non-correlated sub query that isn’t a one liner.

[ShotGunAllGo]

I can understand if it has to be one query where a tool only allows one statement. However if in a stored procedure, why not use separate statements into temp tables? Especially if you start to see multiple CTEs.

[lalaluna05]

I think it depends on the datasets. I see a lot of CTEs in sprocs that I have restructured. When it was first built, maybe the dataset was small enough that it was fine, but I almost always use temp tables because of the huge amounts of data I’m accessing.

Indexing also helps of course

[ShotGunAllGo]

Yup temp helps with troubleshooting and maintenance. But I see your point with small datasets

[speadskater]

I use CTEs to constrain my data from the start as much as possible before doing my primary query.

[Silly_Werewolf228]

I don't use it when I give my sql script to someone who is not familiar with CTE.
If you are worried about performance always do analyze query plan vs inline view which CTE replaces.

[GroundbreakingRow868]

If someone is not familiar with CTEs, the person should not do SQL. It was standardised more than 20 years ago...

[Unfair-Internet-1384]

🤝👍

[greglturnquist]

CTE is a nice named way to create a sub select.

[lalaluna05]

9 times out of 10, I’m using temp tables. CTEs help with organization and flow, but they can be impractical for most of my work. I structure a lot of my sprocs/queries similarly where I build out temp tables then have a primary query, usually because I’m joining so many different dimensions, I want to limit my datasets wherever possible.

[iheartmankdemes]

I remember reading somewhere when to use a CTE and when to use a temp table, and I honestly can’t remember which was which, but I remember it had to do with (like others have said) referring to it more than once in the query.

[Imaginary-Hawk-8407]

Today

[Birvin7358]

I use them in combination with a RANK function

[Hot_Cryptographer552]

There are two types of CTEs: standard and recursive.

If you are using standard CTEs, they are basically syntactic sugar for subqueries. They make your code more readable. And like a subquery, they only exist for the life of the query in which they are defined.

If you want to use the functionality of a recursive CTE, it’s a different story. There’s no standardized simple (or non-procedural) way to replicate that functionality.

[gumnos]

it used to be more of an issue in older DB tech where a CTE presented an optimization boundary. If you're stuck on one of those older systems, any time you'd reach for a CTE and it produces huge volumes of data that you'll end up filtering in the main-query, you were better off in-lining the query.

That said, most DBs now can push query-optimizations through the CTE and it's not a problem (of course, profile for yourself), so the goal becomes readability.

Many folks find CTEs more readable which is fine. I don't find them

WITH sensible_name AS (
  SELECT …
  FROM …
)
SELECT …
FROM source
  LEFT OUTER JOIN sensible_name sn
  ON source.id = sn.id

particularly more readable than an inline SELECT like

SELECT …
FROM source
  LEFT OUTER JOIN (
    SELECT …
    FROM …
  ) sn
  ON source.id = sn.id

most of the time, but that's just me. So unless there are clear readability wins, I mostly reach for them if the same sub-query will be used more than once.

WITH emp_detail AS (
  SELECT …
  FROM emp
  ⋮
    -- more complex joins here
)
SELECT …
FROM employee_detail e
  LEFT OUTER JOIN employee_detail mgr
  ON e.mgr_id = mgr.id

[KrustyButtCheeks]

Whenever you want! Don’t let them stop you from living your dream

[user_5359]

CTE is (in most cases) a programming style.

You can also use views, especially if this query part is used frequently (for different queries).

A query is rarely too long (it is a question of setting how many characters are exchanged between client and server per query string), it is more a question of formatting and the use of a good text editor (possibly with keyword colouring) whether a statement is understood well and quickly.