7 Replies Latest reply: Sep 30, 2013 3:40 PM by user8007837 RSS

    Outer joins in Condtions?

    user8007837

      Hi,

       

      I have the following code in one of my procedures, which I have never seen till date.

      Where IsActive ( + ) = 'Y'

      Can someone tell me what is the use of ( + ) sing in the condtion? Thanks in advance.

        • 1. Re: Outer joins in Condtions?
          SomeoneElse

          Well, you already know what an outer join is so I don't get what you're asking.


          • 2. Re: Outer joins in Condtions?
            user8007837

            Hi,

             

            I know how outer join works in joins  for example, (a.prod_id ( + )= b.prod_id) but I don't understand it in condtions for example ( IsActive ( + ) = 'Y' ).

             

            Hope you understand my point.

            • 3. Re: Outer joins in Condtions?
              SomeoneElse

              It means exactly the same thing.

               

              Try a little test with your own tables but eliminate the outer join syntax on that one line of code and see what happens.

              • 4. Re: Outer joins in Condtions?
                user8007837

                Thanks for your reply. I have tested it with the following example but I don't see any difference. Could you please give me an example where exactly this type of code is used?

                 

                Example:

                 

                Create table:

                CREATE TABLE JOIN_TEST (COLUMN1 VARCHAR2(50), ISACTIVE CHAR(1));

                 

                Insert Statement:

                INSERT INTO JOIN_TEST (COLUMN1, ISACTIVE) VALUES ('Row1', 'Y');

                INSERT INTO JOIN_TEST (COLUMN1, ISACTIVE) VALUES ('Row2', 'Y');

                INSERT INTO JOIN_TEST (COLUMN1, ISACTIVE) VALUES ('Row3', 'N');

                INSERT INTO JOIN_TEST (COLUMN1, ISACTIVE) VALUES ('Row4', 'N');

                INSERT INTO JOIN_TEST (COLUMN1) VALUES ('Row5');

                INSERT INTO JOIN_TEST (COLUMN1) VALUES ('Row6');

                Where Condtion:

                SELECT * FROM join_test

                WHERE 1=1

                AND isactive(+) = 'Y';

                PS: If I use isactive(+) = 'Y' or isactive = 'Y',  I get the same results.

                • 5. Re: Outer joins in Condtions?
                  SomeoneElse

                  You aren't joining any tables.  So using outer join syntax is (apparently) meaningless.


                  • 6. Re: Outer joins in Condtions?
                    SomeoneElse

                    Here's a simple example.  The first query has an outer join and works as expected.  But if we remove the outer join from the second example (on line 7), the query goes back to being an inner join.  Regardless of the other outer join syntax, that line says b.col2 *must* be equal to 1.

                     

                    SQL> with a as (select 1 col1, 1 col2 from dual union all
                      2             select 2 col1, 2 col2 from dual)
                      3      ,b as (select 1 col1, 1 col2 from dual)
                      4  select *
                      5  from   a, b
                      6  where  a.col1 = b.col1(+)
                      7  and    b.col2(+) = 1
                      8  ;

                                    COL1                 COL2                 COL1                 COL2
                    -------------------- -------------------- -------------------- --------------------
                                       1                    1                    1                    1
                                       2                    2

                    SQL> with a as (select 1 col1, 1 col2 from dual union all
                      2             select 2 col1, 2 col2 from dual)
                      3      ,b as (select 1 col1, 1 col2 from dual)
                      4  select *
                      5  from   a, b
                      6  where  a.col1 = b.col1(+)
                      7  and    b.col2 = 1
                      8  ;

                                    COL1                 COL2                 COL1                 COL2
                    -------------------- -------------------- -------------------- --------------------
                                       1                    1                    1                    1

                    • 7. Re: Outer joins in Condtions?
                      user8007837

                      You are the real Guru. Thanks a lot for explaining with an example, which is very easy to understand.