pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on OUTER JOINS.
5+ messages / 3 participants
[nested] [flat]
* Question on OUTER JOINS.
@ 2003-06-28 05:45 Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 06:03 ` Re: Question on OUTER JOINS. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Ludwig Lim @ 2003-06-28 05:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on OUTER JOINS.
2003-06-28 05:45 Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2003-06-28 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-28 06:16 ` Re: Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-06-28 06:03 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on OUTER JOINS.
2003-06-28 05:45 Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 06:03 ` Re: Question on OUTER JOINS. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-06-28 06:16 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 11:22 ` Re: Question on OUTER JOINS. Bruno Wolff III <bruno@wolff.to>
2003-06-28 15:43 ` Re: Question on OUTER JOINS. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Ludwig Lim @ 2003-06-28 06:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on OUTER JOINS.
2003-06-28 05:45 Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 06:03 ` Re: Question on OUTER JOINS. Tom Lane <tgl@sss.pgh.pa.us>
2003-06-28 06:16 ` Re: Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2003-06-28 11:22 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2003-06-28 11:22 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on OUTER JOINS.
2003-06-28 05:45 Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 06:03 ` Re: Question on OUTER JOINS. Tom Lane <tgl@sss.pgh.pa.us>
2003-06-28 06:16 ` Re: Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2003-06-28 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-06-28 15:43 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-06-28 15:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-28 05:45 Question on OUTER JOINS. Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 06:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-28 06:16 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2003-06-28 11:22 ` Bruno Wolff III <bruno@wolff.to>
2003-06-28 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox