I was just reading this article over at ONLamp.com. The author appears to be coming from a MySQL perspective. Let me give you the Oracle perspective - naive join's aren't always bad.
The problem is that subqueries, whether you use 'in' or 'exists' can actually produce worse execution plans than a naive join. Why? The answer is, quite simply, indexes.
I happened to find a Penelope query for a view definition in our production code. I tried the naive join, an 'exists' and an 'in' version. The result is that, without an index on the child select, it just doesn't make a significant difference. I even tried adding the merge_sj hint, but it didn't help.
Addenda: I guess the author is an Oracle consultant after all. I was mislead by a couple unrelated items on the page itself.
Actually, Stéphane is a long-time Oracle consultant.
Re:MySQL Perspective?
djberg96 on 2004-10-07T20:16:42
Whoops. I guess I believed that because of the "MySQL Reference Manual" advertisement on the right side of the page, as well as the "Return to the MySQL DevCenter" at the bottom of the page.
