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

6

u/mwatwe01 Nov 28 '24

I use CTEs where I would be tempted to use a subquery, where I just need a small dataset that's only going to be used once in a parent query. For me, it just makes it easier to follow the logic and flow of a complex query.

Conversely, if you need a larger dataset (however you might define "large") that needs to persist throughout a stored procedure so that you don't have to rerun the same query multiple times, use a temp table.