I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ):

https://www.brentozar.com/pastetheplan/?id=HJEioh56N

I have a nested loop (inner join) with 97% in the query plan.

I'm sure the problem is the OR inside the second join because I changed something here and there and I could get rid of them but I would like to be sure what would be the best way to deal with data like these. also all those tables has millions of rows.

Table Definition:

CREATE TABLE [DBO].[TABLE1](
    [F1] [int] NOT NULL,
    [F1] [varchar](16) NOT NULL,
    [F3] [money] NOT NULL,
    [F4] [money] NOT NULL,
    

Im sure the problem is here: [![enter image description here][1]][1] [1]: https://i.sstatic.net/ieMgK.png

I know created this index:

CREATE NONCLUSTERED INDEX IX_TB1 ON DBO.TABLE1 ( F1, F2)

That index seek is now 14%, but got a HASH MATCH with 82% cost.