This content has been marked as final. Show 7 replies
its called join (right/left).left : returns all rows from the left table even if there are no matches in the right table
see examples :
Osama-mustafa if the plus sign(+) is on left side then it is called as right outer join. In the same way as the above case is also right outer join not left outer. By the there is no table in right side-- only number 3 is there. Can u clarify further
it provides, if there is a row comming from B table it must have 3 in b2 column but also the other rows which is comming from A table but not in B would come.
you can test it too.
Edited by: Mustafa Kalaycı on Jul 31, 2012 3:09 AM
create table a (id number); create table b (id number, r number); insert into a values (1); insert into a values (2); insert into a values (3); insert into b values (1,2); insert into b values (3,2); insert into b values (3,3); commit; select * from a,b where a.id=b.id(+) and b.r(+) = 3; 3 3 3 2 null null 1 null null select * from a,b where a.id=b.id(+) and b.r = 3 ; 3 3 3
Thanks a lot
Edited by: Uvaraja on Jul 31, 2012 3:23 AM
Thanks Mustafa Kalaya you made it clear
Mustafa Kalaya , In you answer it should fetch only 2 records only na, only id 1 and 3 are matching na, but how it is fetching three rows. Pls clarify that. Only id 1 and 3 are in both table.
Uvaraja, yes i missed that. as I understand, it cause to filter first B table and just return rows which has r column is 3 and after that make outer join operation.
this means, this query will return all rows in A table but only matches B table rows whose R column has value of 3. I hope that I could explain.
by the way, it is "KALAY C I" not "KALAY A" :-) a dotless i in my language.
Edited by: Mustafa Kalaycı on Jul 31, 2012 4:12 AM