Date: Sat, 28 Jun 2003 06:22:41 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Question on OUTER JOINS.
Message-ID: <20030628112241.GA13700@wolff.to>
Mail-Followup-To: Ludwig Lim <lud_nowhere_man@yahoo.com>,
	Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Mailing List <pgsql-sql@postgresql.org>
References: <16131.1056780183@sss.pgh.pa.us>
	<20030628061618.19440.qmail@web80510.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030628061618.19440.qmail@web80510.mail.yahoo.com>
User-Agent: Mutt/1.5.4i

On Fri, Jun 27, 2003 at 23:16:18 -0700,
  Ludwig Lim <lud_nowhere_man@yahoo.com> wrote:
> 
>    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;

The two queries don't produce the same results in general.
For example if there is one record in permission with a status of test and
one record in employee with an employee_id of 6 and a status of test,
then the first query will return one row with a status of test and an
employee_id of null and the second query will return no rows.
query