Shulou(Shulou.com)06/01 Report--

Put a piece of code first.

For (int iTuno witi select * from tbl_emp a where a.deptld=1 or a.deptld=2 or a.deptld=3)

Generally speaking, the in keyword is to find out all the results in the subquery, assuming that the result set is B, with a total of m records, and then decompose the result set of the subquery condition into m, and then make m queries. You can see that the index of An is mainly used here, and how table B has little effect on the query.

Using exists

Mysql > select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id)

Exits: put the data of the main query into the subquery for conditional verification, and determine whether to retain the records in the main query according to the verification results (True or False).

For (I = 0; I)

Select * from tbl_emp a where a.deptld in (select id from tbl_dept)

If the number of records of tbl_dept is less than tbl_emp, then it is more efficient to use in.

Mysql > select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id)

If the number of records of tbl_dept is more than tbl_emp, it is more efficient to use in.

Let's introduce the difference between IN and EXISTS.

1. IN query analysis

SELECT * FROM A WHERE id IN (SELECT id FROM B)

Equivalent to: 1. SELECT id FROM B-> execute the query in in first

2. SELECT * FROM A WHERE A.id = B.id

The query in in () above is executed only once, it queries out all the id in B and caches it, then checks whether the id queried in table An exists in the cache, and if so, adds the query data of table A to the result set until all the result sets in table An are traversed.

The following is to analyze the IN query by traversing the result set

From the above procedure, we can see that when the data in table B is large, it is not suitable to use in () query, because it will traverse all the data in table B once.

For example:

1. If there are 100 records in table An and 1000 records in table B, it is possible to traverse 100 to 1000 times at most, which is very inefficient.

2. If there are 1000 records in table An and 100 records in table B, the maximum number of internal loops can be reduced and the efficiency can be greatly improved.

Conclusion: IN () query is suitable for the case that the data of table B is smaller than that of table A, and IN () query fetches data from the cache.

2. EXISTS query analysis

Syntax: SELECT field FROM table WHERE EXISTS (subquery)

SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE B.id = A.id)

The above query is equivalent to:

SELECT * FROM A _ select I FROM B WHERE B.id = A.id

The EXISTS () query executes the SELECT * FROM A query, A.length times, and does not cache the results of the EXISTS () query, because the EXISTS () query returns a Boolean value of true or flase, which only cares about whether there are records in the query of EXISTS (), regardless of the specific result set.

EXISTS () query is to put the result set of the main query into the subquery for verification, according to the verification result is true or false to determine whether the main query data results can be saved.

Summary

The above is the introduction of the use and difference between in and exists in MySQL introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.