Message-ID: <20030628061618.19440.qmail@web80510.mail.yahoo.com>
Date: Fri, 27 Jun 2003 23:16:18 -0700 (PDT)
From: Ludwig Lim <lud_nowhere_man@yahoo.com>
Subject: Re: Question on OUTER JOINS. 
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
In-Reply-To: <16131.1056780183@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ludwig Lim <lud_nowhere_man@yahoo.com> writes:
> >   1) Is the ON clause of an OUTER JOIN always
> > evaluated first before the WHERE clause?
> 
> No; the planner will do whatever it thinks is the
> most efficient way
> (assuming it can prove that the reordering it wants
> to do won't change
> the query result).

  If re-ordering does change the result, is the ON
clause evaluated first and the WHERE filters out the
result of the OUTER JOIN?

 
> >   Is there a way to rewrite the query as a view
> such
> > that one can do:
> 
> I'm really not clear on what you want here.  Better
> example please?
> 
   Sorry for not making it that clear.

   Is there way of rewritting :
   SELECT  a.status,
           employee_id
   FROM   permission a LEFT JOIN 
          ( SELECT * FROM employee WHERE employee_id
=5) as b ON (a.status = b.status)
   WHERE status='test'

into a query that has no subselect in the FROM clause.
 I mean can the query above be rewritten into
something like:
  
   SELECT a.status,
          b.employee_id
   FROM permission a LEFT JOIN employee b ON
        (a.status = b.status)
   WHERE a.status = 'test' and 
         b.employee_id = 5;

Thank you very much,

ludwig

   
__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com