《SQL经典实例》：5. 多表连接查询

《SQL经典实例》：5. 多表连接查询

         目录

多表连接

合并行Union all/union

合并列：1. 全连接

合并列：2. INNER JOIN

合并列：3. LEFT JOIN

合并列：4. RIGHT JOIN

多于两个表的连接

综合应用

---

多表连接 合并行Union all/union

SELECt 列表里的所有项目必须保持数目相同，且数据类型匹配(所以select T1时要加个null)

SELECT ename AS ename_and_dname, deptno
FROM emp
WHERe deptno = 10
UNIOn ALL
SELECt '------------', null
FROM T1
UNIOn ALL
SELECt dname, deptno
FROM dept;

大体而言，使用 UNIOn 等同于针对 UNIOn ALL 的输出结果再执行一次DISTINCT *** 作。

除非有必要，否则不要在查询中使用 DISTINCT *** 作，同样的规则也适用于 UNIOn。除非有必要，否则不要用 UNIOn 代替 UNIOn ALL

合并列：1. 全连接

想得到(deptno=10时的员工姓名ename和部门地点loc两列)

FROM table1， table2 全连接将得到（表1行数*表2行数）行

SELECt e.ename, d.loc
FROM emp e, dept d
WHERe e.deptno = d.deptno
AND e.deptno = 10;
-- 想得到(deptno=10时的员工姓名ename和部门地点loc两列)

合并列：2. INNER JOIN

同上，想得到(deptno=10时的员工姓名ename和部门地点loc两列)

INNER JOIN只保留两张表中完全匹配的结果集。而外连接查询(LEFT/RIGHT JOIN)会返回一个表中的所有行，以及另一个表中与之匹配的行。

SELECt e.ename, d.loc
FROM emp e
JOIN dept d
USING (deptno)
WHERe e.deptno = 10;

合并列：3. LEFT JOIN

想得到（dept表中有，但emp表中没有的deptno）所在的dept表的那一行

LEFT JOIN将保留左表中所有记录（dept表）

SELECt d.*
FROM dept d
LEFT JOIN emp e
USING (deptno)
WHERe e.deptno IS NULL;

合并列：4. RIGHT JOIN

同上，想得到（dept表中有，但emp表中没有的deptno）所在的dept表的那一行

RIGHT JOIN将保留右表中所有记录（dept表）

SELECt d.*
FROM emp e 
RIGHT JOIN dept d
USING (deptno)
WHERe e.deptno IS NULL;

多于两个表的连接

想得到（匹配dept表和emp表得到员工姓名和对应地点，再增加一列得到收到奖金的日期(若有奖金的话)）

SELECt e.ename, d.loc, eb.recieved
FROM emp e
JOIN dept d
ON (e.deptno = d.deptno)
LEFT JOIN emp_bonus eb
ON (e.empno = eb.empno)
ORDER BY d.loc;

注意：三个表两两连接时不能用USING了，要用ON说明哪两个表有共同元素

综合应用: 得到

想得到(视图v3和emp表中的不同元素：包括v3有但emp没有的+emp有但v3没有的)

将这两部分union all起来就可以了，union all上面为emp中有但v3中没有的；下面相反。二者逻辑一模一样。注意WHERe罗列的那些条件，用的是两个内嵌函数的名字，而不是表的名字。

SELECt * FROM 
(SELECt *, count(*) AS cnt
FROM emp e
GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno) e
WHERe NOT EXISTS
(SELECt *
FROM(
SELECt *, count(*) AS cnt
FROM v3
GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno) v0
WHERe v0.empno = e.empno
AND v0.ename = e.ename 
AND v0.job = e.job
AND v0.mgr = e.mgr
AND v0.hiredate = e.hiredate
AND v0.sal = e.sal
AND v0.deptno = e.deptno
AND v0.cnt = e.cnt
AND coalesce(v0.comm, 0) = coalesce(e.comm, 0)  
) 
UNIOn ALL
SELECt * FROM
(SELECt *, count(*) AS cnt
FROM v3
GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno) v0
WHERe NOT EXISTS
(SELECt * FROM
(SELECt *, count(*) AS cnt
FROM emp e
GROUP BY empno, ename, job, mgr, hiredate, sal, comm, deptno) e
WHERe v0.empno = e.empno
AND v0.ename = e.ename 
AND v0.job = e.job
AND v0.mgr = e.mgr
AND v0.hiredate = e.hiredate
AND v0.sal = e.sal
AND v0.deptno = e.deptno
AND v0.cnt = e.cnt
AND coalesce(v0.comm, 0) = coalesce(e.comm, 0)  
) 
ORDER BY deptno DESC;