in和exists的区别

认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

下面是实验：

create table gw4 as select object_id,object_name from dba_objects;
SQL> select * from gw2;


        ID N
---------- -
         1 a
         2 b


2 rows selected.




1.小表做主查询，大表做子查询   结论：如果子表大，则in会转换成exists，走filter连接，主表为驱动表（小表），这时候子查询没有展开。


SQL> select * from gw2 where id in (select object_id from gw4);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    30 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW2  |     2 |    30 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| GW4  |   868 | 11284 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT 0 FROM "GW4" "GW4" WHERE "OBJECT_ID"=:B1))
   3 - filter("OBJECT_ID"=:B1)




SQL> select * from gw2 where exists (select 0 from gw4 where object_id=gw2.id);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    30 |     4   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW2  |     2 |    30 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| GW4  |   868 | 11284 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT 0 FROM "GW4" "GW4" WHERE "OBJECT_ID"=:B1))
   3 - filter("OBJECT_ID"=:B1)






2.小表做子查询，大表做主查询  结论：如果子表小，则in，走hash连接，子表为驱动表（小表），其实是这个时候进行了子查询展开


SQL> select * from gw4 where object_id in (select id from gw2);                        
---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 86801 |  7798K|   117   (1)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI|          | 86801 |  7798K|   117   (1)| 00:00:02 |
|   2 |   VIEW               | VW_NSO_1 |     2 |    26 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | GW2      |     2 |    26 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | GW4      | 86801 |  6696K|   114   (0)| 00:00:02 |
---------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("OBJECT_ID"="ID")




SQL> select * from gw4 where exists (select 0 from gw2 where id=gw4.object_id);
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 86801 |  7798K|   117   (1)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI|         | 86801 |  7798K|   117   (1)| 00:00:02 |
|   2 |   VIEW               | VW_SQ_1 |     2 |    26 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | GW2     |     2 |    26 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL  | GW4     | 86801 |  6696K|   114   (0)| 00:00:02 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("ITEM_1"="GW4"."OBJECT_ID")


看一下禁止子查询展开的情况：禁止展开，默认oracle改成了exists


SQL> select * from gw4 where object_id in (select /*+no_unnest*/id from gw2);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    32 |  2528 |  5540   (1)| 00:01:07 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW4  | 86801 |  6696K|   115   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| GW2  |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "GW2" "GW2" WHERE
              "ID"=:B1))
   3 - filter("ID"=:B1)




SQL> select * from gw4 where exists (select /*+no_unnest*/0 from gw2 where id=gw4.object_id);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    32 |  2528 |  5540   (1)| 00:01:07 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GW4  | 86801 |  6696K|   115   (1)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| GW2  |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "GW2" "GW2" WHERE
              "ID"=:B1))
   3 - filter("ID"=:B1)






总结：


exists：子查询不展开，主表是驱动表。子表大的时候适用。
in：    子查询展开，  子表是驱动表。子表小的时候适用。




简单总结就是：


in     (小表)
exists (大表)