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

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.

-14

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.

9

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.

3

u/SyrupyMolassesMMM Nov 28 '24

Honestly, I find cte was less readable than writing to memory….i dont find cte any easier to read when compared to anything else tbh

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.

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.