Introduction to the use and difference between in and exists in MySQL

First put a piece of code

for(int i=0;i<1000;i++){
 for(int j=0;j<5;j++){
 System.out.println("hello");
 }
}

 for(int i=0;i<5;i++){
 for(int j=0;j<1000;j++){
 System.out.println("hello");
 }
}

Analyzing the above code, we can see that the two lines of code are no different except for the order of the loops. In actual execution, the time and space consumed by the two should also be the same. But this is only in Java. Now let's change the scenario. The outermost loop is a connection operation in the database, and the inner loop is a search operation. Now the results of the two operations will be very different.

The reason for this is determined by the characteristics of the database. Compared with query operations in the database, establishing a connection consumes more resources. The first code established 1,000 connections, but each connection only performed 5 queries, which is obviously a waste.

Therefore, the operation we need to follow when operating the database should be small table driving large table (small data set driving large data set).

in and exists

Table Structure

tbl_emp is the employee table, and deptld is the department id. tbl_dept is the department table. The employee table contains guests, and its deptld field is -1

mysql> desc tbl_emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| deptld | int(11) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc tbl_dept;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| deptName | varchar(30) | YES | MUL | NULL | |
| locAdd | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

We know that a company has many more employees than a department. Now we have such a requirement: query the employees belonging to this company (excluding visiting guests), we can use the following code to solve

Use in

# First query all ids in the department table, then compare them with the deptld field in the employee table. If they are found, keep them.

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

The in keyword is like the concatenation of or. For example, the results found by the subquery in the above SQL are 1, 2, and 3. The sql statement is equivalent to the following form

mysql> select * from tbl_emp a where a.deptld=1 or a.deptld=2 or a.deptld=3

In general, the in keyword is to find all the results of the subquery. Assume that the result set is B, with a total of m records. Then, the result set of the subquery condition is decomposed into m pieces and then m queries are performed. It can be seen that the index of A is mainly used here, and the B table has little impact 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 based on the verification result (True or False).

for (i = 0; i < count(A); i++) { //Traverse the total number of records in Aa = get_record(A, i); //Get records one by one from table Aif (B.id = a[id]) //If the sub-condition is metresult[] = a;
}
return result;

It can be seen that exists mainly uses the index of table B, and the index of table A has little effect on the efficiency of the query.

in conclusion

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

If the number of records in tbl_dept is less than that in tbl_emp, 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 in tbl_dept exceeds that of tbl_emp, it is more efficient to use in

Here is an introduction to 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 the above in() is executed only once. It queries all the IDs in B and caches them. Then it checks whether the ID queried in table A exists in the cache. If it exists, the query data of A is added to the result set until all the result sets in table A are traversed.

The following is an analysis of the IN query by traversing the result set

From the above program, 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. There are 100 records in table A and 1000 records in table B, so the maximum possible traversal is 100*1000 times, which is very inefficient.

2. If there are 1000 records in table A and 100 records in table B, then a maximum of 1000*100 records can be traversed, the number of inner loops is reduced, and the efficiency is greatly improved.

Conclusion: IN() query is suitable for the case where the data in table B is smaller than that in table A. IN() query retrieves data from 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 will execute the SELECT * FROM A query, execute A.length times, and will not cache the EXISTS() query results, because the EXISTS() query returns a Boolean value of true or false, which only cares whether there are records in the EXISTS() query, and has nothing to do with the specific result set.

The EXISTS() query puts the result set of the main query into the subquery for verification, and decides whether the main query data result is saved based on whether the verification result is true or false.

Summarize

The above is an introduction to the usage and differences of in and exists in MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!