Comparison of the usage of EXISTS and IN in MySQL

1. Usage:

(1) EXISTS usage

select a.batchName,a.projectId from ucsc_project_batch a where EXISTS (select b.id from ucsc_project b where a.projectId = b.id) 

The above SQL means: query the batchName and projectId fields using ucsc_project_batch as the main table, where the projectId field exists in the ucsc_project table.

EXISTS will perform a loop query match on the outer table ucsc_project_batch. It does not care what the return value of the inner table subquery is, but only cares whether there is a return value. If there is a return value, the condition is true, the data is matched successfully, and is added to the query result set; if there is no return value, the condition is false, and the data is discarded.

For example, if we change the query return field of the first query, it will not affect the query result of the outer query:

select a.batchName,a.projectId from ucsc_project_batch a where EXISTS (select b.companyId,b.name from ucsc_project b where a.projectId = b.id) 

(2) IN usage

select a.batchName,a.projectId from ucsc_project_batch a where a.projectId in (select b.id from ucsc_project b)

The query result of the above SQL is the same as the result of EXISTS just now, and the meaning of the query is also the same.

2. Note:

(1) When writing EXISTS, you need to pay attention to the fact that the conditional statement in the subquery generally needs to be associated with the table of the outer query. Otherwise, the condition of the subquery may always be true or always be false. When the outer query table is cyclically matched, either all results will be found or none will be found.

select a.batchName,a.projectId from ucsc_project_batch a where EXISTS (select b.id from ucsc_project b)

For example, in the above writing method, since the ucsc_project table has values, the condition of the subquery is always true. When each data of ucsc_project_batch is matched in a loop, it can be matched successfully, and the query result becomes the data of the entire ucsc_project_batch table.

select a.batchName,a.projectId from ucsc_project_batch a where EXISTS (select b.id from ucsc_project b where b.id is null)

In this way, the subquery will definitely not find any results, so the condition of the subquery is false, each piece of data in the outer query fails to match, and the entire query result is empty.

(2) There is no limit on the number of parameters in the IN statement in MySQL, but there is a length limit for SQL statements in MySQL, with the maximum length of the entire statement being 4M.

(3) The EXISTS subquery statement does not care what is queried, but only cares whether there is a result set. If there is, the entire subquery can be regarded as a statement with a true condition, otherwise it is a statement with a false condition.

(4) The IN statement can only return one field for the subquery, otherwise an error will be reported:

select a.batchName,a.projectId from ucsc_project_batch a where a.projectId in (select b.id,b.companyId from ucsc_project b)

[Err] 1241 - Operand should contain 1 column(s)

3. Scene selection

If the outer query table is large and the subquery table is small, select IN; if the outer query table is small and the subquery table is large, select EXISTS; if the two tables are of similar size, then they are similar.

(1) The SQL query in IN will only be queried once, and then the result set will be stored in a temporary file, and then matched with the outer query SQL. Both the outer query and the subquery can use indexes.

select a.batchName,a.projectId from ucsc_project_batch a where a.projectId in (select b.id from ucsc_project b)

is equivalent to:

$result = [];
$ucsc_project_batch = "select a.batchName,a.projectId from ucsc_project_batch a";
$ucsc_project = "select b.id from ucsc_project b";
for($i = 0;$i < $ucsc_project_batch .length;$i++){
 for($j = 0;$j < $ucsc_project.length;$j++){
  if($ucsc_project_batch[$i].projectId== $ucsc_project[$j].id){
   $result[] = $ucsc_project_batch[$i];
   break;
  }
 }
}

(2) EXISTS will perform a loop match on the external query table ucsc_project_batch, executing ucsc_project_batch.length times. The subquery can use the index, and the external query scans the entire table.

select a.batchName,a.projectId from ucsc_project_batch a where EXISTS (select b.id from ucsc_project b where a.projectId = b.id)

is equivalent to:

$result = [];
$ucsc_project_batch = "select a.batchName,a.projectId from ucsc_project_batch a ";
for ($i = 0; $i < $ucsc_project_batch . length; $i++) {
 if (exists($ucsc_project_batch [$i] . projectId)) {//Execute select b.id from ucsc_project b where a.projectId=b.id        
  $result[] = $ucsc_project_batch[$i];
 }
}

Through the analysis of the two pseudo codes, we can see that: when the subquery table is large, using EXISTS can effectively reduce the total number of loops to improve the speed; when the outer query table is large, using IN can effectively reduce the loop traversal of the outer query table to improve the speed.

Summarize

This is the end of this article about the comparison of the usage of EXISTS and IN in MySQL. For more relevant MySQL EXISTS and IN comparison content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!