Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join with a constatnt
Outer Join with a constatnt [message #292325] Tue, 08 January 2008 09:42 Go to next message
jayeshprakash
Messages: 2
Registered: January 2008
Junior Member
I don't understand why we require to put a + sign before a column ,involved in outer join, when comparing with a constant value. I know that we should do like this but not able to understand why. I am looking for a reason explained at oracle behaviour level!
Re: Outer Join with a constatnt [message #292328 is a reply to message #292325] Tue, 08 January 2008 09:48 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why wouldn't you ?

If you don't tell Oracle it's an outer join it is going to assume an inner join.

[Updated on: Tue, 08 January 2008 09:49]

Report message to a moderator

Re: Outer Join with a constatnt [message #292330 is a reply to message #292328] Tue, 08 January 2008 09:51 Go to previous messageGo to next message
jayeshprakash
Messages: 2
Registered: January 2008
Junior Member
So why does it override the outer join with the inner join involving that constant? My question is why not vice-versa?
Re: Outer Join with a constatnt [message #292335 is a reply to message #292330] Tue, 08 January 2008 10:02 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
My best shot at an explanation...

if you state (using emp/dept example):
WHERE  d.department_id = e.department_id(+)
AND    e.last_name(+) = 'Smith'

so, using the + sign for the constant, it's like:
get department with employees named smith including any null values (so, include the departments that have no employees named smith, that also includes the departments with no employees at all, right?)

However, if you delete the + sign in the second line:
WHERE  d.department_id = e.department_id(+)
AND    e.last_name = 'Smith'

then you're asking:
Get departments with employees named smith. Period. No null values please.
Re: Outer Join with a constatnt [message #292338 is a reply to message #292325] Tue, 08 January 2008 10:31 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I'm guessing it's a matter of the 80-20 or 90-10 rule.

The majority of queries written probably do not include an outer join so if you want one, then you need to specify it.
Re: Outer Join with a constatnt [message #292390 is a reply to message #292338] Tue, 08 January 2008 20:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The equals operator is not a good way to explain how (+) works on constants, because if you leave off the (+) it performs an inner join.

Here's a better example:

  1  select dept.deptno, emp.ename, mgr
  2  from dept
  3  , emp
  4* where dept.deptno = emp.deptno
SQL> /

    DEPTNO ENAME             MGR
---------- ---------- ----------
        10 KING
        30 BLAKE            7839
        10 CLARK            7839
        20 JONES            7839
        20 SCOTT            7566
        20 FORD             7566
        20 SMITH            7902
        30 ALLEN            7698
        30 WARD             7698
        30 MARTIN           7698
        30 TURNER           7698
        20 ADAMS            7788
        30 JAMES            7698
        10 MILLER           7782


We have 14 employees, and KING has a NULL manager.

  1  select dept.deptno, emp.ename, mgr
  2  from dept
  3  , emp
  4* where dept.deptno = emp.deptno (+)
SQL> /

    DEPTNO ENAME             MGR
---------- ---------- ----------
        10 KING
        30 BLAKE            7839
        10 CLARK            7839
        20 JONES            7839
        20 SCOTT            7566
        20 FORD             7566
        20 SMITH            7902
        30 ALLEN            7698
        30 WARD             7698
        30 MARTIN           7698
        30 TURNER           7698

    DEPTNO ENAME             MGR
---------- ---------- ----------
        20 ADAMS            7788
        30 JAMES            7698
        10 MILLER           7782
        40


With the outer join, we now get 15 rows: the 14 employees plus a null row for department 40, which has no employees.

  1  select dept.deptno, emp.ename
  2  from dept
  3  , emp
  4  where dept.deptno = emp.deptno
  5* and mgr is null
SQL> /

    DEPTNO ENAME
---------- ----------
        10 KING


Note that ONLY deptartment 10 contains employees with NULL manager, so only department 10 shows.

  1  select dept.deptno, emp.ename
  2  from dept
  3  , emp
  4  where dept.deptno = emp.deptno (+)
  5* and mgr (+) is null
SQL> /

    DEPTNO ENAME
---------- ----------
        10 KING
        30
        40
        20


With the outer join specified "correctly" on both the join predicate and the filter predicate, we see all 4 departments. Departments 20 30 and 40 all show even though they contain no employees with a NULL manager.

Note that department 40 is different from departments 20 and 30 because it contains NO employees at all.

This is intuitively what we want from an outer join: all departments regardless of whether a matching employee was found.

  1  select dept.deptno, emp.ename
  2  from dept
  3  , emp
  4  where dept.deptno = emp.deptno (+)
  5* and mgr is null
SQL> /

    DEPTNO ENAME
---------- ----------
        10 KING
        40



Note the predicate mgr IS NULL without the outer join syntax.

Departments 20 and 30 DO have employees, but none with a NULL manager. When we include the (+) on the constant predicate, those departments successfully outer join, when we exclude it, they fail the outer join.

So what is happening in this last case is that Oracle is performing the outer join and picking up 15 rows (as in the second example), and then filtering out the 13 employees with non-null managers, leaving only 2 rows.

So in answer to your question: why is the (+) required when it seems redundant? Because it is not redundant: inner join, outer join including constant predicates, and outer join excluding constant predicates are all distinctly different (although not all the time).

Ross Leishman
Previous Topic: Script in PL/SQL language
Next Topic: Send data SMS from mobile to Oracle
Goto Forum:
  


Current Time: Mon Dec 05 09:01:09 CST 2016

Total time taken to generate the page: 0.09392 seconds