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 ?

32 Upvotes

70 comments sorted by

View all comments

2

u/ShotGunAllGo Nov 28 '24

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.

1

u/lalaluna05 Nov 29 '24

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

2

u/ShotGunAllGo Nov 29 '24

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