The efficiency of exists and not exists usages in and exists statements in Oracle

Source of the blog post (exists and not EXISTS usage in Oracle): http://chenshuai365-163-com.iteye.com/blog/1003247

Bowen Source (in VS. exists statement efficiency issues): http://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html

A

exists (SQL returns the result set as true)
NOT EXISTS (SQL does not return result set is true)
As follows:
Table A
ID NAME

1A1
2    A2
3  A3

Table B
ID AID NAME
1    1 B1
2    2 B2
3    2 B3

Table A and table B are 1-to-many relationships a.id => B.aid

1 SELECTId,name fromAWHEREEXIST (SELECT *  fromBWHEREa.ID=b.aid)2 execution results are3 1A14 2A25 The reasons can be analyzed as follows6 SELECTId,name fromAWHERE EXISTS(SELECT *  fromBWHEREB.aid=1)7 --->select * from B WHERE b.aid=1 has value return true so there is data8 9 SELECTId,name fromAWHERE EXISTS(SELECT *  fromBWHEREB.aid=2)Ten --->select * from B WHERE b.aid=2 has value return true so there is data One  A SELECTId,name fromAWHERE EXISTS(SELECT *  fromBWHEREB.aid=3) - --->select * from B WHERE b.aid=3 No value return true so no data -  the  not EXISTSis the reverse . - SELECTId,name fromAWHERE 　  notEXIST (SELECT *  fromBWHEREa.ID=b.aid) - execution results are - 3A3

(b) in SQL, Not in,EXISTS, not usage and differences of exists:

 　　 inch keyword allows you to select a row that matches any of the values in the list.　　Determines whether the given value matches a subquery or a value in the list. (1The following query is required to obtain the names and state lists of all authors residing in California, Indiana, or Maryland states:SELECTProductID, ProductName fromNorthwind.dbo.ProductsWHERECategoryID= 1 ORCategoryID= 4 ORCategoryID= 5However, if you useinch, you can also get the same result by typing fewer characters:SELECTProductID, ProductName fromNorthwind.dbo.ProductsWHERECategoryIDinch(1,4,5)inchitems After the keyword must be separated by commas and enclosed in parentheses. (2The following query finds in the titleauthor table the royalties obtained in either book less than -%au_id of all authors, and then from the authors table, select the names of all authors whose au_id match the titleauthor query results:SELECTau_lname, au_fname fromAuthorsWHEREau_idinch(SELECTau_id fromtitleauthorWHERERoyaltyper< -) results show that some authors belong to less than -%of the class.  not inch: by not inchthe sub-query introduced by the keyword also returns a column of 0 values or more.　　The following query finds the name of a publisher who has not published a business book. SELECTPub_name fromPublishersWHEREpub_id not inch(SELECTpub_id fromTitlesWHEREType= ' Business') using  EXISTS and  not EXISTS  the introduced subqueries can be used for the operation of two sets of principles: intersection and Difference sets.　　  (1) The intersection of two sets contains all elements that belong to two of the original collection at one time.　   (2) The difference set contains elements that belong to only the first collection in the two collection. EXISTS : Specifies a subquery that detects the existence of a row. The query in this example looks for titles published by any publisher in a city that begins with the letter B:SELECT DISTINCTPub_name fromPublishersWHERE EXISTS(SELECT *  fromTitlesWHEREpub_id=publishers.pub_id andType=' Business')SELECT distinctPub_name fromPublishersWHEREpub_idinch(SELECTpub_id fromTitlesWHEREType= ' Business')
The difference between the two:EXISTS: The following can be a query statement for an entire sentence such as:SELECT *  fromtitlesinch: The following can only be a single column:SELECTpub_id fromtitles 　 Not   EXISTS  : For example, to find the name of a publisher who does not publish a business book:SELECTPub_name fromPublishersWHERE  not EXISTS(SELECT *  fromTitlesWHEREpub_id=publishers.pub_id andType= 　　 ' Business'The following query finds the name of a book that has not been sold:SELECTTitle fromTitlesWHERE  not EXISTS(SELECTtitle_id fromSalesWHEREtitle_id=TITLES.TITLE_ID)

Three Efficiency problems in the EXISTS

SELECT *From awhere ID in (select ID from B) The query uses the in statement, in () executes only once, it detects all the ID fields in table B and caches them. After that, check that the ID of table A is equal to the ID in table B. If equal, the records of table A are added to the result set until all records of table A are traversed. Its query process resembles the following procedure in list ResultSet=[]; Array A= (SELECT *from A); Array B=(select ID from B); for(inti=0;i<a.length;i++) {    for(intj=0;j<b.length;j++) {      if(a[i].id==b[j].id)         {Resultset.add (a[i]);  Break; }   }}returnResultSet; As you can see, when table B data is large, it is not appropriate to use in () because it will traverse the B-table data all at once. For example: A table has 10,000 records, B has 1 million records, so it is possible to traverse 10000*1 million times, the efficiency is very poor. Again such as: A table has 10,000 records, b table has 100 records, it is possible to traverse 10000*100 times, the number of traverse is greatly reduced, and the efficiency is greatly improved. Conclusion: In () is suitable for the case that B table is smaller than table A, select a.*From A awhere exists (select1 from b b where a.id=b.id) The above query uses the EXISTS statement, exists () executes a.length times, it does not cache the exists () result set, because the contents of the exists () result set are not important, it is important whether there are records in the result sets, and if so, returns True. None returns FALSE. Its query process is similar to the following procedure list ResultSet=[]; Array A= (SELECT *From A) for(inti=0;i<a.length;i++) {   if(Exists (a[i].id) {//executes select 1 from b where b.id=a.id if there are records returnedResultset.add (A[i]); }}returnResultSet; When the B table is larger than the A-table data, it is appropriate to use exists (), because it does not have that traversal operation and only needs to execute the query again. For example: A table has 10,000 records, B has 1 million records, then exists () Executes 10,000 times to determine if the ID in table A is equal to the ID in table B. For example: A table has 10,000 records, b table has 100 million records, then exists () is executed 10,000 times, because it only executes a.length times, the more B table data is visible, The more suitable the exists () to play the effect. Again such as: A table has 10,000 records, b table has 100 records, then exists () or execute 10,000 times, rather than using in () traversal 10000*100 times, because in () is traversing the comparison in memory, and exists () needs to query the database, we all know that querying the database consumes more performance, and the memory is relatively fast. Conclusion: Exists () is suitable for B table is larger than table A, when the A-table data is as large as the B-table data, In and exists efficiency is similar, can choose one to use.

The efficiency of exists and not exists usages in and exists statements in Oracle