Re: ORDER BY does not work as expected with multiple joins

On Jan 14, 2006, at 0:22 , Adam Rosi-Kessel wrote:

> id1 will always have a value but id2 can be NULL. So should I do a
> left JOIN
> on id2 but a plain JOIN on id1? Is there a disadvantage to using a
> left JOIN
> where it is not necessary?

In that case, yes, I'd JOIN on id1 and LEFT JOIN on id2. I'm not sure
if there's a penalty or not in query planning, though there might be.
For me, I use JOIN unless I need to use a LEFT JOIN. (I can't think
of a time I've used a RIGHT JOIN.) In relational theory, JOIN is a
relational operator, while LEFT [OUTER] JOIN (or any OUTER JOIN) is
not. It's just a goal of mine to keep my queries as close as possible
to constructions that are based on relational theory, and I deviate
from that only when I have to, either for performance reasons, or
when SQL doesn't provide an appropriate equivalent to a relational
construct.

You can always use EXPLAIN ANALYZE to compare query plans. It can be
very useful to see how your query is executed by the planner.

Michael Glaesemann
grzm myrealbox com