Mysql-exists and in usage

"1" exists

For the appearance with loop-by-article query, each query will see exists conditional statements.

When a conditional statement in exists can return a record row (regardless of the number of records, as long as it can be returned), the condition is true, returning the current loop to the record. Conversely, if the conditional statement in the exists cannot return a record line, the condition is false, and the current loop to which the record is discarded.

The exists condition is like a Boolean condition, when it can return a result set of 1 and cannot return a result set of 0.

The syntax format is as follows:

select * from tables_name where [not] exists(select..);

• 1

Examples are as follows:

select * from p_user_2 where  EXISTS(select * from p_user where id=12)

• 1

If there is a record with ID 12 in the P_user table, all records in the p_user_2 table are returned, otherwise, the returned record is empty.

If not exists, this is the opposite of the above.

In general, if a table has n records, then the exists query is to take these n records out one by one and then judge N Times exists condition

"2" in

The syntax format is as follows:

select * from A where column in (select column from B);

• 1

It is necessary to note that in where, column A is a row, in the corresponding subquery statement is returned as a column of multi-row result sets.

Note that in the corresponding SELECT statement returns the result must be a column! can be multiple lines.

Examples are as follows:

select * from p_user_2 where id [not] in (select id from p_user )

• 1

The query ID records for the p_user_2 of the P_user table ID collection. Not in is the opposite.

The relationship between "3" exists and in

After SQL changes, the two can achieve the same goal:

select * from p_user_2 where id [not] in (select id from p_user );select * from p_user_2 where [not] EXISTS (select id from p_user where id = p_user_2.id )

• 1
• 2
• 3

So when do you use exists or in?

If the two table size of the query is equal, then the in and exists are not very different.
If one of the two tables is smaller and one is a large table, then the subquery table is large with exists, and the subquery table is small in:

Example: Table A (small table), table B (large table)

① Sub-query table is table B:

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。 相反的

• 1
• 2
• 3
• 4

② Sub-query table is table A:

select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。

• 1
• 2
• 3

Not-in and not-exists if the query statement uses not-in to perform a full-table scan of the outer surface, the index is not used, and the index on the table is still used by not Extsts's subquery.

So no matter the table is large, using not exists is faster than not.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced. 72910548

Mysql-exists and in usage