- 网上查了很多关于这两个单词的用法,说法很多,但大同小异,现在归纳如下:
- 关于EXISTS与IN的区别:
- EXISTS检查是否有结果,判断是否有记录,返回的是一个布尔型(TRUE/FALSE)。
- IN是对结果值进行比较,判断一个字段是否存在于几个值的范围中,所以 EXISTS 比 IN 快。
- 主要区别是:
- exists主要用于片面的,有满足一个条件的即可,
- in主要用于具体的集合操作,有多少满足条件.
- exists是判断是否存在这样的记录,
- in是判断某个字段是否在指定的某个范围内。
- exists快一些吧 。
- in适合内外表都很大的情况,exists适合外表结果集很小的情况。
- 在ASKTOM的讲解:
- Well, the two are processed very very differently.
- Select * from T1 where x in ( select y from T2 )
- is typically processed as:
- select *
- from t1, ( select distinct y from t2 ) t2
- where t1.x = t2.y;
- The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then
- joined to the original table -- typically.
- As opposed to
- select * from t1 where exists ( select null from t2 where y = x )
- That is processed more like:
- 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
- It always results in a full scan of T1 whereas the first query can make use of
- an index on T1(x).
- So, when is where exists appropriate and in appropriate?
- Lets say the result of the subquery
- ( select y from T2 )
- is "huge" and takes a long time. But the table T1 is relatively small and
- executing ( select null from t2 where y = x.x ) is very very fast (nice index on
- t2(y)). Then the exists will be faster as the time to full scan T1 and do the
- index probe into T2 could be less then the time to simply full scan T2 to build
- the subquery we need to distinct on.
- Lets say the result of the subquery is small -- then IN is typicaly more
- appropriate.
- If both the subquery and the outer table are huge -- either might work as well
- as the other -- depends on the indexes and othe* **ctors.