A subquery introduced with in, any, or exists cannot be flattened if one of the following is true:
The subquery is correlated and contains one or more aggregates.
The subquery is in the select list or in the set clause of an update statement.
The subquery is connected to the outer query with or.
The subquery is part of an isnull predicate.
The subquery is the outermost subquery in a case expression.
If the subquery computes a scalar aggregate, materialization rather than flattening is used.
See “Materializing subquery results”.