Re: Outer Join - doing it first

Try the following SQL:

select * from a where id not in (select id from b)

You do not need an outer join in this case.

Marco A.
DBA - Tecsys Inc. (www.tecsys.com)

Alireza Assadzadeh <aassadza_at_chat.carleton.ca> wrote in article <323E34E4.76FD_at_chat.carleton.ca>...
> 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 as
> 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
> -----------------------
>
> then use the this select:
>
> -----------------------
> select * from work_around
> where b_id is NULL
> -----------------------
>
> All suggestions are greatly appreciated.
>
> Best regards,
>
> Alireza Assadzadeh
> --
>
Received on Fri Sep 20 1996 - 00:00:00 CEST