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

Reg : concept of Outer Joins -

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 -
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 -
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.
• 3. Re: Reg : concept of Outer Joins -
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.
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 -
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 -
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 -
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
• 7. Re: Reg : concept of Outer Joins -
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
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 -
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 -
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 -
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.