Walter Meester
HoogkarspelPays-Bas
Information générale
Catégorie:
Bases de données
> No, see erics reply. A self join would do also. I'm not sure about performance differences though..
>
>Yeah, unless you're talking about a small table, one should always look an alterative to a correlated subquery - a self join or even derived table will outperform a correlated subquery.
There are always different ways to skin the cat like self joins, joining with a subquery, using EXISTS(subquery) or IN(subquery) are all viable options to this type of query. However it really depends on the optimizer on what is fastest in my experience. I've been stumped more than once where I had an average complex expression that was not optimizable in either VFP and SQL server. I had to rewrite the statement to use a UNION ALL !!
The same with EXISTS(). Though is logically is a subquery, it often is handled as a special type of join (VFP8 can handle 2 subqueries, but often up to 9 EXISTS() subqueries) and is extremely efficient and fast. However in some (more complex) cases the optimizer decides the statment is not optimizable and the performance is bad.
Walter,
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement