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

[ulrich_mhrke]

I expect there are some duplicates within your result. If you have some results with different tab1.bb (= tab2.bb_id) you will get more entries for each of them.

With the subquery you will have

... where tab1.bb in (..., value1,value2, ...)

This makes one record for each combination of tab1 and score. For the inner join you will get a record for value1 and one for value2 for each of these combinations.

If you are interested in the different result values without counting them, you can use select distinct or delete the duplicates after the selection.

sort lt.
delete adjacent duplicates from lt.