SELECT * FROM emp
WHERE (job,sal)=
(
SELECT job.sal FROM emp
WHERE ename='ALLEN'
)
IN(NOT IN):用户指定一个子查询的范围
SELECT * FROM emp
WHERE sal IN
(SELECT sal FROM emp
WHERE job='MANAGER')
但是在使用IN的时候要注意NOT IN的问题,如果使用NOT IN 操作,在子查询中,如果有一个内容是NULL,则不会查询出任何的结果
ANY:与每一个内容相匹配,有三种匹配模式
=ANY:与IN操作是一样的
SELECT * FROM emp
WHERE sal =ANY
(SELECT sal FROM emp
WHERE job='MANAGER')
>ANY:比子查询中返回记录最小的还要大的数据
SELECT * FROM emp
WHERE sal >ANY
(SELECT sal FROM emp
WHERE job='MANAGER')
<ANY:比子查询中返回记录的最大的还要小
SELECT * FROM emp
WHERE sal <ANY
(SELECT sal FROM emp
WHERE job='MANAGER')
ALL:与第一个内容相匹配,有两种匹配形式:
>ALL:比子查询里返回的记录还要大
SELECT * FROM emp
WHERE sal >ALL
(SELECT sal FROM emp
WHERE job='MANAGER')
<ALL:比子查询里返回的最小的记录还要小
SELECT * FROM emp
WHERE sal <ALL
(SELECT sal FROM emp
WHERE job='MANAGER')
FROM 子查询
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d,
(SELECT deptno dno,COUNT(empno) count,AVG(sal) avg
FROM emp
GROUP BY deptno) temp
WHERE d.deptno=temp.deno(+)