10 Replies Latest reply: Jan 28, 2013 7:03 AM by ranit B RSS

    Reg : concept of Outer Joins -

    ranit B
      Hi Experts,

      I've a very silly and newbie doubt regarding Outer joins.

      Suppose we have 2 tables - A and B.

      +A right outer join B+ yields same result as +B left outer join A+

      Then why do we have two different type of Outer Joins (Inner & Outer), when only one can handle both scenarios (by reversing the table join clause)?

      Am i missing some important underlying concept here?

      TIA,
      Ranit B.
        • 1. Re: Reg : concept of Outer Joins -
          Frank Kulash
          Hi, Ranit,
          ranit B wrote:
          Hi Experts,

          I've a very silly and newbie doubt regarding Outer joins.

          Suppose we have 2 tables - A and B.

          +A right outer join B+ yields same result as +B left outer join A+
          That's right; those two are equivalent.
          Then why do we have two different type of Outer Joins (Inner & Outer), when only one can handle both scenarios (by reversing the table join clause)?

          Am i missing some important underlying concept here?
          No you're not missing anything. There's no particular reason to have both ways. Languages (computer languages as well as natural languages) often have illogical, useless features.
          You can always get by with just one or the other. In fact, most people do exactly that. Most people use always use LEFT OUTER JOIN, and never use RIGHT OUTER JOIN.

          One situation where you might be tempeted to use both is
          FROM              a
          LEFT OUTER JOIN   b  ON ...
          RIGHT OUTER JOIN  c  ON ...
          That is, tables a and c are related through b, and you want to see all possible values of a and c at least once, even if they are not related to any row in b. Depending on the data, I would handle this rare situation with a combination of LEFT - and FULL outer joins, such as
          FROM              a
          LEFT OUTER JOIN   b  ON ...
          FULL OUTER JOIN   c  ON ...
          • 2. Re: Reg : concept of Outer Joins -
            ranit B
            Thanks a lot Frank, for that wonderful piece of demo. Much appreciated.

            Also, I was going through the Join doc pages and got stuck here -
            >
            A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.

            A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

            A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
            >
            From - http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

            I'm trying a lot to replicate this in my code, but not able to achieve it.
            Can you please help me understand this?
            • 3. Re: Reg : concept of Outer Joins -
              Frank Kulash
              Hi,
              ranit B wrote:
              ... I was going through the Join doc pages and got stuck here -
              >
              A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.

              A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
              >
              Actually, starting in Oracle 10.2.0.5.0, you can do that, though the documentation still says you can't.
              See {message:id=10811552}
              >
              A WHERE condition cannot compare any column marked with the (+) operator with a subquery.
              >
              From - http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

              I'm trying a lot to replicate this in my code, but not able to achieve it.
              Can you please help me understand this?
              I'm not sure I uderstand your question.
              Are you saying you tried to re-create the errors by doing some of the things you know will cause errors? Okay, that can be a good learning experience.
              Post your code. It's hard to say what you did wrong when I don't know what you did. (It seems funny to say "did wrong" to mean "did not get an error".)
              • 4. Re: Reg : concept of Outer Joins -
                ranit B
                I tried something like this :
                Ranit>> with Table1 as (
                  2                  select 1 Account,100 Column2 from dual union all
                  3                  select 2,200 from dual union all
                  4                  select 3,300 from dual union all
                  5                  select 4,400 from dual
                  6                 ),
                  7       Table2 as (
                  8                  select 1 Account2,'OLD' ColumnB from dual union all
                  9                  select 1 Account2,'NEW' ColumnB from dual union all
                 10                  select 2,'OLD' from dual union all
                 11                      select 5,'OLDx' from dual union all
                 12                  select 3,'NEW' from dual
                 13                 )
                 14  select  Table1.Account,
                 15          Table1.Column2,
                 16          Table2.Account2,
                 17          Table2.ColumnB
                 18    from
                 19  table2,table1
                 20  where
                 21  table1.account(+) = table2.account2
                 22  and table1.account IN (2,3);
                
                   ACCOUNT    COLUMN2   ACCOUNT2 COLU                                                                                                                                                                                       
                ---------- ---------- ---------- ----                                                                                                                                                                                       
                         2        200          2 OLD                                                                                                                                                                                        
                         3        300          3 NEW     
                As per docs :
                >
                A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
                >
                but I could use the IN clause and get the results.

                Can you please give me some demo for the above 3 points from docs?
                Thanks.
                • 5. Re: Reg : concept of Outer Joins -
                  Frank Kulash
                  Hi,
                  ranit B wrote:
                  I tried something like this :
                  Ranit>> with Table1 as (
                  2                  select 1 Account,100 Column2 from dual union all
                  3                  select 2,200 from dual union all
                  4                  select 3,300 from dual union all
                  5                  select 4,400 from dual
                  6                 ),
                  7       Table2 as (
                  8                  select 1 Account2,'OLD' ColumnB from dual union all
                  9                  select 1 Account2,'NEW' ColumnB from dual union all
                  10                  select 2,'OLD' from dual union all
                  11                      select 5,'OLDx' from dual union all
                  12                  select 3,'NEW' from dual
                  13                 )
                  14  select  Table1.Account,
                  15          Table1.Column2,
                  16          Table2.Account2,
                  17          Table2.ColumnB
                  18    from
                  19  table2,table1
                  20  where
                  21  table1.account(+) = table2.account2
                  22  and table1.account IN (2,3);
                  
                  ACCOUNT    COLUMN2   ACCOUNT2 COLU                                                                                                                                                                                       
                  ---------- ---------- ---------- ----                                                                                                                                                                                       
                  2        200          2 OLD                                                                                                                                                                                        
                  3        300          3 NEW     
                  As per docs :
                  >
                  A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
                  >
                  but I could use the IN clause and get the results.
                  Ah, you're using an IN comparison condition, but no expression in that comparison has the ( + ) operator. You happen to be using the ( + ) operator elsewhere in the same query, but that's not what the documentation is talking about. In fact, what you're doing is the same as an inner join; if you're not using the ( + ) operator in one condition involving table1, then you might as well not be usinng it in any condition involving table1.
                  If you want to get the error, then change line 22 to say
                  and table1.account (+) IN (2,3);
                  This will be similar to case 2 below.

                  You may notice that, outside of \
                   tags, I'm adding spaces around the + sign in "( + )", just so this site won't render it as (+).  When writing code, you shouldn't use the spaces.  Actually, when writing code, you shouldn't use the ( + ) opeator at all; you should use ANSI join syntax.
                  
                  Can you please give me some demo for the above 3 points from docs?
                  Let's say we want to see some information about the departments in scott.emp, and the employees in each department (if any).  However, we're not interested in all employees, we're only interested in employees who have the jobs 'ANALYST' or 'PRESIDENT'.  That is, we're looking for output like this:
                  DNAME DEPTNO ENAME JOB
                  -------------- ---------- ---------- ---------
                  ACCOUNTING 10 KING PRESIDENT
                  RESEARCH 20 FORD ANALYST
                  RESEARCH 20 SCOTT ANALYST
                  SALES 30
                  OPERATIONS 40
                  Note that the result set includes all 4 departments from scott.dept.
                  No row in scott.emp has deptno=40, but we want deptno=40 to be in the result set anyway.
                  There are employees with deptno=30, but none of them are either ANALYSTs or PRESIDENTs.
                  There are 2 ways you might try to solve this, bith of which the documentation says you can't do.
                  
                  <h2> Case 1: A WHERE condition containing the ( + ) operator cannot be combined with another condition using the OR logical operator. </h2>
                  Acutally, it can, but as I pointed out earlier, the result is the same as an inner join.
                  I think what the documentation is trying to say here is that
                  SELECT     d.dname, d.deptno, e.ename, e.job
                  FROM     scott.dept d
                  ,     scott.emp e
                  WHERE     e.deptno (+) = d.deptno
                  AND     ( e.job (+) = 'ANALYST'
                       OR e.job (+) = 'PRESIDENT'
                       )
                  ;
                  raises this error:
                  ... OR e.job (+) = 'PRESIDENT'
                  *
                  ERROR at line 6:
                  ORA-01719: outer join operator (+) not allowed in operand of OR or IN
                  <h2> Case 2: A WHERE condition cannot use the IN comparison condition to compare a column marked with the ( + ) operator with an expression. </h2>
                  That is, trying to get the same results as above:
                  SELECT     d.dname, d.deptno, e.ename, e.job
                  FROM     scott.dept d
                  ,     scott.emp e
                  WHERE     e.deptno (+) = d.deptno
                  AND     e.job (+) IN ( 'ANALYST'
                                 , 'PRESIDENT'
                                 )
                  ;
                  results in the ORA-01719 error, similar to the previous case.
                  This is similar to the example you posted.
                  
                  <h2> Case 3: A WHERE condition cannot compare any column marked with the ( + ) operator with a subquery. </h2>
                  To show what this means, let's change the requirements.  Instead of looking for 2 specific jobs, 'ANALYST' and 'PRESIDENT', let's say we want to look for the most common job, as returned by STATS_MODE, whatever job that may be.  The documentation says that we can't do it this way:
                  SELECT     d.dname, d.deptno, e.ename, e.job
                  FROM     scott.dept d
                  ,     scott.emp e
                  WHERE     e.deptno (+) = d.deptno
                  AND     e.job (+) = (
                                 SELECT     STATS_MODE (job)
                                 FROM     scott.emp
                                 )
                  ;
                  Sure enough, if we try this, we'll get the error:
                  ORA-01799: a column may not be outer-joined to a subquery
                  In all these cases, there are work-arounds.  For example, the get the results we want in cases 1 and 2, we can do some of the filtering in a sub-query, rather than in the join condition:
                  WITH     emp_subset     AS
                  (
                       SELECT     ename, job, deptno
                       FROM     scott.emp
                       WHERE     job     IN ('ANALYST', 'PRESIDENT')
                  )
                  SELECT     d.dname, d.deptno, e.ename, e.job
                  FROM     scott.dept d
                  ,     emp_subset e
                  WHERE     e.deptno (+) = d.deptno
                  ;
                  This works.  In fact, this is the query I used to produced the results I posted above.
                  
                  However, the best solution is to use ANSI join notation, where none of the restrictions you mentioned apply.
                  For example:
                  SELECT     d.dname, d.deptno, e.ename, e.job
                  FROM     scott.dept d
                  LEFT OUTER JOIN     scott.emp e
                            ON      e.deptno = d.deptno
                            AND     e.job     IN ('ANALYST', 'PRESIDENT')
                  ;
                  I recommend ANSI notation for all joins, especially outer joins.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 6. Re: Reg : concept of Outer Joins -
                    ranit B
                    In all these cases, there are work-arounds. For example, the get the results we want in cases 1 and 2, we can do some of the filtering in a sub-query, rather than in the join condition:
                    WITH     emp_subset      AS
                    (
                         SELECT     ename, job, deptno
                         FROM     scott.emp
                         WHERE     job     IN ('ANALYST', 'PRESIDENT')
                    )
                    SELECT     d.dname, d.deptno, e.ename, e.job
                    FROM     scott.dept  d
                    ,     emp_subset  e
                    WHERE     e.deptno (+)   = d.deptno
                    ;
                    This works. In fact, this is the query I used to produced the results I posted above.
                    But, then how this works for me ???
                    Ranit>> select
                      2  d.deptno,d.dname,
                      3  e.ename,e.job
                      4  from
                      5       dept d,emp e
                      6  where
                      7       e.deptno(+) = d.deptno
                      8  and
                      9       e.job(+) in ('ANALYST','PRESIDENT');
                    
                        DEPTNO DNAME           ENAME      JOB                                                                                                                                                                                   
                    ---------- --------------- ---------- ---------                                                                                                                                                                             
                            20 RESEARCH        SCOTT      ANALYST                                                                                                                                                                               
                            10 ACCOUNTING      ELLISON    PRESIDENT                                                                                                                                                                             
                            20 RESEARCH        LOFSTROM   ANALYST                                                                                                                                                                               
                            30 SALES                                                                                                                                                                                                            
                            40 OPERATIONS   
                    
                    Ranit>> select
                      2  d.deptno,d.dname,
                      3  e.ename,e.job
                      4  from
                      5       dept d,emp e
                      6  where
                      7       e.deptno(+) = d.deptno
                      8  and
                      9       e.job in ('ANALYST','PRESIDENT'); -- "on removing (+)"
                    
                        DEPTNO DNAME           ENAME      JOB                                                                                                                                                                                   
                    ---------- --------------- ---------- ---------                                                                                                                                                                             
                            10 ACCOUNTING      ELLISON    PRESIDENT                                                                                                                                                                             
                            20 RESEARCH        LOFSTROM   ANALYST                                                                                                                                                                               
                            20 RESEARCH        SCOTT      ANALYST    
                    Tested on Database :
                    BANNER
                    --------------------------------------------------------------------------------
                    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                    PL/SQL Release 11.2.0.2.0 - Production
                    CORE    11.2.0.2.0      Production
                    TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
                    NLSRTL Version 11.2.0.2.0 - Production
                    Edited by: ranit B on Jan 28, 2013 2:36 AM
                    -- added DB version
                    • 7. Re: Reg : concept of Outer Joins -
                      Frank Kulash
                      Hi,
                      ranit B wrote:
                      ... But, then how this works for me ???
                      Ranit>> select
                      2  d.deptno,d.dname,
                      3  e.ename,e.job
                      4  from
                      5       dept d,emp e
                      6  where
                      7       e.deptno(+) = d.deptno
                      8  and
                      9       e.job(+) in ('ANALYST','PRESIDENT');
                      
                      DEPTNO DNAME           ENAME      JOB                                                                                                                                                                                   
                      ---------- --------------- ---------- ---------                                                                                                                                                                             
                      20 RESEARCH        SCOTT      ANALYST                                                                                                                                                                               
                      10 ACCOUNTING      ELLISON    PRESIDENT                                                                                                                                                                             
                      20 RESEARCH        LOFSTROM   ANALYST                                                                                                                                                                               
                      30 SALES                                                                                                                                                                                                            
                      40 OPERATIONS   
                      In the most recent versions of Oracle, starting with 10.2.0.5, ( + ) works with IN, even though the documentation still says it shouldn't.
                      See {message:id=10811552}
                      Ranit>> select
                      2  d.deptno,d.dname,
                      3  e.ename,e.job
                      4  from
                      5       dept d,emp e
                      6  where
                      7       e.deptno(+) = d.deptno
                      8  and
                      9       e.job in ('ANALYST','PRESIDENT'); -- "on removing (+)"
                      
                      DEPTNO DNAME           ENAME      JOB                                                                                                                                                                                   
                      ---------- --------------- ---------- ---------                                                                                                                                                                             
                      10 ACCOUNTING      ELLISON    PRESIDENT                                                                                                                                                                             
                      20 RESEARCH        LOFSTROM   ANALYST                                                                                                                                                                               
                      20 RESEARCH        SCOTT      ANALYST    
                      Right; if any condition involving table e does not use ( + ), then that cancels the effect of ( + ) in other conditions involving e.
                      Since you're saying:
                      9      e.job in ('ANALYST','PRESIDENT'); -- "on removing (+)"
                      then you're effectively doing an inner join, even though you did use ( + ) in the other condition
                      7      e.deptno(+) = d.deptno
                      Tested on Database :
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                      PL/SQL Release 11.2.0.2.0 - Production
                      CORE    11.2.0.2.0      Production
                      TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
                      NLSRTL Version 11.2.0.2.0 - Production
                      Edited by: ranit B on Jan 28, 2013 2:36 AM
                      -- added DB version
                      Again, since you're using Oracle 9.1 or higher, then you should always use ANSI join syntax.
                      It's good to be able to read and understand queries that involve the older notation, but don't feel that you need to know exactly what is alowed and what is not allowed in the old syntax, since it has only historical interest.

                      The place of ( + ) in SQL is sort of like that of the Latin language in the English-speaking world. Isaac Newton lived in the English-speaking world, and he wrote books in Latin. In the 17th century, when Newton wrote, there were good reasons to learn Latin well enough to write in it. Today, it's nice for everybody to recognize a few Latin phrases or abbreviations (e.g. "e.g."), and a few people in special fields need to read Latin, but there's no compelling reason for most of us to learn it well enough to write in it. There's nothing wrong with learning Latin today, but the practical reasons that existed 300 years ago have mostly disappeared.
                      • 8. Re: Reg : concept of Outer Joins -
                        ranit B
                        So, as a Rule of Thumb - Can we always use ANSI SQL style for writing queries or they have any exceptions?

                        Thanks Frank for all your effort and tolerating my silly qns.
                        • 9. Re: Reg : concept of Outer Joins -
                          Frank Kulash
                          Hi,
                          ranit B wrote:
                          So, as a Rule of Thumb - Can we always use ANSI SQL style for writing queries or they have any exceptions?
                          Yes, always use ANSI style joins.

                          The only exceptions involve very old, unsupported versions.
                          Oracle did not support ANSI style joins until version 9.1, so people who are still using Oracle 8 (or earlier) can't use ANSI syntax.
                          There were quite a few bugs in Oracle 9.1 involving some of the less commonly used features, such as FULL OUTER JOIN, so people using Orace 9.1 may want to use the old syntax.
                          You're using Oracle 11, so none of that applies to you. Always use ANSI stryle joins.
                          • 10. Re: Reg : concept of Outer Joins -
                            ranit B
                            Thanks a ton Frank!!!

                            You have always been a savior for many in this forum. Your efforts are much appreciated. Please keep up the great work.
                            The world (and most importantly this forum) need more people like you. :)

                            Ranit B.