In-Reply-To: <43C7C5D1.9030600@rosi-kessel.org>
References: <43C7BA12.90103@rosi-kessel.org>
	<3AA68074-D959-442F-8329-79D09D5F9129@myrealbox.com>
	<43C7C5D1.9030600@rosi-kessel.org>
Mime-Version: 1.0 (Apple Message framework v746.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <77672CF1-49CF-4224-B2CA-E6FFDAA8D13B@myrealbox.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@myrealbox.com>
Subject: Re: ORDER BY does not work as expected with multiple joins
Date: Sat, 14 Jan 2006 00:45:09 +0900
To: Adam Rosi-Kessel <adam@rosi-kessel.org>


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