I have a query with several JOIN and sub-queries, and when executed, it takes about 7 seconds to return.
Getting the same result, using CTE's, the query takes about 8 seconds of the first execution, but the next few times ( I could not know the interval, but it's a few hours ) the query is returned in less than 1 second.
When I use CTE's, does the database create a cache type?
No, these concepts are not related. The cache is created as needed for a variety of circumstances. Of course, CTE can take advantage of what's in the cache, can put stuff in the cache and take advantage of it later, but that's circumstantial and occurs as a side effect, not because CTE depends on it.
You may have obtained a different result for several reasons, it may be because the query can be better processed, better planned in more detail. But of course the cache may be influencing as well. They are too many variables to claim without knowing the concrete case in detail.
If you do not know how to do the test you may be collecting false information.
What would be relevant in choosing between JOIN's and SUBQUERIES's or CTE's?
The question does not make much sense. You have to use whatever is most pertinent to what you need and what works best in the specific case, and is not restricted to these 3 forms, which are not even antagonistic. In general it is possible to do anything without these 3 things, but it may be easier or more optimized to choose one in a certain scenario.
The question speaks in scenario example, but it has no example, has a result that does not even know how it was obtained.
