Outer Join - doing it first
Hello,
I am trying to do an outer join between two tables with an
extra where clause condition on the subserviant (outer join) table.
This results in where clause to be applied to the subserviant table
BEFORE the joining. I want the outer join to be done first and then
the where cluase to be applied to the result. Is there a better way
to do this other than creating a view.
Please consider the following example:
-----------------------
Table a :
id integer,
name char
-----------------------
Table b:
id integer.
account char
-----------------------
What is needed:
-----------------------
select
a.id as a_id,
a.name as a_name,
b.id as b_id,
b.account as b_account
from
a, outer b
where a.id = b.id
and b.id is NULL
-----------------------
This should give all the rows in table a that don't exist in table b.
But it doesn't work because b.id is NULL condition is applied to b first
and then the outer join is done.
A simple work around is:
-----------------------
create view wa asselect
a.id as a_id,
a.name as a_name,
b.id as b_id,
b.account as b_account
from
a, outer b
where a.id = b.id
-----------------------
then use the this select:
-----------------------
select * from work_around
where b_id is NULL
-----------------------
All suggestions are greatly appreciated.
Best regards,
Alireza Assadzadeh
--