--举个例子,查找存在工资大于4000的员工的部门 select d.deptno,d.dname from dept d where exists (select 1 from emp e where e.deptno = d.deptno and sal > 4000); --理解exists查询的关键是只要子查询(红色部分)中返回有记录,则该条件为真--下面这个语句不使用exists与上面的等价 select distinct d.deptno, d.dname from dept d, emp e where d.deptno = e.deptno and e.sal > 4000;
上一回复显示红色标记有误--举个例子,查找存在工资大于4000的员工的部门 select d.deptno,d.dname from dept d where exists (select 1 from emp e where e.deptno = d.deptno and sal > 4000); --理解exists查询的关键是只要子查询(括号里的部分)中返回有记录,则该条件为真--下面这个语句不使用exists与上面的等价 select distinct d.deptno, d.dname from dept d, emp e where d.deptno = e.deptno and e.sal > 4000;
exists not exists是标准sql,那个数据库都一样
1、exists的使用: Exists用于只能用于子查询,可以替代in,若匹配到结果,则退出内部查询,并将条件标志为true,传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕,使用exists可以将子查询结果定为常量,不影响查询效果,而且效率高。 In和exists对比: 若子查询结果集比较小,优先使用in,若外层查询比子查询小,优先使用exists。因为若用in,则oracle会优先查询子查询,然后匹配外层查询,若使用exists,则oracle会优先查询外层表,然后再与内层表匹配。最优化匹配原则,拿最小记录匹配大记录。 例子: 使用in select last_name, title from s_emp where dept_id in (select id from s_dept where name=’Sales’); 使用exists select last_name,title from s_emp e where exists (select ‘x’ –把查询结果定为constant,提高效率 from s_dept s where s.id=e.dept_id and s.name=’Sales’); 2、not exists的使用 与exists 含义相反,也在子查询中使用,用于替代not in。其他一样。如查询不在销售部的员工姓名 select last_name,title from s_emp e where not exists (select ‘x’ –把查询结果定为constant,提高效率 from s_dept s where s.id=e.dept_id and s.name=’Sales’);
先感谢tangren你的回贴,你很仔细周到,你的例子很好,谢谢,我这里有一个问题:--下面这个语句不使用exists与上面的等价 select distinct d.deptno, d.dname from dept d, emp e where d.deptno = e.deptno and e.sal > 4000; 这里distinct有什么用呢?除掉也一样的。我运行了
distinct 去除重复行,如果查出来本变没有当然去掉也一样了
create table t_name(name varchar2(30)); insert into t_name values('zeng'); commit; select name from t_name where not exists(select 1 from dual where 1=0); select name from t_name where not exists(select 1 from dual where 1=1); select name from t_name where exists(select 1 from tt_name where 1=1); select name from t_name where exists(select 1 from tt_name where 1=0); 挺好玩的,自己试下就明白了
Hemes_MC这位兄台oracle不错吧,以后多多向你学习
Hemes_MC这位兄台上面的回复如何理解exists这个例子不错,谢谢
--EXISTS select id from (select 1 as id from dual) t where exists (select * from (select 1 as id from dual union all select 2 as id from dual) tt where t.id = tt.id)--用INselect id from (select 1 as id from dual) t where t.id in (select 1 as id from dual union all select 2 as id from dual)--用ANY select id from (select 1 as id from dual) t where t.id =any (select 1 as id from dual union all select 2 as id from dual)--用SOME select id from (select 1 as id from dual) t where t.id =some(select 1 as id from dual union all select 2 as id from dual)--还有ALL不喜欢写了...GAGA
--举个例子,查找存在工资大于4000的员工的部门
select d.deptno,d.dname
from dept d
where exists (select 1
from emp e
where e.deptno = d.deptno
and sal > 4000);
--理解exists查询的关键是只要子查询(红色部分)中返回有记录,则该条件为真--下面这个语句不使用exists与上面的等价
select distinct d.deptno, d.dname
from dept d, emp e
where d.deptno = e.deptno
and e.sal > 4000;
select d.deptno,d.dname
from dept d
where exists (select 1
from emp e
where e.deptno = d.deptno
and sal > 4000);
--理解exists查询的关键是只要子查询(括号里的部分)中返回有记录,则该条件为真--下面这个语句不使用exists与上面的等价
select distinct d.deptno, d.dname
from dept d, emp e
where d.deptno = e.deptno
and e.sal > 4000;
Exists用于只能用于子查询,可以替代in,若匹配到结果,则退出内部查询,并将条件标志为true,传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕,使用exists可以将子查询结果定为常量,不影响查询效果,而且效率高。
In和exists对比:
若子查询结果集比较小,优先使用in,若外层查询比子查询小,优先使用exists。因为若用in,则oracle会优先查询子查询,然后匹配外层查询,若使用exists,则oracle会优先查询外层表,然后再与内层表匹配。最优化匹配原则,拿最小记录匹配大记录。 例子:
使用in
select last_name, title
from s_emp
where dept_id in
(select id
from s_dept
where name=’Sales’);
使用exists
select last_name,title
from s_emp e
where exists
(select ‘x’ –把查询结果定为constant,提高效率
from s_dept s where s.id=e.dept_id and s.name=’Sales’);
2、not exists的使用
与exists 含义相反,也在子查询中使用,用于替代not in。其他一样。如查询不在销售部的员工姓名
select last_name,title
from s_emp e
where not exists
(select ‘x’ –把查询结果定为constant,提高效率
from s_dept s where s.id=e.dept_id and s.name=’Sales’);
select distinct d.deptno, d.dname
from dept d, emp e
where d.deptno = e.deptno
and e.sal > 4000;
这里distinct有什么用呢?除掉也一样的。我运行了
commit; select name
from t_name
where not exists(select 1 from dual where 1=0); select name
from t_name
where not exists(select 1 from dual where 1=1); select name
from t_name
where exists(select 1 from tt_name where 1=1); select name
from t_name
where exists(select 1 from tt_name where 1=0); 挺好玩的,自己试下就明白了
--EXISTS
select id
from (select 1 as id from dual) t
where exists (select *
from (select 1 as id
from dual
union all
select 2 as id from dual) tt
where t.id = tt.id)--用INselect id
from (select 1 as id from dual) t
where t.id in (select 1 as id
from dual
union all
select 2 as id from dual)--用ANY
select id
from (select 1 as id from dual) t
where t.id =any (select 1 as id
from dual
union all
select 2 as id from dual)--用SOME select id
from (select 1 as id from dual) t
where t.id =some(select 1 as id
from dual
union all
select 2 as id from dual)--还有ALL不喜欢写了...GAGA
union 并集MINUS Intersect