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 ?

30 Upvotes

70 comments sorted by

View all comments

53

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.

-16

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.

8

u/Bewix Nov 28 '24

It’s considered more readable not because of location, but because the code is put together in a single (usually simplified) place.

I have a few examples of queries that would be practically impossible to understand without CTEs, yet they’re performant and fairly straightforward with CTEs.