[replyguy912]

Agree on struggling for real scenarios where you'd use a right-join, but your example with

select * from dogs where owner_id not in (select id from owners)

You wouldn't actually recommend something like this would you? The query plans for sub-selects vs. left-joins are very different, with lots of implementations having limits on the "in" clause.

Kind of funny that the way you'd feel if you saw a right join is similar to how I feel about sub-selects!

[p2t2p]

I would say "it depends". I have seen when replacing bunch of joins with sub selects improved performance. I have seen otherwise as well. It all depends on the nature of your data, RDBMS you're using and statistics it has accumulated.

I haven't seen anybody hitting "in" clause limits in query like mine but we did hit such a limit once when we were passing a hardcoded list of IDS from client side. Something like "where blah in (id1, id2, id3.... idn)". I remember that one failing on MS SQL.