Обсуждение: Question on OUTER JOINS.

Hi:
 1) Is the ON clause of an OUTER JOIN always
evaluated first before the WHERE clause?
2) Given the ff SQL statement :
  SELECT employee_id,         a.status as status  FROM permissions a LEFT JOIN      (select * from employee where
employee_id= 3)
 
as b on (a.status=b.status)  WHERE a.status='test';
 Is there a way to rewrite the query as a view such
that one can do:
  select *  from test_view  where employee_id=3 and status='test';

Thank you very much,

ludwig lim

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

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).

>   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?
        regards, tom lane

--- 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

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 

Ludwig Lim <lud_nowhere_man@yahoo.com> 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