From: Michael Glaesemann <grzm@myrealbox.com>
To: Adam Rosi-Kessel <adam@rosi-kessel.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY does not work as expected with multiple joins
Date: Sat, 14 Jan 2006 00:45:09 +0900
Message-ID: <77672CF1-49CF-4224-B2CA-E6FFDAA8D13B@myrealbox.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@myrealbox.com, adam@rosi-kessel.org
Subject: Re: ORDER BY does not work as expected with multiple joins
In-Reply-To: <77672CF1-49CF-4224-B2CA-E6FFDAA8D13B@myrealbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox