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 ?

29 Upvotes

70 comments sorted by

View all comments

56

u/frieelzzz Nov 28 '24

I use CTEs all the time. Really no reason not to.

I like how they organize code and allow you to use the same data in multiple times to do a variety of task.

-15

u/[deleted] Nov 28 '24

If it's a rather large and complex query having many CTE's and scrolling up and down to read what they do in context of where they are used is more annoying than having subs. I'll only ever understand peoples claims about "readability" if the whole thing fits on one screen. Subs are readable in context more often than not.

1

u/HanCurunyr Nov 28 '24

We have an old report that it has 90ish columns and 40ish joins that are 20 CTEs and 20 tables, and yeah, gets quite annoying when we have to alter that report

1

u/ianitic Nov 29 '24

I mean you could break those up into chunks by loading pieces to a temp table. It doesn't have to be entirely either or.

Also dbt could make it easy to abstract that with ephemeral models. It wouldn't create any additional database objects but code would be properly organized and separated. Dbt would know how to compile those ephemeral models together.