Oracle SQL多表查询(二)
RCH DALLAS
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected
--SQL1999标准写法
SQL> select empno, ename, job, sal, dept.deptno, dname, loc
2 from emp
3 right join dept
4 on emp.deptno = dept.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
----- ---------- --------- --------- ------ -------------- -------------
7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS
7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS
7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS
7369 SMITH CLERK 800.00 20 RESEARCH DALLAS
7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
40 OPERATIONS BOSTON
15 rows selected
全连接
--SQL1999标准写法
SQL> select empno, ename, job, sal, d.deptno, dname, loc
2 from emp e
3 full join dept d
4 on e.deptno = d.deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
----- ---------- --------- --------- ------ -------------- -------------
7369 SMITH CLERK 800.00 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO
7521 WARD SALESMAN 1250.00 30 SALES CHICAGO
7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO
7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO
7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS
7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO
7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS
7900 JAMES CLERK 950.00 30 SALES CHICAGO
7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS
7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
15 rows selected
自连接
把表自身的镜像当成另外一个表
--oracle 的写法
SQL> select e.ename || ' works for ' || m.ename
2 from emp e, emp m
3 where e.empno = m.mgr;
E.ENAME||'WORKSFOR'||M.ENAME
-------------------------------
JONES works for FORD
JONES works for SCOTT
BLAKE works for TURNER
BLAKE works for ALLEN
BLAKE works for WARD
BLAKE works for JAMES
BLAKE works for MARTIN
CLARK works for MILLER
SCOTT works for ADAMS
KING works for BLAKE
KING works for JONES
KING works for CLARK
FORD works for SMITH
13 rows selected
--SQL1999标准的写法
SQL> select e.ename || ' works for ' || m.ename
2 from emp e
3 join emp m
4 on e.empno = m.mgr;
E.ENAME||'WORKSFOR'||M.ENAME
-------------------------------
JONES works for FORD
JONES works for SCOTT
BLAKE works for TURNER
BLAKE works for ALLEN
BLAKE works for WARD
BLAKE works for JAMES
BLAKE works for MARTIN
CLARK works for MILLER
SCOTT works for ADAMS
KING works for BLAKE
KING works for JONES
KING works for CLARK
FORD works for SMITH
13 rows selected
自然连接
以两个表具有相同的字段的所有列为基础，可采用自然连接（natural join)
它将选择两个表中那些在所有匹配的列中值相等的行。
如果列具有相同的名称，但数