本文通过具体的SQL查询语句展示了左外连接和内连接的不同效果，并解释了为何部分记录在进行内连接后会丢失。通过使用子查询的方法解决了这一问题，确保所有部门信息都能被正确展示。
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.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
