What to use instead of in in mysql

In Mysql, exists is used instead of in; exists uses loop to query the external table one by one. Each query will check the conditional statement of exists. When the conditional statement in exists can return the record row, the condition is true and the current state is returned. loop to this record.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

exists uses loop to query the external table one by one. Each query will check the conditional statement of exists. When the conditional statement in exists can return the record row (no matter how many record rows there are, as long as it can be returned), the condition will be If true, the record in the current loop is returned. Otherwise, if the conditional statement in exists cannot return the record row, the record in the current loop is discarded. The condition of exists is like a bool condition. When the result set can be returned, is true, if the result set cannot be returned, it is false

As follows:

select * from user where exists (select 1);

The records of the user table are taken out one by one. Since select 1 in the sub-condition can always return record rows, then the user table All records will be added to the result set, so it is the same as select * from user;

And as follows

select * from user where exists (select * from user where userId = 0);

You can know that when looping the user table, check the conditional statement (select * from user where userId = 0), since userId is never 0, the conditional statement will always return the empty set, and the condition will always be false, then all records in the user table will be discarded

not exists is the opposite of exists, and also That is, when the exists condition returns a result set, the looped records will be discarded, otherwise the looped records will be added to the result set

In general, if table A has n records, then the exists query is Take out these n records one by one, and then judge the exists condition n times

in query is equivalent to the superposition of multiple or conditions. This is easier to understand, such as the following query

select * from user where userId in (1, 2, 3);

Equivalent to

select * from user where userId = 1 or userId = 2 or userId = 3;

not in is the opposite of in, as follows

select * from user where userId not in (1, 2, 3);

Equivalent to

select * from user where userId != 1 and userId != 2 and userId != 3;

In general, the in query is to first combine the subquery conditions All the records of are found. 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, and then m queries are performed.

It is worth mentioning that the in query The return result of the sub-condition must have only one field, such as

select * from user where userId in (select id from B);

but not

select * from user where userId in (select id, age from B);

. However, exists does not have this restriction.

Let’s consider the performance of exists and in.

Consider the following SQL statement

1: select * from A where exists (select * from B where B.id = A.id);

2: select * from A where A.id in (select id from B);

Query 1. You can convert the following pseudo code for easy understanding

for ($i = 0; $i < count(A); $i++) {
　　$a = get_record(A, $i); #从A表逐条获取记录
　　if (B.id = $a[id]) #如果子条件成立
　　　　$result[] = $a;
}
return $result;

This is probably what it means. In fact, you can see that Query 1 is mainly The index of table B is used. How table A has little impact on the efficiency of the query

Assume that all the ids of table B are 1,2,3, query 2 can be converted into

select * from A where A.id = 1 or A.id = 2 or A.id = 3;

This It’s easy to understand. The index of A is mainly used here. How table B has little impact on the query

Let’s look at not exists and not in

1. select * from A where not exists (select * from B where B.id = A.id);

2. select * from A where A.id not in (select id from B);

Looking at query 1, it is still the same as above, using the index of B

And for query 2, it can be converted into the following statement

select * from A where A.id != 1 and A.id != 2 and A.id != 3;

You can know that not in is a Range query, this != range query cannot use any index, which means that each record in table A must be traversed once in table B to check whether the record exists in table B

so not exists is more efficient than not in

The in statement in mysql is a hash connection between the external table and the internal table, while the exists statement is a loop loop on the external table, and the internal table is queried each time the loop loops. Everyone has always believed that exists is more efficient than the in statement. This statement is actually inaccurate. This depends on the environment.

If the two tables queried are of the same size, there is not much difference between using in and exists.

If one of the two tables is smaller and the other is a large table, use exists for the larger subquery table and in for the smaller subquery table:

For example: Table A (small table ), Table B (large table)

1:

select * from A where cc in (select cc from B) is inefficient and uses the index of the cc column on table A;

select * from A where exists(select cc from B where cc=A.cc) is efficient and uses the index of the cc column on table B.

The opposite

2:

select * from B where cc in (select cc from A) is highly efficient and uses the index of the cc column on table B;

select * from B where exists(select cc from A where cc=B.cc) is inefficient and uses the index of the cc column on table A.

not in and not exists If the query statement uses not in, then the entire table will be scanned on both the inner and outer tables without using the index; and the subquery of not extsts can still use the index on the table. So no matter which table is big, using not exists is faster than not in.

The difference between in and =

select name from student where name in ('zhang','wang','li','zhao');

与

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

The results are the same.

Recommended tutorial: mysql video tutorial

The above is the detailed content of What to use instead of in in mysql. For more information, please follow other related articles on the PHP Chinese website!