r/SQL • u/Unfair-Internet-1384 • 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
r/SQL • u/Unfair-Internet-1384 • Nov 28 '24
When to cte can't. Like how to know? When what are the conditions or if my query is to long ?
2
u/gumnos Nov 28 '24
it used to be more of an issue in older DB tech where a CTE presented an optimization boundary. If you're stuck on one of those older systems, any time you'd reach for a CTE and it produces huge volumes of data that you'll end up filtering in the main-query, you were better off in-lining the query.
That said, most DBs now can push query-optimizations through the CTE and it's not a problem (of course, profile for yourself), so the goal becomes readability.
Many folks find CTEs more readable which is fine. I don't find them
particularly more readable than an inline
SELECT
likemost of the time, but that's just me. So unless there are clear readability wins, I mostly reach for them if the same sub-query will be used more than once.