Re: Outer Join - doing it first
Date: 1996/09/17
Message-ID: <323F9D46.52EF_at_netcomuk.co.uk>#1/1
If I understand you correctly what you want is
SELECT id, name FROM a WHERE id NOT IN (
SELECT id FROM b)
The sub-SELECT gives all b.id and the main SELECT gives
all the a.id whose values are not in the set from b.
Alireza Assadzadeh wrote:
>
> 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.
>
Finally, why include b.id and b.account if the query is to return only those rows where they don't exist? They will always be NULL!
Regards
Ian Received on Tue Sep 17 1996 - 00:00:00 CEST