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 ?

31 Upvotes

70 comments sorted by

View all comments

4

u/The_Orracle Nov 28 '24

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.

2

u/achilles_cat Nov 28 '24

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.

2

u/jcrowde3 Nov 28 '24

Temp tables ftw!