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

This article mainly introduces what mysql can use instead of in, the article is very detailed, has a certain reference value, interested friends must read it!

Mysql uses exists instead of in;exists to query the appearance one by one with loop. Each query will check the conditional statement of exists. When the conditional statement in exists can return record rows, the condition will be true and the record to which the current loop will be returned will be returned.

Exists uses loop to query the appearance one by one, and each query will check the conditional statement of exists. When the conditional statement in exists can return record rows (no matter how many record rows are, as long as it can), the condition is true and the record to which the current loop goes is returned. On the contrary, if the item statement in exists cannot return record rows, the record to which the current loop arrives is discarded, and the condition of exists is like a bool condition. True when the result set can be returned, and false if the result set cannot be returned

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 subcondition can always return record rows, then all records of the user table will be added to the result set, so it is the same as select * from user;.

Again as follows

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

You can know that when loop the user table, check the conditional statement (select * from user where userId = 0). Because the userId is never 0, the conditional statement always returns an empty set, and the condition is always false, then all records of the user table will be discarded.

Not exists is the opposite of exists, that is, when the exists condition has a result set returned, the record of loop will be discarded, otherwise the record of loop will be added to the result set.

In general, if table A has n records, then the exists query is to take out the n records one by one, and then judge the n-pass exists condition

The in query is equivalent to the superposition of multiple or conditions, which is easy 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

Generally speaking, the in query is to first find out all the records of the subquery condition, 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.

It is worth mentioning that the subcondition of an in query must return a result with only one field, such as

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

And can't be

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

Exists does not have this restriction.

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. The following pseudocode can be converted for easy understanding

For ($I = 0; $I < count (A); $iTunes +) {$a = get_record (A, $I); # get records if item by item from table A (B.id = $a [id]) # if the subcondition holds $result [] = $a;} return $result

In fact, we can see that query 1 mainly uses the index of table B, and table A should have little effect on the efficiency of the query.

Assuming that all id of table B are 1 and 2, query 2 can be converted to

Select * from A where A.id = 1 or A.id = 2 or A.id = 3

This is easy to understand. The index of An is mainly used here, and table B has little effect on the query.

Let's take a look at not exists and not in.

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

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

Look at query 1, which is the same as above, using the index of B

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, and this! = range query cannot use any index, which means that every record in table A has to be traversed once in table B to see if it exists in table B.

Therefore, not exists is more efficient than not in.

The in statement in mysql connects the outer table with the inner table as hash, while the exists statement uses the external table as a loop loop, and each loop loop queries the inner table. It has always been believed that exists is more efficient than in statements, but this is actually not accurate. This is to distinguish between the environment.

If the two tables of the query are of the same size, there is little difference between using in and exists.

If one of the two tables is small and the other is large, the large subquery table uses exists, and the small subquery table uses in:

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.

On the contrary

2:

Select * from B where cc in (select cc from A) is 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 both the inside and outside of the table are scanned without using the index, while the subquery of not extsts can still use the index on the table. So no matter which watch is big, it is faster to use not exists than not in.

The difference between in and =

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

Vs.

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

The result is the same.

The above is what mysql can use to replace all the content of in, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.