Splitting & Refactoring `NestedLoopJoinStream`

[saadtajwar]

Is your feature request related to a problem or challenge?

As a part of the #23974 effort of simplifying streams, we would like to simplify the NestedLoopJoinStream. This includes both:

• splitting regular joins (inner/left/right/full) and the semi/anti/mark joins into separate streams, as they are different relational operations and different optimizations tend to apply to each. The current approach combines them and therefore needs several flags/configurations to route the internal logic, which adds complexity. The sort-merge join underwent a similar split
• refactor the streams to use the async generator pattern

Describe the solution you'd like

Per @2010YOUY01 :

PR1: Keep existing implementation, and implement a new stream for semi/anti/mark joins directly with the generator pattern

if !standard_join:
    SemiAntiStream
else:
    NestedLoopStream

PR2: Implement the standard join similarly, and delete the legacy implementation

Describe alternatives you've considered

N/A

Additional context

Note that we're tracking this in a separate issue to keep the conversation in #23974 a bit shorter - see #23974 for additional context

[saadtajwar]

cc @2010YOUY01 !

I'm going to start on this now with the below breakdown of two PRs! If along the way it looks like this might be two big for two PRs/there's a better way to split across PRs I will let you know! Thanks!

PR1: Keep existing implementation, and implement a new stream for semi/anti/mark joins directly with the generator pattern
if !standard_join:
SemiAntiStream
else:
NestedLoopStream
PR2: Implement the standard join similarly, and delete the legacy implementation

[saadtajwar]

take