The efficiency of exists and not exists usage in and exists statements in oracle. oracleexists

The efficiency of exists and not exists usage in and exists statements in oracle. oracleexists

Blog source (exists and not exists usage in oracle): http://chenshuai365-163-com.iteye.com/blog/1003247

Blog source (the efficiency of in and exists statements): Http://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html

(1)

Exists (the result set returned by SQL is true)
Not exists (SQL does not return true result sets)
As follows:
Table
ID NAME

1 A1
2 A2
3 A3

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

A. ID => B. AID

1 select id, name from a where exist (SELECT * from B WHERE. ID = B. AID) 2 execution result is 3 1 A1 4 2 A2 5 reasons can be analyzed as follows 6 select id, name from a where exists (SELECT * from B where B. AID = 1) 7 ---> SELECT * from B where B. AID = 1 A value returns true, so there are data 8 9 select id, name from a where exists (SELECT * from B where B. AID = 2) 10 ---> SELECT * from B where B. AID = 2 returns true if there is A value, so 11 12 select id, name from a where exists (SELECT * from B where B. AID = 3) 13 ---> SELECT * from B where B. AID = 3 no value returns true, so no data 14 15 not exists is in turn 16 select id, name from a where not exist (SELECT * from B WHERE. ID = B. AID) 17 execution result is 18 3 A3

 

(2) SQL IN,NOT IN,EXISTS,NOTEXISTS usage and difference:

 

The IN keyword allows you to select a row that matches any value IN the list. Determines whether the given value matches the value in the subquery or list. (1) to obtain a list of names and States of all authors living in California, Indiana, Or Maryland, You need to query the following: SELECT ProductID, ProductName FROM Northwind. dbo. products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5 however, if you use IN, you can also get the same result by typing less characters: SELECT ProductID, ProductName FROM Northwind. dbo. the items after the Products WHERE CategoryID IN (1, 4, 5) IN keyword must be separated by commas (,) and enclosed IN brackets. (2) The following query queries in the titleauthor table the au_id of all authors whose royalties in any book are less than 50%, then, FROM the authors table, SELECT the names of all authors matching the au_id and titleauthor query results: SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE altyper <50) the results show that some authors belong to the category of less than 50%. Not in: subqueries introduced by the not in keyword also return a column of zero or more values. The following query lists the names of publishers who have not published any commercial books. SELECT pub_name FROM publishers WHERE pub_id not in (SELECT pub_id FROM titles WHERE type = 'business') use the subquery introduced by EXISTS and not exists to operate on two sets principles: intersection and difference set. (1) the intersection of the two sets contains all elements of the two original sets. (2) The difference set contains only the elements of the first set of the two sets. EXISTS: Specifies a subquery to check the existence of a row. This example shows how to query the title of a book published by any publisher in the city WHERE the letter starts with B: SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers. pub_id AND type = 'business') SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business ')
The difference between the two: EXISTS: the query statement can be followed by the entire sentence, for example: SELECT * FROM titles IN: the query statement can only be a single column: SELECT pub_id FROM titles not exists: for example, the name of the publisher who does NOT publish a commercial book: SELECT pub_name FROM publishers where not exists (SELECT * FROM titles WHERE pub_id = publishers. pub_id AND type = 'business') query names of books that are NOT sold: SELECT title FROM titles where not exists (SELECT title_id FROM sales WHERE title_id = titles. title_id)

 

(3) Efficiency of In EXISTS

Select * from Awhere id in (select id from B) the preceding query uses the in statement. in () is executed only once. It finds all the id fields in Table B and caches them. then, check whether the IDs of Table A are equal to those of Table B. If they are equal, add the records of Table A to the result set until all the records of Table A are traversed. its query process is similar to the following process: List resultSet = []; Array A = (select * from A); Array B = (select id from B); for (int I = 0; I <. length; I ++) {for (int j = 0; j <B. length; j ++) {if (A [I]. id = B [j]. id) {resultSet. add (A [I]); break ;}} return resultSet; it can be seen that in () is not suitable for large data volumes in table B (), because it traverses all data in Table B once. for example, if Table A has 10000 records and table B has 1000000 records, it is possible to traverse A maximum of 10000*1000000 times, resulting in poor efficiency. for example, if Table A has 10000 records and table B has 100 records, it is possible to traverse A maximum of 10000*100 times, greatly reducing the number of traversal times and greatly improving the efficiency. conclusion: in () is suitable for select A if Table B has less data than table. * from A awhere exists (select 1 from B B B where. id = B. id) The above query uses the exists statement, exists () will execute. length, it does not cache exists () result set, because exists () result set content is not important, it is important to check whether there is a record in the result set, if yes, return true, if not, false is returned. its query process is similar to the following process: List resultSet = []; Array A = (select * from A) for (int I = 0; I <. length; I ++) {if (exists (A [I]. id) {// execute select 1 from B B where B. id =. returns resultSet. add (A [I]);} return resultSet; when table B is larger than table A, exists () is applicable because it does not perform this traversal operation, you only need to execute the query again. for example, if Table A has 10000 records and table B has 1000000 records, exists () will execute 10000 times to determine whether the IDs in Table A are equal to those in table B. for example, if Table A has 10000 records and table B has 100000000 records, exists () still executes 10000 times because it only executes. length times. The more data in Table B, the more suitable exists. for another example, if Table A has 10000 records and table B has 100 records, it is better to use in () to traverse 10000*10000 times if exists () is executed 100 times, because in () is compared in memory traversal, and exists () needs to query the database, we all know that the query database consumes a higher performance, and the memory is relatively fast. conclusion: exists () is suitable for the case where Table B is larger than table A. when table A is as large as table B, in and exists are more efficient. You can choose one of them.