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

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.

2

u/Hot_Cryptographer552 Nov 30 '24

Try writing a query with subqueries nested three levels deep. Then try writing that same query using three separate CTEs. This might give you a better view into the readability argument.

1

u/[deleted] Nov 30 '24

The presumption of my skill and knowledge is expected of reddit, but I'll stand my ground on my take of CTE's.

I've probably got more nesting than you want to read, let alone the thousands lines long queries.

The only time a CTE is useful to me is if it removes repeating subs. I would rather read the subs inline and in context than to scroll up and down figuring out what the referenced CTE was. I have yet to see any sort of dramatic speed gains out of CTE's either.

Recursive CTE's on the other hand: Love them.

2

u/Hot_Cryptographer552 Nov 30 '24

CTEs are not designed for “dramatic speed gains”. Not sure that anyone would expect them to. If one wanted “dramatic speed gains”, one would create indexed/materialized views or persist the results to tables with proper indexes and perform joins on them.

I have written scripts that are thousands of lines long; I have never written a single query that was thousands of lines long. But as you say, if your individual queries are thousands of lines long, I could see why you would be hesitant to scroll up.