(04)oracle新人笔记------多表连接

多表查询（等值连接）

SELECT  emp.empno,  emp.ename,  emp.deptno,  dept.deptno,   dept.loc

FROM  emp,dept

WHERE  empno.deptno=dept.deptno  AND  loc=’NEW YORK’;

 

表起别名

SELECT  e.empno,  e.ename,  e.deptno,  d.deptno,   d.loc

FROM  emp e  ,  dept d

WHERE  e.deptno=d.deptno;

 

非等值连接的数据检索

SELECT e.ename,e.sal,s.grade

FROM emp e,salgrade s

WHERE e.sal BETWEEN s.losal AND s.hisal;

 

外部连接

加号相当于万能行，这个行全部由空值组成。它可以和另一边表中所有不满足连接条件的行连接

SELECT e.ename,d.deptno,d.dname

FROM emp e,dept d

WHERE e.deptno(+) =d.deptno     

ORDER BY e.deptno;              

 

自身连接

SELECT worker.ename||'leader is'||manager.ename

FROM emp worker,emp manager

WHERE worker.mgr=manager.empno;

 

交叉连接 (没用)

SELECT emp.empno,emp.ename,dept.loc

FROM emp

CROSS JOIN dept;

 

自然连接

对两表之间相同名字和数据类型的列进行的等值连接

SELECT empno,ename,sal,deptno,loc

FROM emp

NATURAL JOIN dept;

 

USING子句

//和自然连接差不多，不过这个是指定列，范围更细

//NATURAL JOIN子句和USING子句相互排斥，不能同时使用

SELECT empno,ename,sal,deptno,loc

FROM emp JOIN dept USING(deptno)

WHERE deptno=20;

 

ON子句

//指定任意连接条件，或指定任意要连接的列

查询员工的姓名、编号、地址、主管姓名

SELECT e.empno,e.ename,d.loc,m.ename

FROM emp e

JOIN dept d

ON (e.deptno=d.deptno)

JOIN emp m

ON e.mgr=m.empno;

 

 

左外连接

//左边为基本表，右边为空的还是会显示出来，作用和（+）差不多

SELECT e.ename,e.deptno,d.loc

FROM emp e

LEFT OUTER JOIN dept d

ON (e.deptno=d.deptno);

 

右外连接

SELECT e.ename,e.deptno,d.loc

FROM emp e

RIGHT OUTER JOIN dept d

ON (e.deptno=d.deptno);

 

全外连接

SELECT e.ename,e.deptno,d.loc

FROM emp e

FULL OUTER JOIN dept d

ON (e.deptno=d.deptno);

 

 

 

 

 

 

练习

1. SELECTename,dept.deptno,dept.dname FROMemp, dept;
2. SELECTename,dept.deptno,dept.dname FROMemp, dept;
3. SELECTename,dept.loc,emp.comm FROM emp,dept WHERE dept.loc!='CHICAGO' AND emp.comm  IS NOT NULL;
4. SELECT* FROM emp,dept;
5. SELECTename,dname,hiredate

    FROM emp

    NATURAL JOIN dept

    WHERE hiredate>'01-5月-80';

 

6. SELECTename,dname,manager.ename

FROM emp worker,emp manager,dept

WHERE dept.deptno=worker.deptno

AND worker.mgr=manager.empno

AND worker.ename='SMITH';

 

7. SELECTename,dname,sal,grade

FROM emp,dept,salgrade

WHERE emp.deptno=dept.deptno

AND  emp.sal BETWEEN salgrade.losal AND salgrade.hisal

AND grade>4;

 

8. SELECTename,m.ename

FROM emp w,emp m

WHERE w.mgr=m.empno

AND (m.ename='KING' OR m.ename='FORD');

 

9. SELECTename worker_name,w.hiredate,m.ename manager_name,m.hiredate

FROM emp w,emp m

WHERE w.mgr=m.empno

AND w.hiredate<m.hiredate;