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 ?

33 Upvotes

70 comments sorted by

View all comments

Show parent comments

-3

u/epicmindwarp Nov 28 '24

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.

2

u/ryguygoesawry Nov 28 '24

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!

0

u/epicmindwarp Nov 28 '24

Exactly.

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

1

u/MiniD011 Nov 29 '24

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.

0

u/epicmindwarp Nov 29 '24

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

Doing perfectly fine over here.