Currently the join optimizer only produces NLJ plans which are uncorrelated. This is because correlated looping plans are expressed using the INLJ node. NLJ plans apply the join predicate as a filter attached to the NLJ node itself, rather than a filter in the RHS.

The result is that NLJ plans may repeat duplicate work as the RHS will always produce the same results. We should try to avoid this when it would be beneficial. We could introduce some kind of caching node into the plan (similar to DocumentSourceSequentialDocumentCache) or explicit spool/unspool nodes to prevent the inner side from executing multiple times. However, if the inner side produces many results, this approach may not be beneficial. We should explore making a cost based decision on whether to do this based on cardinality estimates.