子查询
查询中有查询(查看select关键字的个数);
1、出现的位置
where后作为条件存在
from后作为表存在(多行多列)
# 查询本公司工资最高的员工的详细信息
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
SELECT e.empno,e.ename
FROM (SELECT * FROM emp WHERE deptno=30) e
WHERE 条件
2、条件
单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [= > < >= <= !=] (SELECT 列 FROM 表2 别名2 WHERE 条件);
多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN,ALL,ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件);
单行多列: SELECT * FROM 表1 别名1 WHERE (列1 列2) IN (SELECT 列 FROM 表2 别名2 WHERE 条件);
多行多列:SELECT * FROM 表1 别名1 , (SELECT…) 别名2 WHERE 条件);
练习:
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE deptno=20);
SELECT * FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE job='经理');
SELECT * FROM emp WHERE (job,deptno) IN (SELECT job,deptno FROM emp WHERE ename='殷天正');
SELECT * FROM emp WHERE (job,deptno,sal) IN (SELECT job,deptno,sal FROM emp WHERE ename='殷天正');