Re: [SQL] Question on OUTER JOINS.

[Bruno Wolff III]

On Fri, Jun 27, 2003 at 23:16:18 -0700,
  Ludwig Lim <[EMAIL PROTECTED]> 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 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Question on OUTER JOINS.

[Tom Lane]

Ludwig Lim <[EMAIL PROTECTED]> writes:
>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.

In this particular case you could do

FROM   permission a LEFT JOIN 
   employee b ON (a.status = b.status AND b.employee_id=5)
WHERE status='test'

which AFAICS would give the same answers.  As Bruno points out,
you can't move qualification conditions up and down past outer
joins without changing the answers in general.  But the above
change is okay: either way, A rows that don't match to a B row
with employee_id=5 will be emitted with nulls instead.

However, in this particular case I don't see why you're bothering.
If the sub-SELECT can be flattened, the planner will generally do
it for you.  I'd expect the first form of the query to give the
same plan (in pre-7.4 releases, maybe even a better plan) as the
second.

Perhaps you're showing us an oversimplified version of your
real problem?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])