[odb-users] Question about join type for polymorphic objects

[Boris Kolpackov]

Hi Aleksey,

Енакаев Алексей <Aleksey.Enakaev at infotecs.ru> writes:

> SELECT * FROM "employee" LEFT JOIN "person" ON "person"."id" =
> "person"."id" ... etc.
>
> And doesn't is look more reasonable to use INNER JOIN always for
> this kind of objects?

Yes, for all intents and purposes this is an INNER JOIN. And it
shouldn't be any slower, in theory. However, in practice, the
implementations seem to do a better job optimizing more commonly-
used LEFT JOINs than INNER JOINs[1][2]. So I am reluctant to
just change this.


> but PostgreSQL can not use "LEFT JOIN" with "FOR UPDATE".
>
> Is there any way to explicit join type for polymorphic objects? 

Not at the moment. What you could do, however, is create a
"for update" object loading view that would include that
"FOR UPDATE OF ..." clause once in the query condition. This
way you won't have to keep repeating it in your query
construction code. Object loading views (Section 10.2) were
added to handle stuff like this. 

[1] http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server
[2] http://stackoverflow.com/questions/17100819/left-join-significantly-faster-than-inner-join 

Boris