7 Replies Latest reply: Jul 31, 2012 6:15 AM by Mustafa KALAYCI RSS

    b2(+) = 3

    945595
      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
          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
            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
              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
                Thanks a lot

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