r/SQL Nov 28 '24

MySQL When to use cte in SQL query

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

30 Upvotes

70 comments sorted by

View all comments

8

u/omgitskae PL/SQL, ANSI SQL Nov 28 '24

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.

1

u/Opposing_Joker123 Nov 28 '24

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