Splitting & Refactoring `NestedLoopJoinStream`

### 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