If you are using MS SQL then you can review the execution plan and see exactly why it's different.

From a 30,000 ft view...

I have always felt that JOIN is the natural SQL engine experience. The optimizer might actually be smart enough to go to the table ra first in that query (you would have to view the execution plan to really know that).

NOT IN is a tough construct to understand. If you imagine for a second it might actually build a "list" and then hit each r_id against that list. Building that can't be fast...

btw - you can use JOIN instead of LEFT JOIN and actually get rid of your WHERE statement. JOIN will only return a row from the table on the "left" if the sister row exists in the table on the "right" - which is what your WHERE clause is testing.