This discussion is archived
7 Replies Latest reply: Jul 31, 2012 4:15 AM by Mustafa KALAYCI RSS

b2(+) = 3

945595 Newbie
Currently Being Moderated
Hi all,
I have samll doubt in the following query. here is the query,

select a1,a2,b1,b2 from a,b where a1=b1(+) and b2(+) = 3

in the above query what does "b2(+) = 3 " what does it mean. I never heard about that. Please explain me.

Thanks in advance.

Regards,
Uvaraja
  • 1. Re: b2(+) = 3
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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 :
    http://www.w3schools.com/sql/sql_join_left.asp
  • 2. Re: b2(+) = 3
    945595 Newbie
    Currently Being Moderated
    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
  • 3. Re: b2(+) = 3
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    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.
    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
    Edited by: Mustafa Kalayc─▒ on Jul 31, 2012 3:09 AM
  • 4. Re: b2(+) = 3
    945595 Newbie
    Currently Being Moderated
    Thanks a lot

    Edited by: Uvaraja on Jul 31, 2012 3:23 AM
  • 5. Re: b2(+) = 3
    945595 Newbie
    Currently Being Moderated
    Thanks Mustafa Kalaya you made it clear
  • 6. Re: b2(+) = 3
    945595 Newbie
    Currently Being Moderated
    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.
  • 7. Re: b2(+) = 3
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points