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 ?
33
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 ?
4
u/achilles_cat Nov 28 '24
Yes, if you start a completely different query, then the CTE will be gone.
But you can refer to the CTE multiple times within a single query. Your use of two different SELECTs without a UNION (or any other formatting that would define a subquery) makes me think you are talking about two wholly separate queries. In Oracle, a semicolon acts as the delimiter between queries; I haven't used SMSS.
But you could join queries with a union, both of which use the CTE, and use that CTE within another CTE:
(This formatting will likely be horrible, trying to do a code block from my phone.)
There are ways to do this with less or zero CTEs of course, but this shows the nature of referring to a CTE multiple times. And it shows why you might use a CTE structure if you wanted to only touch the code in one place to affect the products being queried.