... I'm asking because PHVs may imply lateral dependencies which may make us have to use nestloop join.
I thought lateral join imply nestloop join, am I missing something? Here is my simple
testing.
I think it's true most of the time. And that's why we should try to avoid unnecessary lateral dependencies, as discussed in this thread.
ISTM in your example nestloop is chosen because there is no available mergejoinable/hashjoinable clause. In your query the lateral subquery would be pulled up into the parent query and there would be no lateral dependencies afterwards.
