Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 447EB475AE6
	for <pgsql-sql@postgresql.org>; Thu, 28 Nov 2002 03:10:00 -0500 (EST)
Received: from email02.aon.at (WARSL402PIP7.highway.telekom.at [195.3.96.94])
	by postgresql.org (Postfix) with SMTP id 3E001475A9E
	for <pgsql-sql@postgresql.org>; Thu, 28 Nov 2002 03:09:59 -0500 (EST)
Received: (qmail 402324 invoked from network); 28 Nov 2002 08:09:59 -0000
Received: from m170p007.dipool.highway.telekom.at (HELO cantor)
	([62.46.11.39]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail2rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <nathan@ncyoung.com>; 28 Nov 2002 08:09:59 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: nathan@ncyoung.com
Cc: pgsql-sql@postgresql.org
Subject: Re: join question
Date: Thu, 28 Nov 2002 09:10:26 +0100
Message-ID: <grjbuugdh8fr7t7rkekl7jngjlksi3itb9@4ax.com>
References: <20021126080514.C77510-100000@megazone23.bigpanda.com>
	<98FCPJ2YA0B9EDSOVU5ZURIFVR76US2X.3de502c3@inky>
In-Reply-To: <98FCPJ2YA0B9EDSOVU5ZURIFVR76US2X.3de502c3@inky>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/426
X-Sequence-Number: 10568

On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <nyoung@asis.com>
wrote:
>OK, that works great, but I was told that I should avoid sub-selects when 
>possible for performance reasons.
>>
>> select member.memberId, member.name from member left outer join
>>  (select * from payment where yearPaid=2002) as a using (memberId) where
>>  yearPaid is null;

Nathan,
if you want a version without a subselect, try

    SELECT m.memberId, m.name
    FROM member AS m LEFT OUTER JOIN
         payment AS p ON p.yearPaid=2002 AND m.memberId=p.memberId
    WHERE p.memberId IS NULL;

though I don't know whether it is faster.

Servus
 Manfred