ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Mixing inner join and outer join

Mixing inner join and outer join

原创 Linux操作系统 作者:chncaesar 时间:2013-10-17 16:49:46 0 删除 编辑
SQL> show rel
release 1102000300

SQL> select dept.deptno, emp.ename
from dept left outer join emp on dept.deptno=emp.deptno
order by deptno nulls last;

    DEPTNO ENAME
---------- ----------
        10 az
        10 MILLER
        10 KING
        10 CLARK
        20 JONES
        20 SMITH
        20 FORD
        20 ADAMS
        20 SCOTT
        30 ALLEN
        30 TURNER
        30 JAMES
        30 WARD
        30 BLAKE
        30 MARTIN
        40

16 rows selected.

Let's see what happens after inner joining to bonus table.
SQL> select dept.deptno, emp.ename, bonus.comm
from dept left outer join emp on dept.deptno=emp.deptno
inner join bonus on emp.ename = bonus.ename
order by deptno nulls last;  

    DEPTNO ENAME            COMM
---------- ---------- ----------
        10 az                 .1
        10 MILLER             .1
        10 KING               .1
        10 CLARK              .1
        20 JONES              .1
        20 SMITH              .1
        20 FORD               .1
        20 ADAMS              .1
        20 SCOTT              .1
        30 ALLEN              .1
        30 TURNER             .1
        30 JAMES              .1
        30 WARD               .1
        30 BLAKE              .1
        30 MARTIN             .1

15 rows selected.
One row where deptno=40 is missing. The reason is that : 
Oracle first processes the left outer join, the intermediate result has a row: deptno=40 ename is null. When Oracle does inner join, this row  does not satisfy the inner join condition, therefore, it's ruled out from the final result.

In oracle null does not equal null, so even if there's a row with null ename, this row does not show up in the final result.

Here, we need a sub-query:

select dept.deptno, em.ename, em.comm 
from dept left outer join (
select emp.deptno, emp.ename, bonus.comm
from emp inner join bonus on emp.ename = bonus.ename 
order by emp.deptno nulls last) em
on dept.deptno=em.deptno;

    DEPTNO ENAME            COMM
---------- ---------- ----------
        10 az                 .1
        10 MILLER             .1
        10 KING               .1
        10 CLARK              .1
        20 JONES              .1
        20 SMITH              .1
        20 FORD               .1
        20 ADAMS              .1
        20 SCOTT              .1
        30 ALLEN              .1
        30 TURNER             .1
        30 JAMES              .1
        30 WARD               .1
        30 BLAKE              .1
        30 MARTIN             .1
        40

16 rows selected.







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-774572/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899680