多表查询(5)

 

• 多表查询的基本语法　

SELECT {DISTINCT} * | 查询列 1 别名 1,查询列 2 别名 2,…
FROM 表名称 1 别名 1 , 表名称 2 别名 2 ,…
{WHERE 条件(s)}
{ORDER BY 排序字段 ASC|DESC , 排序字段 ACS | DESC ,…}

范例：下面使用了多表查询，同时查询 emp 和 dept 表

SELECT * FROM emp,dept ;

以上的查询使用了两张表进行同时查询。但是，从查询结果上可以发现，返回的数据是 56 条，但是 emp 表一共才 14 条，dept 表一共才 4 条。

　　

56 条记录 = emp 表的 14 条记录 * dept 表的 4 条记录。在使用多表查询的时候会产生笛卡尔积。如果表的数据越多，那么笛卡尔积就会越大。如果现在假设有 5 张表，每张表有 10000 条记录，笛卡尔积10000的5次方 。所以多表查询在开发中基本上是不建议过多的使用。要想去掉笛卡尔积则必须使用字段进行关联的操作。

emp 中的 deptno = dept 中的 deptno，属于关联字段。在多表查询中加入 WHERE 语句，就可以消除掉笛卡尔积。

范例：修改之前的查询操作

SELECT * FROM emp,dept
WHERE emp.deptno=dept.deptno ;

　　

范例：要求查询出每个雇员的姓名、工作、雇员的直接上级领导的姓名

　　

　　在 emp 表中的 mgr 字段使用没有使用过，其表示一个雇员的上级领导的编号。那么如果现在要想查询一个雇员的上级领导，则肯定要将 emp 表与 emp 表自己进行关联。

SELECT e.ename,e.job,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno ;

进一步思考：现在要求按照以下的样式显示工资等级
· 1：第五等工资
· 2：第四等工资
· 3：第三等工资
· 4：第二等工资
· 5：第一等工资
此时肯定只能使用 DECODE()函数

SELECT e.ename,e.sal,d.dname,DECODE(s.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资'),m.ename,m.sal,DECODE(ms.grade,1,'第五等工资',2,'第四等工资',3,'第三等工资',4,'第二等工资',5,'第一等工资')
FROM emp e,dept d,salgrade s,emp m,salgrade ms
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.mgr=m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;

　　

•  左、右连接

现在将 emp 和 dept 表关联查询，查询一下指定的字段

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno ;

　　

部门表中一共存在四个部门的信息，但是此时只列出了 3 个。因为在雇员表中并没有指定 40 部门的雇员。

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno ;

　　

可以发现，此时 40 部门已经出现了。所以，此时就使用到了右连接，证明以下的规律：
· (+)在=左边表示右连接
· (+)在=右边表示左连接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno(+) ;

以上的（+）写与不写都是一样的，肯定是无法显示 40 部门信息的。

---

 

多表查询：一张以上的表进行查询，称为多表查询，多表查询的时候可以为表指定别名的方式以简化查询列的编写，在多表查询中，会产生笛卡尔积，就是两张表的总数相乘得到的结果，如果要想消除笛卡尔积需要通过关联条件。