Re: Outer Join - doing it first

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. > You save a selection term, b.id IS NULL and a join term a.id = b.id. The optimiser will take the selection term first. This will return you a set of results with b.id as NULL; that's what you asked for. The join clause will then return nothing. Any attempt to compare a value - even NULL - to NULL will return FALSE. There was a long running discussion about this not long ago. As a matter of interest, if you did do the join first this same rule about NULLs would ensure that the returned set contains no instances where the id is NULL. The selection term would then eliminate all of that set and return you nothing. At the bottom of your problem is a failure to understand the nature of the NULL returned from an outer join versus the NULL in the selection term. If you had: 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 this might return some rows with b_id and b_account set to NULL. This comes about from rows in a with no counterpart in b as you say. You should think of these NULLs as padding inserted AFTER the reading of the database. The NULL in the WHERE section refers to b.id, the data in the database BEFORE you read it. I hope that by my comparing the alias (b_id) with the table.column form, b.id, you will understand this more clearly. 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