1.子表在WHERE里
--工资高于平均工资SELECT ename,salFROM empWHERE sal > (SELECT avg(sal) FROM emp);
2.子表是分组后的数据
--每组工资最高的人SELECT emp.ename,emp.deptno,emp.empnoFROM empJOIN (SELECT max(sal) m_s,deptno m_d FROM emp GROUP BY deptno) maxON (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_dFROM salgradeJOIN (SELECT avg(sal) a_s,deptno a_d FROM emp GROUP BY deptno)avgON (avg.a_s BETWEEN salgrade.losal AND salgrade.hisal);--用WHERE方法,1：SELECT e1.empno ,e1.ename ,e1.mgr ,e2.enameFROM emp e1,emp e2WHERE e1.mgr = e2.empno AND e1.empno = 7369 ;--WHERE方法，2：避免笛卡尔积SELECT * FROM emp,deptWHERE (emp.deptno = dept.deptno) AND (emp.empno = 7902);--JOIN_ON_USINGSELECT * FROM empJOIN deptON emp.deptno = dept.deptno --或不用ON，用USING deptnoWHERE emp.empno = 7902;--三张表连接,多个JOINSELECT ename,dname,gradeFROM empJOIN 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.enameFROM emp e1 LEFT JOIN emp e2 --RIGHT 或者 FULLON e1.mgr = e2.empno;