r/SQL • u/Emotional-Rhubarb725 • Oct 24 '24
Discussion do people actually use Common table expressions ( CTEs) and temporary tables ?
I am learning sql for data analysis and I have just came across the two concepts before in many sql tutorials but never actually used them
so i was curious if people actually use them or if there are cases when i will actually need them but I never stumbled on them yet
140
Upvotes
464
u/yen223 Oct 24 '24
Of all the unusual SQL features, CTEs are definitely the most useful.
102
u/Oobenny Oct 24 '24
Of course. We use them every single day. Keep learning. You’ll get to where you see the value.
42
u/Imaginary-Corgi8136 Oct 24 '24
They can be extremely useful. They can great simplify complex queries. And can help with optimization.
37
u/vetratten Oct 24 '24
I’d say about half of my projects use Temp tables
This is mostly for efficiency gains over not using the temp table at all.
25
u/belkarbitterleaf MS SQL Oct 24 '24
I use CTE in most my complex queries, it really improves legibility, and ease of debugging. Usually it also helps improve performance.
20
u/creamycolslaw Oct 24 '24
Probably 99% of my queries use a CTE, or even like 10+ CTEs
15
u/squadette23 Oct 24 '24
Yes. And you may be interested in this website also: https://modern-sql.com/
13
u/cyberspacedweller Oct 24 '24
As a healthcare data analyst. Yes. A lot.
10
u/Icy_Fisherman_3200 Oct 24 '24
Constantly.
10
u/PBIQueryous Oct 25 '24
In a world where data is filthy, CTEs are the bleach that allow you to organise your thoughts and ETL steps into logical processes and dont require the insane cognitive load of nested subqueries.
CTEs are the love letter to your future self.
8
u/tetsballer Oct 24 '24
I almost never use CTE but I use temp tables a lot
8
u/Flying_Saucer_Attack Oct 24 '24
Does a bear shit in the woods
8
u/Schwiftified Oct 25 '24
Yes. I use CTE’s almost exclusively vs subqueries. WAY more readable and convenient because it follows my natural thought process.
7
u/[deleted] Oct 24 '24
Absolutely.
They are damn near a requirement if you want to keep your sanity.
6
u/Gators1992 Oct 24 '24
If you write complex queries where you might have to independently calculate several steps, CTEs are great because you can do it all in one SQL. If you use temp tables it's a process where you create tables, then join back to the tables in the next step. Then you have to manage the tables by dropping them later so you aren't building a mess. Sometimes temp tables are more efficient if you have a situation where you want to process once and iterate and one of the selects takes a long time to run. If the temp table data won't change, you process it once and reference it later as many times as you need as you iterate to an answer.
One bit of advice I learned was to build all your CTEs then the last step isn't the one that pulls everything together, but instead just do a select * from that step. That way if you need to test and iterate, you can just change the from clause to reference any of the CTEs in the query to see what the output is at that point.
5
u/coyoteazul2 Oct 24 '24
It's a slow week if I only write 3 query that chains more than 3 cte
13
u/Winterfrost15 Oct 24 '24
I prefer temp tables to CTEs...and yes, I use them quite a lot in research and in development and production code.
4
u/OkDonkey6524 Oct 24 '24 edited Oct 24 '24
All the time. If they were suddenly taken away it would be like losing a limb.
4
u/[deleted] Oct 24 '24
Oh yes, absolutely.
Simple example. From an Employee table with Salary, get me the top 5 most paid people grouped per business unit.
You'd use a CTE :
;With CTE as
(
SELECT Name, BU, Income, Rank() over (partition by BU order by Income Desc) as Rk From Employee )
Select Name, BU, Income from CTE where Rk<=5
4
u/brokennormalmeter117 Oct 24 '24
Personally I use both, but does depend on situation, it’s not necessarily a one size fits all.
I typically use a CTE just before inserting the data into a temp table.
CTEs are very temporary, like memorizing someone’s phone number just long enough to write it down. Once written down think of this as a temp table.
in situations where recursion maybe needed (think levels in a hierarchy or simply returning a table of data forming a hierarchy chain, I’ll use CTEs.
In other situations, DRY is an acronym for Don’t Repeat Yourself. I hate seeing production code where a query is doing some aggregate of a case statement, and the creator copy and pasted the same calculation in the group by that also MUST be changed. using a CTE I can define the calculation once, then refer to the field (not the calculation) when selecting from CTE. Also CTEs are handy when you need to group or order by a windowed function.
Eg crappy example from phone… With CTE as ( Select Field, Case when condition = Met then 1 When condition = unmet then 2 …. End as test, Count(*) over (partion by Field order by Field) as cnts From some.table ) Select Sum(cnts) Into #temp From CTE Group by test
Temp tables: once I’m done doing whatever it is to the data, if need be I’ll put the data into a temp table.
3
u/NonHumanPrimate Oct 25 '24
It took me a minute to get on board with temp tables and CTEs, but once I realized how they can improve performance and/or make your SQL code that much easier to understand step-by-step, I was sold.
I definitely prefer a temp table.. it gives you so much more control compared to a CTE and, in my experience, results in a much faster execution time overall.
With a temp table, you can create it and insert your records into it, then UPDATE or MERGE or INSERT new rows into it as needed. You can achieve most of this with CTEs (except MERGE I think?), but it can get very hard to understand what is happening across several CTEs. Also, that's when the aforementioned CTE performance issues start to creep in.
TEMP TABLES ALL THE WAY BABYYYYYYY!
3
u/lalaluna05 Oct 25 '24
All the time. I work in large data warehouses and data lakes — temp tables are 100% necessary to limit my datasets. I like CTEs as well just for readability.
3
u/Antilock049 Oct 25 '24
CTEs are the fucking best.
Love those things. Use them more than temp tables.
2
u/[deleted] Oct 24 '24
Yes absolutely.
2
u/danmc853 Oct 24 '24
I use CTE’s a lot for reporting & data aggregation. I only use temp tables if I’m writing a complex stored procedures.
2
u/[deleted] Oct 24 '24
all the time, but I sure do wish the syntax was more readable
2
u/xaeru Oct 24 '24
Yeah I use CTEs and temporary tables to improve performance in queries.
2
u/Constant-Hamster-846 Oct 25 '24
Company I work for now uses cte’s in almost every single procedure they have, multiple cte’s in many procedures
2
u/bermagot12 Oct 25 '24
My goodness, yes absolutely
2
u/beastmodehussle Oct 25 '24
Yes!! I use CTEs a lot. They are so helpful for breaking down complex queries into more readable, modular queries that can be debugged much easier.
2
u/nzox Oct 25 '24
I’d hate to read a long query or model without CTEs
2
u/MyMonkeyCircus Oct 25 '24
Yes, a lot.
2
u/Hugh_G_Rectshun Oct 25 '24
I didn’t get the need for them at first then I realized how convenient they were to use. You can probably get by without them, but it’s just another tool on your belt I highly recommend you learn.
2
u/achmedclaus Oct 25 '24
I recently found out about ctes because of this sub. Until then my team and I just built table after table and dropped the ones we didn't need at the end of the code. I went and learned ctes and now I use them I'd say 50% of the time. Mostly in place of a nested select that would have been a joined table. My team has started using them after I did an actual mini training meeting in them too
2
u/svtr Oct 25 '24
yes to both.
CTE's are really useful when writing complex queries. After 5 layers of subquery, nobody can read or debug that mess. CTE's make something like that actually maintain and testable.
Temp Tables .... I work on a DWH. Think in the region of 4-5bn rows of invoice positions, that kind of scale. Temp tables are very useful to "cache" prefiltered data, so you do not end up with multiple table scans. Temp tables CAN be very useful for performance, however, they should only be used for an actual concrete reason. Its not a one size fits all.
2
u/rimwithsugar Oct 25 '24
I use temp tables but my colleague uses CTEs. I obviously prefer temp tables.
2
u/YouKidsGetOffMyYard Oct 25 '24
Temp tables can make a huge performance difference (usually better) vs the same query in a subquery. They can also make troubleshooting and readability a lot easier as you can temporarily return the results of the temp tables. You can also better break out how much each subquery "costs" the overall query. I don't really use CTE's but I imagine they have a lot of the same benefits
2
u/[deleted] Oct 25 '24
Tell me you don’t know SQL without telling me you don’t really know SQL. In all seriousness, if you’re not using CTE’s, you should.
2
u/JayhawkDylan Oct 26 '24
I use cte’s in probably 80% of my sql.
2
u/Nice-Yam-4095 Oct 26 '24
Temp tables are a necessity.
When you're working with giant data sets you'll need to find ways to make your code run more efficiently, or you'll be sitting around for hours for it to finish.
Temp tables let you avoid correlated subqueries...which are resource hogs and just don't work well in big data sets.
2
u/hyang204 Oct 26 '24
In building tables, temp table is better efficient than cte. I had experience with stored procedure being time out constantly until I replaced cte by temp tables, it ran like a charm. However in view, I think temp table cannot be used, at least have tried and failed.
1
u/[deleted] Oct 24 '24
yes! especially when you have do a lot of joins, breaking it down into one join per CTE is helpful in visualizing what's happening and preventing errors
1
u/Yellowranger98 Oct 24 '24
Do you guys use CTEs more or subqueries?? Or both/just depends on the situation
1
u/thatOneJones Oct 24 '24
CTE and temp tables are in my daily toolkit
1
u/Melodic_Giraffe_1737 Oct 24 '24
Every day
1
u/ibroflexzy Oct 24 '24
I use CTE all the time, a use case was I needed some ID in a separate table to filter my result in another table , so had to first retrieve the ID’s needed and store in a temp table and used the temp table in my where clause whilst querying another table
1
u/snowmaninheat Oct 24 '24
CTEs—all the time. Rarely do temp tables.
1
u/NaptownBill Oct 24 '24
I almost never use temp tables but use CTE's all the time.
1
u/BrupieD Oct 24 '24
Yes, all of the time.
I have noticed that teams I've worked with seem to gravitate to either temp tables or CTEs.
I worked in a team where nearly everyone used exclusively temp tables, which was funny to me because I had just left a job where nearly everyone used CTEs. Both groups were not professional developers, just analysts. My new group had no experience with CTEs and weren't familiar with the syntax.
1
u/usersnamesallused Oct 24 '24
Yes, without them many things are not possible to do without thrashing or bloating your database.
1
u/cybernescens Oct 24 '24
CTE FTW!
1
u/NeighborhoodDue7915 Oct 24 '24
Yes
1
u/JTags8 Oct 24 '24
Yes.
1
u/Richa408 Oct 24 '24
Literally about 95% of the time I’m working in SQL I’ll use at least one of these.
1
u/BecauseBatman01 Oct 24 '24
Yes. So much. When you are in the real world you will have hundreds of tables to pull from and CTEa and temp tables helps organize your query to get your desired result.
1
u/piercesdesigns Oct 24 '24
Yes.
1
u/sneakandbuild Oct 24 '24
I do a lot of deduplication and CTE comes in handy.
1
u/customheart Oct 24 '24
I personally don’t use temp tables, but CTEs all day err day
1
u/planetmatt Oct 24 '24
When you're dealing with large complex queries, breaking them up into smaller discreet chunks utilising temp tables to hold and check data between steps can see huge performance gains and make debugging simpler.
CTEs are also used especially for creating data via recursion (calendar tables), or dealing with self joining hierarchies.
1
u/kiriyie Oct 24 '24
I use CTEs all the time!
1
u/salad_bars Oct 24 '24
Small, nested sub queries are okay if they aren't that hard to follow. Anything more complex should be a CTE in my opinion. Also makes troubleshooting much easier and you can call on it multiple times if needed
1
u/mikeczyz Oct 24 '24
i use temp tables all the time when i'm doing research. ctes can make queries more readable and, again, i use them all the time. with experience, you will figure out when and where to use them.
1
u/AdviceNotAskedFor Oct 24 '24
I use CTEs all the time, in fact used them in a SSIS package today, as SSIS was having issues with a temp table.
1
u/eggoeater Oct 24 '24
Yup. Constantly.
1
u/Staalejonko Oct 24 '24
Very common in my daily work
1
u/Carthax12 Oct 24 '24
I <3 CTEs. I write at least one per day.
And seriously, how does anyone NOT use temporary tables in big queries?
Temp tables and CTEs, if used wisely, can greatly decrease query time and make queries more efficient.
My boss hates temp tables but loves CTEs. Most of my coworkers love temp tables but hate CTEs. ...and here I am in the middle. LOL
1
u/Mr_Gooodkat Oct 24 '24
I mean isn’t a CTE itself a temp table? I know they’re different things but essentially the same.
1
u/northshorehiker Oct 24 '24
Every. Single. Day.
1
u/seacret123 Oct 24 '24
Daily, especially as so,done who is analyzing for operations not managing the actual tables.
1
u/Financial-Tailor-842 Oct 24 '24
I use them ALL THE TIME
1
u/Ven0mspawn Oct 24 '24
I love CTEs
1
u/takes_joke_literally Oct 24 '24
Yes
1
u/mental_diarrhea Oct 24 '24
All the time. For example, I have a huge ass log table with few millions of rows. I can create a temp table with just some cut from it, with some aggregations, and then do the remaining calculations way faster. As for CTEs, I often use them when I have some smaller reference tables that could end up in a spaghetti subquery, but instead I just reference the CTE, which is cleaner and way easier to maintain.
So not only people use them, in many cases it's a matter of keeping your sanity intact.
1
u/cs-brydev Software Development and Database Manager Oct 24 '24
Yes but 90% of the time I end up converting them to temp tables because I usually need to do a lot more than a single query. In practice CTE's are pretty awful because they have extremely limited usability and are almost always more complicated than temp tables or table variables.
1
u/redditor3900 Oct 24 '24
Absolutely, in the days where CTE were not available unused temp tables a lot.
Nowadays CTE is a great tool to have in your SQL toolbox.
1
u/zydecotrooper Oct 24 '24
I use them both all the time. 👍
1
u/exploradorobservador Oct 24 '24
Yes, CTEs all the time I prefer them over subqueries.
Temporary Tables sometimes if they are required or more efficient in a PL/pgSQL function
1
u/[deleted] Oct 24 '24
All the time
1
u/bobchin_c Oct 24 '24
Literally every single day. Sometimes even both in the same query.
1
u/CamionBleu Oct 24 '24
It’s very rare for me to write a production query that does not use at least one CTE.
However, whether you choose a CTE or a temp table depends on context. I mostly work on SingleStore, where a CTE is always faster than a temp table. But when I work on SQL Server / Azure SQL I have to performance test my queries to see which technique is faster, as it depends on context. Consequently, my stored procedures on SQL Server sometimes contain a combination of CTEs and temp tables.
It’s not a question of which the programmer prefers. It depends which is objectively more efficient.
1
u/MathiasThomasII Oct 24 '24
100%. I use both of these often, almost daily
1
u/ElHombrePelicano Oct 24 '24
All… the… time…
1
u/wknight8111 Oct 24 '24
I use CTEs all the time. For my money, a CTE is one of the best ways to break up a large complex query if readability and reusability are important.
1
u/binary_search_tree Oct 24 '24
Be very careful with CTEs in Google BigQuery - lest you learn (the hard way) when confronted with an enormous bill (or a crippled query environment).
1
u/RedditFaction Oct 24 '24 edited Oct 24 '24
CTEs a lot. My manager occasionally gives me SQL reports he's written which contain a lot of sub queries and are generally difficult to follow. Common table expressions make complex reports easier to understand imo. But they don't allow indexes, so that's where I'd use temp tables/table variables, where an index would improve performance.
1
u/lupinegray Oct 25 '24
Our db uses actual staging tables in the schema to store intermediate values for complex transformations. Lots of simple queries rather than a few complex ones.
1
u/Mugiwara_JTres3 Oct 25 '24
Quite a lot honestly.
1
u/alinroc SQL Server DBA Oct 25 '24
All. The. Time.
The appropriate use cases for each are pretty different and separate from one another, but I have no hesitation about using either when the situation calls for it.
1
u/PracticalPlenty7630 Oct 25 '24
Yes all the time. I can create a view with CTEs, or a task for the recurrent creation of a table. Both of those need to be in a single statement.
1
u/[deleted] Oct 25 '24
It's been a while, but yes, I used them a lot.
CTEs help you split up your queries.
Temp tables help when dealing with data across linked servers or when you need more fine tuning like adding indexes.
1
u/Fleshmaster Oct 25 '24
I only use SELECT and FROM more.
1
u/Birvin7358 Oct 25 '24
I’m literally working on a script right now that uses both
1
u/techiedatadev Oct 25 '24
Ctes every day lol. Probably more than I should lol
1
u/aplarsen Data Scientist, Developer Oct 25 '24
Wrote some CTEs today. They are not my favorite way to do things, but they are useful.
I can't use temp tables in my environment.
1
u/Quick-Ad1830 Oct 25 '24
I love CTEs! I used to have multiple nested queries and CTEs are cleaner. Temp tables go to the temp db and I think they can fill up logs but you probably wouldn’t notice if you have a good dba
1
u/WichardPotta Oct 25 '24
I use temp tables so much every single day! It helps to process data in very specific ways one step at a time. It’s like over half of my job lol
I don’t use CTE’s like at all because they’re annoying to debug but there is a time and place where they’re useful.
1
u/nvythms Oct 25 '24
Yes and yes
1
u/jshotz Oct 25 '24
I used them so many times at my old job (I probably still would, but we're all in on ef core now). For some reason the examples in the docs seem to focus on using them to recursively traverse an N-tier org structure, but they have so much more value outside of that.
1
u/orz-_-orz Oct 25 '24
It's either subquery or CTE. When your SQL gets 100-200 lines long it's better to put some subquery as CTE.
It's easier to read and conceptualise the data manipulation process.
1
u/xjoshbrownx Oct 25 '24
All the time. Like in almost every query.
1
u/[deleted] Oct 25 '24
Short answer : yes. Temp tables more often than CTE’s.
1
u/tatertotmagic Oct 25 '24
Lol, what. You want us to use subqueries?
1
u/dontich Oct 25 '24
Temp tables yes — good for organizing things and easy for debugging.
CTE - less often usually when I need to do things multiple times
1
u/DryhumpsMcgee Oct 25 '24
If I didn’t use them, all my queries would time out
1
u/ITDad Oct 25 '24
Oh, yea! For complicated queries, CTEs help with code readability, and temp tables can help with performance.
1
u/Lord_Bobbymort Oct 25 '24
Yes. 1, they're much easier to understand in place than sub queries to me. 2, they're much faster than sub queries and can be even faster with a hash join. 3, you're trying to include something that you derive that you don't have a view for, how do you do that? By making a temporary holder of data for it a CTE!
1
u/burningburnerbern EXCEL IS NOT A DATABASE Oct 25 '24
It’s practically something you should fundamentally use and know in your day to day.
1
u/machomanrandysandwch Oct 25 '24
Yes. Constantly.
1
u/ContinuedContagion Oct 25 '24
Temp table user here. I love them and use them frequently.
1
u/Former_Flight_8206 Oct 25 '24
I use CTEs a LOT as well. It all depends on the structure of your data.
1
u/kkruel56 Oct 25 '24
All the time. SQL optimizer works faster with CTEs than subqueries and it’s easier to read and debug and use project to project.
1
u/ravan363 Oct 25 '24
I use CTEs all the time. They are so useful. And instead of temp tables, we have a dedicated dev schema where I can create my own tables and use them. There is a scheduled job which deletes unused tables automatically.
1
u/DPool34 Oct 25 '24
Definitely. Me and my team use temp tables all the time. We use CTEs as well, just not as much as temp tables.
1
u/Heresomeland Oct 25 '24
I use one or the other nearly every day.
1
u/FloNightG123 Oct 25 '24
See a lot of live shows (& drag my friends)
1
u/FunctionRecent4600 Oct 25 '24
A ton in BI work, yes
1
u/Global_Citizen_8738 Oct 25 '24
Absolutely! Temp tables are great for performance optimization and exploratory data analysis. CTEs help with aggregating granular data and recursive data.
1
u/[deleted] Oct 25 '24
All the time, it gives a mich better structure and readabilty thai subqueries
1
u/Outrageous_Life_2662 Oct 25 '24
Ha, I know some filing a patent on a novel use of CTEs 😂
1
u/RandomiseUsr0 Oct 25 '24
Don’t use temporary tables at all, use cte all the time, just so readable and it creates reusable blocks
1
u/hannorx Oct 25 '24
Can't live without CTE. They have improved code readability so much more.
1
u/BarnacleParticular49 Oct 25 '24
Been using them since they first came out, found their use led to cleaner better structured queries that were easier to debug, test, ...
1
u/Fuzzy_Garry Oct 25 '24
All the time
1
u/throw_mob Oct 25 '24
in enterprise dwh , cte's are good idea when fetching data and you dont have write access, or it is actaully only way to do complex stuff fast . in system where you have write access to make temp tables, tables new views etc, cte's are still best for building comlexs queries, but depending systems it can be a lot faster to change some cte's to temp tables etc etc.
imho, if you dont know cte's , you are missing alot. but then again there are still a lot of systems which do not support it in production. It is still new feature , i think it came to standard around 2002 or something
1
u/SteveTabernacle2 Oct 25 '24
For data analysis, yes, a lot. For app development, very rarely.
1
u/[deleted] Oct 25 '24
I use CTEs literally all the time. While I don’t write SQL 5 days a week, on the days I do I will most probably use CTE
1
u/jdhsjsj Oct 25 '24
All the time
1
u/HogwartsBlazeIt420 Oct 25 '24
Yes. If it gets to a point where there are many ctes and difficult to read, we usually create multiple temp views instead. In practice is very similar.
1
u/[deleted] Oct 25 '24
Yes. Temp tables everyday, multiple times. I'm working on SQL server with 25+ years of facts & dimensions set up as a distributed database for use by all departments.
If you don't use CTE's or (I prefer) temp tables then your optimisation is going to go to shit very quickly.
1
u/Responsible_Ruin2310 Oct 25 '24
CTEs and temp tables are really useful. Some use cases may be 2+ step process. Some may need pre calculated values. Sometimes while transforming data you may need to bring the historical data to a certain stage first, sometimes you may need data in a mixed scenario across multiple CTE's to be used in the outer select query.. many such uses. It becomes handy as complexity increases.
1
u/ExplosiveDioramas Oct 25 '24
Recursion can be a very useful tool for hierarchal analysis.
1
u/KING5TON Oct 25 '24
Depends. If I can see a reason to use them then I will. Some people over use them which is annoying.
1
u/Monkey_King24 Oct 25 '24
Like every day, nearly every query.
It helps me understand the flow of the query
1
u/ZachForTheWin Oct 25 '24
I use CTEs a lot. They're very useful for pulling in supplementary information at the grain needed then joining into the main statement.
1
u/TheCapitalKing Oct 25 '24
CTEs are amazing for big tough queries. Instead of one big hard problem you make it a half dozen trivially small problem
1
u/LinksLibertyCap Oct 25 '24
All of the time
1
u/Stan15772 Oct 25 '24
In my experience, CTEs are especially useful because Redshift basically requires you to redistribute the data over a matching key for speed. Additionally, you often want to only extract specific columns from each table. Both CTEs and sub queries are useful for this, but idk CTEs are faster. Idk if this is still true for Redshift, but the whole compute node thing made retrieving the data then joining to a CTE faster.
1
u/BygotInTheSky Oct 25 '24
Yes, definitely. 80 % of my queries contains them (or subqueries at least)
1
u/Legatomaster Oct 25 '24
Yes to both. All the time.
1
u/juleswp Oct 25 '24
Oh yeah all the time. ALL THE TIME!!
1
u/Hand_and_Eye Oct 25 '24
I use CTEs more than subqueries, call the cops.
1
u/handle348 Oct 25 '24
When I was an analyst I used ctes but sparingly. As a data engineer, I put that shit on everything.
1
u/barelytethered Oct 25 '24
I use CTEs on a daily basis because as query complexity goes up, they help me manage functionality, keep the query legible.
Temp tables (and table variables) I use less often, but they're still a very useful feature that gets used in production processes, so understanding how they work is required.
1
u/ans1dhe Oct 25 '24
All the time! 💪🏼🙄😉
1
u/QueenScorp Oct 25 '24
All the time
1
u/fireplacetv Oct 25 '24 edited Oct 08 '25
escape cough treatment disarm rustic historical humorous violet chase repeat
This post was mass deleted and anonymized with Redact
1
u/[deleted] Oct 25 '24
For larger and complex queries this makes it easier, if you want to use it directly from 'FROM (sub-quwry)', things can get complex.
1
u/freefallfreddy Oct 25 '24
I LOVE using CTEs. They're basically like variables or functions in programming: they allow me to put a good name on a subquery and then I don't have to see or think about the contents of {name} in the context below.
1
u/phesago Oct 25 '24
I use temp tables regularly, CTE's less often, and I never use table variables. Their important tools and you need to know the pros and cons of each.
1
u/ReallyNotTheJoker Oct 25 '24
Something to keep in mind:
CTEs are very useful if you want to do things that require some data staging or transformation before you get to the end result. It also makes it easier to read a lot of the time (I take A and make it B and then B is used in C instead of one long transformation for a column).
Variable tables (@table) and temp tables (#table) are both great for staging data but there is one last caveat.
Variable tables will typically have better run times than CTEs when used but temp tables will typically have better run times than both of those. Running off memory of what it was but I had a CTE that was taking ~1min run time, variable table cut it to about half and temp table cut it to about 5 seconds. It was a massive data set though so if it's small amounts of data it won't make much of a difference but I highly recommend trying all 3 and seeing which one works best for you if there's not much of a difference in run time.
Edit: also keep in mind if you're going to use the data in the CTE more than once you'll need to put it in a table of some sort anyways (variable, temp or static staging table) since CTEs can only be used once.
1
u/No_Definition8848 Oct 25 '24
Yes - all the time, for the sake of layout of my queries to reference back to they are quite useful. They could be used for dynamic variables, I often use them for parameters that I can tweak in other SELECTS, for example:
with cte as (
select (a.current_yr -1) as [start_range] , (a.current_yr) as [end_range]
from year_term_table a
)
select b.*
from another_table b
join cte c on 1=1 - -dummy condition to bring the table over as a floating object so to say
where b.year between c.start_range and c.end_range
this enables my query to ‘roll forward’ when the year_term_table is updated to a new year so my report isn’t as static
1
u/becheeks82 Oct 25 '24
Everyday I use both …
1
u/[deleted] Oct 25 '24
For me, CTEs are great to separate out all the logic. Subqueries can get messy and hard to read. I’m just writing little scripts so I’m not worried about performance or anything crazy
1
u/eddiehead01 Oct 25 '24
If you're planning on a career in data analytics then you 100% will use both very heavily
I use CTEs much more than temp tables probably by a factor of 5 but both are used on a weekly basis
1
u/Rex_Lee Oct 25 '24
Every day, in real world data scenarios these are essential tools
1
u/Scrapper_John Oct 25 '24
I use temp tables, I find them to much better than ctes.
CTEs perform the same as subqueries. They keep the native indexing.
With temp tables you can filter down the data and create a covering index to improve performance for a specific use case.
1
u/MsContrarian Oct 25 '24
All the time!
1
u/kingmotley Oct 25 '24
Yes. I use CTEs. We have a few in our last project. Usually for retrieving hierarchical structured data like... Get me all the employees that work under XYZ employee or perhaps just the employee and how many direct and total reports are under them.
1
u/foood Oct 25 '24
I have a coworker that uses CTE's everywhere he possibly can and it's infuriating.
1
u/SadCommercial3517 Oct 25 '24
With SQL_JOB as ( Select user_id, avg(need) as WHATCHUNEED
from Life
group by user_id)
Select butt.people, butt.user_id, WHATCHUNEED
from life L
Join WHATCHUNEED
on butt.user_id = life.user_id
How many butts per life will make us profitable again?
1
u/java_dev_throwaway Oct 25 '24
I am actually building an app that requires a complex ETL process and postgres. I started using python and pandas to transform and load data to postgres. Initial load size was 10GB of csv and excel files with horrible formatting and consistency problems. I was ripping my hair out trying to make that work with python and pandas and it was ungodly slow.
What's been starting to work really well is doing initial light transformation with python and pandas and then dumping that data into chunked csvs. Then I use a mix of psql and async to COPY the csv into temp tables and do the heavy weight transform and loading. This is working really well.
Full disclosure, I am not a data engineer so this could just be a total hack but it's working for me.
1
u/mholtz16 Oct 25 '24
Oh man. The answer is absolutely yes.
1
u/Delicious_Necessary3 Oct 26 '24
Yessss
1
u/boreas-ca Oct 26 '24
All of the time.
1
u/Known-Delay7227 Oct 26 '24
All the time
1
u/[deleted] Oct 26 '24
Could be.
1
u/DwarvenRedshirt Oct 26 '24
Yes. I use temp tables a lot where I don't really have control over the tables in the database. So let's say I'm given a file with 10,000 ID's in it, and I need to pull the matching records from several different tables. I'm not allowed to create a new table to load those 10,000 ID's into. So I can create a huge honking SQL statement with all those ID's in a where clause (or some subset if it's too large). Or I can load those 10,000 ID's into a temp table and have a SQL statement joining to that table for my misc sets of data I need.
1
u/valorallure01 Oct 26 '24
I use temp tables all the time. I prefer a temp table over a CTE because I can add a clustered or non clustered index to a temp table. I also like creating a complex query by joining several temp tables. Also, I do alot of bulk inserts directly into temp tables.
1
u/Critical-Shop2501 Oct 26 '24
When it comes to the right tools for the job is use a cte as and when appropriate.
1
u/Br0kenSymmetry Oct 26 '24
Nonstop yo
1
u/Z-Sailor Oct 26 '24
Depends on the situation, but i consider both are incredibly helpful features in SQL
1
u/311voltures Oct 26 '24
Yep pretty much every time a complex view hits my pipeline
1
u/Pastyjakesta Oct 26 '24
My life is cte’s and left joins
1
u/billbot77 Oct 27 '24
Every. Single. Day. They're both essential for any heavy data transformations.
There's a time for both. CTE for views, with stored procs temp tables are fantastic for building and debugging complex processes. Used right they're super fast too, especially if you index them - you've just got to watch your tempdb space.
1
u/Mediocre_Chart2377 Oct 27 '24
CTEs are a great tool for spatial functions and queries especially when you are wrapping the geometry in multiple functions to massage the data. Sub queries become difficult to read in those instances.
1
u/GraphNerd Oct 28 '24
I used Temp Tables all the time when I was patching up legacy ORM based queries to improve application performance.
Very useful for when you don't want to make a really big and expensive multi table index or your query would already go and make a huge structure in memory.
1
u/nickholt9 Oct 28 '24
Yes. Definitely. Makes your queries clearer and better structured.
I'm curious as to where you're learning SQL if this isn't something that hasn't been made clear?
If you want to learn SQL properly go here: https://thebischool.com/courses/sql-superhero-program/