817
Oracle中的in和exsits的用法
一、基本概念
in
用來判斷某值是否出現在一個特定的集合中。若出現則返回true，否則返回false。
exists
先對where前的主查詢進行查詢，然後將每一個主查詢的結果逐行代入exists查詢進行判斷。若滿足條件則輸出當前這一條主查詢的結果，否則不輸出。
二、實例（以scott用戶下的表為例）
-- 財務部或銷售部的員工
select * from emp e where e.deptno in(select deptno from dept d where d.dname ='ACCOUNTING' or d.dname = 'SALES')
select * from emp e where exists
(select 1 from dept d where (d.dname ='ACCOUNTING' or d.dname = 'SALES') and d.deptno = e.deptno)
-- 不是財務部或銷售部的員工
select * from emp e where e.deptno not in
(select deptno from dept d where d.dname ='ACCOUNTING' or d.dname = 'SALES')
select * from emp e where not exists
(select 1 from dept d where (d.dname ='ACCOUNTING' or d.dname = 'SALES') and d.deptno = e.deptno)
三 、exists詳解
表A
ID NAME1 A1
2 A2
3 A3
表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID)
執行結果
1 A12 A2
SELECT * FROM A WHERENOT EXIST (SELECT * FROM B WHERE A.ID = B.AID)
執行結果
3 A3
參考地址：https://www.cnblogs.com/highriver/archive/2011/05/30/2063461.html
最後更新：2017-04-04 07:03:41