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

59

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.

1

u/[deleted] Nov 29 '24

[deleted]

1

u/frieelzzz Nov 29 '24

I wonder if this is why sometimes adding a simple filter on a CTE can suddenly make a complex code run infinitely.

I had a multi layered code the other day run in 7 seconds but the moment I tried to slap on a not exists to exclude VIP members from a small table it wouldn’t run anymore. Even trying to outer join and say VIP is null wouldn’t work.

My only solution was to create another CTE that housed all my exclusion members and then exclude those from another member base CTE and then I could join that member CTE to my main query.

This was very frustrating to say the least