SQL回炉之二：多表连接

1.子表在WHERE里

--工资高于平均工资
SELECT ename,sal
FROM emp
WHERE sal > (SELECT avg(sal) FROM emp);

2.子表是分组后的数据

--每组工资最高的人
SELECT emp.ename,emp.deptno,emp.empno
FROM emp
JOIN (SELECT max(sal) m_s,deptno m_d FROM emp GROUP BY deptno) max
ON (emp.sal = max.m_s AND emp.deptno = max.m_d);

--下边这种是错的，因为凡是等于此值得就会筛选出来，不论在哪组
--SELECT ename,deptno,empno
--FROM emp,
--(SELECT max(sal) max_sal FROM emp GROUP BY deptno)max
--WHERE sal IN max_sal;

3.用JOIN_ON 代替多表连接时WHERE的指定

SQL99的标准这个原来没学过，目的是把连接语句从WHERE中剔除
WHERE中只有数据过滤

--各部门平均工资等级
SELECT grade,avg.a_d
FROM salgrade
JOIN (SELECT avg(sal) a_s,deptno a_d FROM emp GROUP BY deptno)avg
ON (avg.a_s BETWEEN salgrade.losal AND salgrade.hisal);

--用WHERE方法,1：
SELECT e1.empno ,e1.ename ,e1.mgr ,e2.ename
FROM emp e1,emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369 ;

--WHERE方法，2：避免笛卡尔积
SELECT * 
FROM emp,dept
WHERE (emp.deptno = dept.deptno) AND (emp.empno = 7902);

--JOIN_ON_USING
SELECT * 
FROM emp
JOIN dept
ON emp.deptno = dept.deptno --或不用ON，用USING deptno
WHERE emp.empno = 7902;

--三张表连接,多个JOIN
SELECT ename,dname,grade
FROM emp
JOIN dept ON (emp.deptno = dept.deptno)
JOIN salgrade ON (emp.sal BETWEEN salgrade.losal and salgrade.hisal)
WHERE emp.empno < 10000;

4.外连接(左、右、全)

连接时用LEFT JOIN、RIGHT JOIN或者FULL JOIN，
把左边、右边的表的独有项目显示出来

SELECT e1.ename,e2.ename
FROM emp e1 
LEFT JOIN emp e2 --RIGHT 或者 FULL
ON e1.mgr = e2.empno;