Skip site navigation (1) Skip section navigation (2)

Re: join question

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: nathan(at)ncyoung(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: join question
Date: 2002-11-28 08:10:26
Message-ID: grjbuugdh8fr7t7rkekl7jngjlksi3itb9@4ax.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <nyoung(at)asis(dot)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

In response to

pgsql-sql by date

Next:From: Christoph HallerDate: 2002-11-28 16:14:06
Subject: Re: retrieving specific info. from one column and locating it in another
Previous:From: Peter ChildsDate: 2002-11-27 20:04:40
Subject: Re: Question on SQL and pg_-tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group