| Question about outer join [message #371334] |
Wed, 04 October 2000 09:44  |
vencent
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
Hi,
Could anyone help me out?
Thanks a lot in advance!
The following query uses an outer join
SELECT ename, job, dept.deptno, dname
FROM emp, dept
WHERE emp.deptno (+) = dept.deptno
AND job (+) = 'CLERK';
ENAME JOB DEPTNO DNAME
---------- --------- ---------- --------------
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
40 OPERATIONS
In this outer join, Oracle returns a row containing the OPERATIONS department even though no clerks work in this department. The (+) operator on the JOB column ensures that rows for which the JOB column is NULL are also returned. If this (+) were omitted, the row containing the OPERATIONS department would not be returned because its JOB value is not 'CLERK'.
(the above is from Oracle documents, table emp and dept is in user SCOTT's schema)
Following is my test result, I use a subquery in the FROM clause to get the result set first, then use other condition to get the final result, but it is different from the last, WHY?
I can understand the first condition user outer join (emp.deptno (+) = dept.deptno)
But what does 'job (+) = 'CLERK'' really mean?
SQL> SELECT ename, job, deptno, dname
2 from (SELECT ename, job, dept.deptno, dname
3 FROM emp, dept
4 WHERE emp.deptno (+) = dept.deptno) a
5 where a.job(+) = 'CLERK';
ENAME JOB DEPTNO DNAME
---------- --------- --------- --------------
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
SQL> SPOOL OUT
|
|
|
|
| Re: Question about outer join [message #371335 is a reply to message #371334] |
Wed, 04 October 2000 09:57  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Hi,
In second case, query returns only four rows because job values for fifth row is NULL..
SELECT ename, job, dept.deptno, dname
3 FROM emp, dept
4 WHERE emp.deptno (+) = dept.deptno
above query returns
MILLER CLERK 10 ACCOUNTING
SMITH CLERK 20 RESEARCH
ADAMS CLERK 20 RESEARCH
JAMES CLERK 30 SALES
NULL NULL 40 Operations
So , Query is not fetching last row.
HAve a nice day
[b][i]SURESH[b][i]
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
