The difference between SQL exists and in

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.