Difference b/w these subquery and inner join? I am getting different results.

[sh4il3sh]

Hello!
I get correct amount of entries with below query:

    lt =   SELECT tab1.act_id
                    FROM table1 AS tab1
                    INNER JOIN :it_score AS score
                    ON score.id = tab1.id
                    WHERE tab1.bb IN (
                                      -- BBIDs
                                      SELECT tab2.BB_ID
                                      FROM table2 AS tab2
                                      INNER JOIN :it_score AS score
                                      ON score.id = tab2.id
                                      WHERE tab2.si = :iv_si
                                         ) ;

But more with below query, which is not as expected but I want to go for a join instead of subquery.
I understand this could be because of how data is in tables as well, but I also want to know why.
I have failed after multiple tries.

lt =  SELECT tab1.act_id
      FROM table1 AS tab1
      INNER JOIN :it_score AS score
        ON score.id = tab1.id
      INNER JOIN table2 AS tab2
        ON tab1.bb = tab2.BB_ID           
      AND score.id = tab2.id              
      WHERE tab2.si = :iv_si;

this is also wrong result, idk why:

lt =  SELECT tab1.act_id
      FROM table1 AS tab1
      INNER JOIN :it_score AS score
        ON score.id = tab1.id
      INNER JOIN table2 AS tab2
        ON tab1.bb = tab2.BB_ID     
      INNER JOIN :it_score AS score2     
      ON score2.id = tab2.id           
      WHERE tab2.si = :iv_si;

some help would be appreciated, how do I get same result as my subquery?

Thanks

[Savio_Dmello]

The subquery method only matches rows where tab1.bb is in the list of BB_ID values produced by the subquery. The subquery does not combine conditions between table1 and table2 directly but applies filtering in stages.
Join logic directly combine rows based on match conditions. Incorrect or additional joins can produce more rows or incorrect matches.

To replicate the subquery logic with joins, you should ensure that the joins and filtering conditions align with the logic in the subquery. Here’s how you can structure it:

SELECT tab1.act_id
FROM table1 AS tab1
INNER JOIN :it_score AS score
ON score.id = tab1.id
INNER JOIN table2 AS tab2
ON tab1.bb = tab2.BB_ID
AND score.id = tab2.id
WHERE tab2.si = :iv_si;

[sh4il3sh]

Thanks @Savio_Dmello for the explanation, unfortunately I am getting more results.