exists和in 的使用

一、exists 字面意思存在。 

  EXISTS里的子查询结果集非空，EXISTS()子句的值就是true。 
  EXISTS里的子查询结果集为空，EXISTS()子句的值就是false。 

  SELECT * FROM SCOTT.EMP WHERE EXISTS(SELECT SYSDATE FROM DUAL); 
  此句将查出scott.emp表所有内容. 

  SELECT * FROM SCOTT.EMP WHERE EXISTS(SELECT * FROM scott.salgrade where 1=2) ; 

  返回空结果集 

论述： 
   1.带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 
   2.使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 
   3.由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值， 
   给出列名无实际意义。

语法 ：
EXISTS subquery 
参数: subquery ,是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)
结果类型:Boolean 
结果值:如果子查询包含行，则返回 TRUE。 

示例:
在子查询中使用 NULL 仍然返回结果集 

这个例子在子查询中指定 NULL，并返回结果集，通过使用 EXISTS 仍取值为 TRUE。 
USE Northwind 
GO 
SELECT CategoryName 
FROM Categories 
WHERE EXISTS (SELECT NULL) 
ORDER BY CategoryName ASC 
GO 

二、用exists的相关子查询 
     
      明白相关子查询： 
      子查询的查询条件依赖于外层父查询的某个属性值，称这类查询为相关子查询。 
      求解相关子查询不能像求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。 
      内层查询由于与外层查询有关，因此必须反复求值。 
       
       弄清相关子查询处理过程： 
       从概念上讲，相关查询的一般处理过程是： 
       首先去外层查询中表的第1个元组，根据它与内层查询相关的属性值处理内层查询， 
       若WHERE子句返回值为真，则取此元组放入结果表；然后再取表的下一个元组； 
       重复这个过程直到外层表全部检查完为止。 


  SQL> select a.ENAME  from  scott.emp a where exists(select * from  scott.dept b where b.deptno=a.deptno); 
  ENAME 
  ---------- 
  SMITH 
  ALLEN 
  WARD 
  JONES 
  MARTIN 
  BLAKE 
  CLARK 
  SCOTT 
  KING 
  TURNER 
  ADAMS 
  JAMES 
  FORD 
  MILLER 

  查询到14记录. 


  相当于下sql: 
  select a.ENAME  from  scott.emp a  where a.deptno in (select b.deptno  from  scott.dept b ); 

exists子查询实际是通过关联别的表按某种条件缩小主查询的范围。 



三、not exists    简单的理解就是 not exists= not in 
      实际not  exists  取得是不属于exists限制条件的主表的数据集  

SQL>  select a.ENAME  from  scott.emp a 
             where exists(select * from  scott.dept b where b.deptno=a.deptno and b.deptno=10); 

ENAME 
---------- 
CLARK 
KING 
MILLER 


     select a.ENAME  from  scott.emp a 
      where not exists(select * from  scott.dept b where b.deptno=a.deptno and b.deptno=10) 


  ENAME 
  ---------- 
  SMITH 
  ALLEN 
  WARD 
  JONES 
  MARTIN 
  BLAKE 
  SCOTT 
  TURNER 
  ADAMS 
  JAMES 
  FORD 


  查询到11记录. 



4 、exists和in 


      in子句通常用在不相关子查询中。通常先执行子查询，将子查询的结构用于父查询。 
      子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。 
      
      关键字: 在Oracle SQL中取数据时in 和 exists 的区别？ 
  在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢？ 


  1. 性能上的比较 
  比如Select * from T1 where x in ( select y from T2 ) 
  执行的过程相当于: 
  select * 
    from t1, ( select distinct y from t2 ) t2 
  where t1.x = t2.y; 

  相对的 

  select * from t1 where exists ( select null from t2 where y = x ) 
  执行的过程相当于: 
  for x in ( select * from t1 ) 
     loop 
        if ( exists ( select null from t2 where y = x.x ) ) 
        then 
    OUTPUT THE RECORD 
        end if 
  end loop 
  表 T1 不可避免的要被完全扫描一遍 


  分别适用在什么情况? 
  以子查询 ( select y from T2 )为考虑方向，如果子查询的结果集很大需要消耗很多时间， 
  但是T1比较小执行( select null from t2 where y = x.x )非常快，那么exists就比较适合用在这里。 
  相对应得子查询的结果集比较小的时候就应该使用in. 

EXISTS的作用指定一个子查询，检测行的存在，而select null 这句话就是返回带null值的行，所以说EXISTS永远返回一个true值，这里的select null并不查询数据库，只是返回带null值的行。

如： select null from emp;

null表示空。其实就是什么都不查，但行数和emp表行数相同。

有两个简单例子，以说明 “exists”和“in”的效率问题

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

exists 用法：

请注意 1）句中的有颜色字体的部分 ，理解其含义；

其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询，相当于

“select 1 from T1,T2     where T1.a=T2.a”

但是，如果你当当执行 1） 句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

因此“select 1”这里的 “1”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1） 句的where 条件成立。

in 的用法：

继续引用上面的例子

“2) select * from T1 where T1.a in (select T2.a from T2) ”

这里的“in”后面括号里的语句搜索出来的字段的内容一定要相对应，一般来说，T1和T2这两个表的a字段表达的意义应该是一样的，否则这样查没什么意义。

打个比方：T1，T2表都有一个字段，表示工单号，但是T1表示工单号的字段名叫“ticketid”，T2则为“id”，但是其表达的意义是一样的，而且数据格式也是一样的。这时，用 2）的写法就可以这样：

“select * from T1 where T1.ticketid in (select T2.id from T2) ”

Select name from employee where name not in (select name from student);

Select name from employee where not exists (select name from student);

第一句SQL语句的执行效率不如第二句。

通过使用EXISTS，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因

五、问题： 我创建了一个表来存放客户信息，我知道可以用 insert 语句插入信息到表中， 
                   但是怎么样才能保证不会插入重复的记录呢？ 

  答案： 可以通过使用 EXISTS 条件句防止插入重复记录。 


  示例一：插入多条记录 
  假设有一个主键为 client_id 的 clients 表，可以使用下面的语句： 

  INSERT INTO clients 
  (client_id, client_name, client_type) 
  SELECT supplier_id, supplier_name, 'advertising' 
  FROM suppliers 
  WHERE not exists (select * from clients 
  where clients.client_id = suppliers.supplier_id); 

个人批注：

not exists不存在，也就是说后面的括号中只要返回了数据那么这个条件就不存在了， 
        可以理解为括号前的notexists是一个左表达式 ，括号后的查询是一个右表达式， 
        只有当右表达式返回的也是not exists（即后面的查询出来的结果是非空的）时，等式才成立。 


  示例一：插入单条记录 
  Code: 
  INSERT INTO clients 
  (client_id, client_name, client_type) 
  SELECT 10345, 'IBM', 'advertising' 
  FROM dual 
  WHERE not exists (select * from clients 
  where clients.client_id = 10345); 

  使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值，即使这些值还不存在当前表中。