Re: Question on OUTER JOINS.

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