DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> select deptno,empno,ename from emp;
DEPTNO EMPNO ENAME ------ ----- ---------- 20 7369 SMITH 30 7499 ALLEN 30 7521 WARD 20 7566 JONES 30 7654 MARTIN 30 7698 BLAKE 10 7782 CLARK 20 7788 SCOTT 10 7839 KING 30 7844 TURNER 20 7876 ADAMS 30 7900 JAMES 20 7902 FORD 10 7934 MILLER
14 rows selected
SQL> select d.deptno,d.dname,e.empno,e.ename from dept d left join emp e on d.deptno=e.deptno;
DEPTNO DNAME EMPNO ENAME ------ -------------- ----- ---------- 20 RESEARCH 7369 SMITH 30 SALES 7499 ALLEN 30 SALES 7521 WARD 20 RESEARCH 7566 JONES 30 SALES 7654 MARTIN 30 SALES 7698 BLAKE 10 ACCOUNTING 7782 CLARK 20 RESEARCH 7788 SCOTT 10 ACCOUNTING 7839 KING 30 SALES 7844 TURNER 20 RESEARCH 7876 ADAMS 30 SALES 7900 JAMES 20 RESEARCH 7902 FORD 10 ACCOUNTING 7934 MILLER 40 OPERATIONS
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select deptno,empno,ename from emp;
DEPTNO EMPNO ENAME
------ ----- ----------
20 7369 SMITH
30 7499 ALLEN
30 7521 WARD
20 7566 JONES
30 7654 MARTIN
30 7698 BLAKE
10 7782 CLARK
20 7788 SCOTT
10 7839 KING
30 7844 TURNER
20 7876 ADAMS
30 7900 JAMES
20 7902 FORD
10 7934 MILLER
14 rows selected
SQL> select d.deptno,d.dname,e.empno,e.ename from dept d left join emp e on d.deptno=e.deptno;
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
40 OPERATIONS
15 rows selected
SQL> --注意上面的结果中部门号deptno=40的记录在emp中并没有对应的记录
SQL> --但通过左联结可以显示出来,自己体会下
1.查询有效的保单,left join cust_temp_ply_base parents on base.c_ply_no = parents.c_ply_no这段我想可以去掉。
2.查询和有效保单关联的3个历史保单。比如说某人1年交一次保单,4年来共产生4条保单,而这些保单可以从last_ply_no获得关联。其实可以改写成用connect by语句可能更清晰些。