select ENAME
from EMP
where salary = some (select salary from EMP where EMPNO = 30) and deptno <> 30
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。
in 的子查询可以先执行,因为子查询和外部查询无关(无关子查询) exists 先执行外部查询,因为子查询内部与外部查询可能有关(比如where条件中使用外部查询的别名) – 注意:只要子查询能独立执行就是不相关。 – 外查询表中有多少行,就执行多少次exists,每一行都去测试。 – in 是子查询结束,进行筛选。
select name
from student as s
where not exists (select * --exists子查询的select后面属性可以随便写,因为exists只判断是否符合条件。
from takes as t
where s.ID=t.ID and course_id='cd-347')
-- 注意:not exists 不会受表中数据存在NULL的影响。
生物系的课 - 学生选的课 = 空,即子查询不会返回行。
– 判断条件: not exists (生物系的课 except 学生选的课)
select DEPTNO
from EMP
where not exists (select distinct job
from EMP
where MGR is not NULL) --所有除了总经理的职位
except
(select distinct job
from EMP a
where emp.deptno = a.deptno)--减去部门包含的职位
-- 减去部门包含的职位这里,外面的emp.deptno = 里面a.deptno 说明子查询的职位对应部门,正在外部查询的部门
select 里面可以相关,不相关子查询 from 不相关子查询 where 相关,不相关,标量子查询
可以出现在任何 值出现的地方:值运算符
with 表名(属性名1,属性名2) as 表
with 表1
select 属性
from 表2
where 小表和大表的条件
with 表1
由表1定义的表2
from 表1,表2
where 表1和表2条件
-- from写法:
select ename
from emp a,(select AVG(sal) avgs,deptno
from emp
group by deptno) as b
where a.DEPTNO=b.DEPTNO and a.sal>b.avgs
-- with写法:
with avg_SAL(avgs,deptno) as (select avg(sal),deptno
from EMP
group by DEPTNO)
select ENAME
from EMP a,avg_SAL --这里EMP记为a和上面子查询区分开。
where a.DEPTNO = avg_SAL.DEPTNO and a.SAL > avg_SAL.avgs
-- where写法:
select ename
from emp a
where a.sal >(select avg(sal)
from emp b
where a.DEPTNO=b.DEPTNO)
select empno,sal-(select avg(sal) from emp),(count(*) from emp b where a.deptno=b.deptno)
from emp a