多表查询

做一个简单的多表查询：

 • 查询emp表和dept表

  Emp表一共有14条记录

  Dept表一共有4条记录

SELECT * FROM emp,dept ;如果直接执行此语句，会发现产生了56条记录

 其中肯定存在大量的重复内容，此内容称为笛卡尔积（表1的数据量＊表2的数据量）

那么该如何消除掉笛卡尔积呢？

 消除的方法就是找两张表的关联字段。

 Emp表中有一个deptno字段

 Dept表中有一个deptno字段

此时标准的多表查询语句就必须将两个关联字段进行联系

 SELECT * 

FROM emp,dept

WHERE emp.deptno=dept.deptno ;  去除笛卡尔积

有些时候如果表名称过长，则也可以为表名称起一个别名，之后使用此别名进行查询。

要求：

 查询出雇员的姓名、雇员编号、部门名称、部门位置、职务。

SELECT e.empno,e.ename,e.job,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno ;

思考：

 要求查询出每个雇员的编号、姓名、工资，及每个员工工资对应的等级

 • emp表和salgrade表关联

 • 关联字段是emp中的sal和salgrade的losal及hisal

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

FROM emp e,salgrade s

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

进一步思考：

 现在要求根据工资的等级进行以下的显示；

• 如果工资等级为1则显示为E等工资

• 如果工资等级为2则显示为D等工资

• 如果工资等级为3则显示为C等工资

• 如果工资等级为4则显示为B等工资

• 如果工资等级为5则显示为A等工资

要使用decode完成

SELECT e.empno,e.ename,e.sal,

decode(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资') 工资等级

FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal ;

 查询出每个雇员的姓名、职务及其直接上级的姓名、工资和职务

 Emp表要自己关联自己吧，所以此种关联称为自身连接。

 SELECT e.ename,e.job,m.ename 

FROM emp e,emp m

WHERE e.mgr=m.empno ;

观察以下的代码：

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

WHERE e.deptno=d.deptno;

发现部门一共有四个：10、20、30、40，但是以上的查询中只出现3个部门

此时就需要进行外连接

 • 左外连接：

WHERE e.deptno=d.deptno(+);

 • 右外连接：

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

WHERE e.deptno(+)=d.deptno;

  所以此时40部门出现了

 • (+)在等号的左边表示右连接

 • (+)在等号的右边表示左连接

表连接的基本操作完了，但是除了这些之外SQL:1999语法中又定义了另外一套实现效果一样的语法。（了解）

CROSS JOIN表示返回笛卡尔积

FROM emp CROSS JOIN dept ;

NATURAL JOIN：根据两个表的关联字段自动进行匹配

SELECT * 

FROM emp NATURAL JOIN dept ;

USING子句：指定要产生连接的列

FROM emp JOIN dept USING(deptno) ;

 限制：要求emp表和dept表中都必须同时存在deptno列，列的名字不能不一致。

ON子句：指定要进行连接匹配的列

FROM emp e JOIN dept d ON(e.deptno=d.deptno) ;

右外连接：

FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno) ; 

左外连接：

FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno) ;

本文转自 李兴华 51CTO博客，原文链接：http://blog.51cto.com/lixinghua/91210，如需转载请自行联系原作者