Re: Question on OUTER JOINS.

On Fri, Jun 27, 2003 at 23:16:18 -0700,
Ludwig Lim <lud_nowhere_man(at)yahoo(dot)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