Summary of differences between in queries and exists queries in mySQL, mysqlexists

Summary of differences between in queries and exists queries in mySQL, mysqlexists

1. query exists

explain select * from vendor where EXISTS(select * from area where area_code = vendor_prov_code ) limit 10

The preceding is a typical SQL statement for exists queries.

The function is as follows: each time a piece of data is queried from the vendor tablevendor_prov_codeThe value is passed to the exists query for execution, that is, the execution of the subquery.

If the data found in the subquery is true, the return value is true. If not, the return value is false. If the return value is true, the data is saved; otherwise, the data is discarded. That is to say, the exists query is to execute a subquery when a piece of data is queried.

Note that:Exists .... Only boolean values true or false are returned. Other fields are not returned.

Ii. query in

explain select * from vendor where vendor_prov_code in (select area_code from area ) limit 10

The preceding is an SQL statement about in queries.

Its function is as follows: Execute the subquery first, that isin()The statement. After the data is queried by the subquery, the preceding query is divided into n common queries (n indicates the number of data rows returned in the subquery ). That is, take the following steps:

Select * from doub_vendor where vendor_prov_code = subquery result 1 ...... select * from doub_vendor where vendor_prov_code = subquery result n

Some people are competing on the Internet for the efficiency of exists queries and in queries. Here we have to talk about the following: Different mysql versions, different businesses, different data, and execution efficiency are not necessarily, this involves the optimization of SQL statements by the mysql optimizer and the data volume of subqueries.

Some people on the Internet said that MySQL has optimized in queries, so the efficiency issue depends on the specific scenario and the actual test data for optimization!

Summary

The above are all the differences between in queries and exists queries in Mysql. I hope the content in this article will help you in your study or work. If you have any questions, please leave a message.