1.子查询用小括号括起来
SELECT ename FROM emp
WHERE
(SELECT SAL
FROM emp
WHERE ENAME='jack') < sal
2.WHERE子句中的子查询,子查询可以包括分组函数
子查询可以在WHERE HAVING FROM UPDATE的SET
分组函数可以在 SELECT HAVING GROUP BY
SELECT ename
FROM emp
WHERE (SELECT SAL FROM emp WHERE ENAME='jack') < sal
SELECT ename,empno,sal
FROM emp
WHERE sal=(SELECT MIN(SAL) FROM emp )
关系运算和单行函数不能包含返回多行结果的子查询语句
SELECT ename,empno from emp
WHERE sal=(SELECT MIN(sal)
FROM emp
GROUP BY deptno)
3.多行字查询运算符
IN的使用
查询每个部门工资最低的员工姓名,员工编号,员工薪资
SELECT ename,empno,sal from emp
WHERE sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)
ANY 的使用 只要满足结果中任意一行即可查询大于所有部门最低工资的员工姓名,员工编号,员工薪资
SELECT MIN(sal) FROM emp
GROUP BY deptno
SELECT ename,empno,sal
FROM emp
WHERE sal >ANY (SELECT MIN(sal)
FROM emp
GROUP BY deptno)
ALL 的使用 满足结果中所有行
SELECT MIN(sal) FROM emp GROUP BY deptno
SELECT ename,empno,sal from emp where sal > ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)
4.相关子查询
SELECT *
FROM emp,dept
WHERE emp.deptno=dept.deptno
SELECT dname,(SELECT count(empno)
FROM emp
WHERE emp.deptno=dept.deptno
GROUP BY emp.deptno )
FROM dept