Because to use in, later on the internet to find, found the following articles, we share a bit. Progress together. With in words, if there is an index, there is little difference in using join performance. Reprinted from Http://blog.chinaunix.net/u/4929/showart_1075412.htmlIN
Determines whether the given value matches a subquery or a value in the list.
EXISTS
Specifies a subquery that detects the presence of a row.
Compare queries using EXISTS and in
This example compares two semantically similar queries. The first query uses EXISTS and the second query uses in. Note Two queries return the same information.
UsepubsGO SELECT DISTINCTpub_name fromPublishersWHERE EXISTS ( SELECT * fromtitlesWHEREpub_id=publishers.pub_id andType= ' Business'
) GO
--Or, using the IN clause:
use GO SELECT distinct pub_name from publishers WHERE inch (SELECTfromWHERE=' Business '
GO
The following is the result set for either query:
Pub_name
----------------------------------------
Algodata Infosystems
New Moon Books
(2 row (s) affected) exits is equivalent to the existence of quantifiers: Indicates that the collection exists, that is, the collection is not empty, only a collection. For example, exist p indicates that p is true when it is not empty; The not exist p indicates that p is null when true in represents a scalar and unary relationship relationship. For example: s in P means true when s is equal to a value in P; s not in p indicates that s is not equal to each of the values in P when it is true to reprint from http://wp19908.javaeye.com/blog/148024 when fetching data in Oracle SQL sometimes use in and exists so what's the difference?
1 Comparison of performance
such as SELECT * from T1 where x in (select Y from T2)
The procedure performed is equivalent to:
SELECT *
from T1, (select distinct y from T2) T2
where t1.x = T2.y;
Relative to
SELECT * from t1 where exists (select null from t2 where y = x)
The procedure performed is equivalent to:
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
Table T1 inevitable to be completely scanned again
What are the conditions for each?
Take a subquery (select Y from T2) as a direction
If the result set of a subquery is large and takes a lot of time, but T1 is relatively small execution (select null from t2 where y = x.x) is very fast, then exists is more suitable for use here
You should use in when the result set of the subquery is relatively small. Reprinted from http://hi.baidu.com/hopedaily/blog/item/56d23edbde87cd60d0164efe.html
In and existsIn is the appearance and the inner table as a hash connection, and exists is the external loop loop, each loop loop and then query the internal table.
The assertion that exists is more efficient than in is inaccurate. If the two table size of the query is equal, then the in and exists are not very different.
If one of the two tables is smaller and one is a large table, then the subquery table is large with exists, and the subquery table is small in:
Example: Table A (small table), table B (large table)
1:
SELECT * from A where CC in (select CC from B) is inefficient and uses the index of the CC column on table A;
SELECT * from A where exists (select cc from B where cc=a.cc) is efficient and uses the index of the CC column on table B. The opposite
2:
SELECT * from B where cc in (select CC from A) is highly efficient and uses the index of the CC column on table B;
SELECT * from B where exists (select cc from A where cc=b.cc) is inefficient and uses the index of the CC column on table A.
Not in and not exists
If the query statement uses not in so the appearance of the full table scan, not used index;
The index on the table can still be used by the subquery of not extsts.
So no matter the table is large, using not exists is faster than not.
difference between in and =
Select name from student where name in (' Zhang ', ' Wang ', ' Li ', ' Zhao ');
And
Select name from student where Name= ' Zhang ' or name= ' li ' or name= ' Wang ' or name= ' Zhao '
The result is the same.
Original address: http://www.cnblogs.com/milo_yu/archive/2010/12/23/1914549.html
The difference between SQL exists and in
