Basic usage of exists, in and any in MySQL

【1】exists

Use a loop to query the external table one by one, and check the exists conditional statement for each query.

When the conditional statement in exists can return a row of records (no matter how many rows there are, as long as they can be returned), the condition is true and the record currently looped to is returned. On the contrary, if the conditional statement in exists cannot return a record row, the condition is false, and the record currently looped to is discarded.

The exists condition is like a boolean condition, which is 1 if a result set can be returned and 0 if a result set cannot be returned.

The syntax format is as follows:

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

Here is an example:

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

If there is a record with id 12 in the p_user table, all records in the p_user_2 table will be returned; otherwise, the returned records will be empty.

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

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

【2】in

The syntax format is as follows:

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

It should be noted that in where, column is a column of A, and the subquery statement corresponding to in returns a result set with one column and multiple rows.

Note that the result returned by the select statement corresponding to in must be one column! Can be multiple lines.

Here is an example:

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

Query the record of p_user_2 whose id is in the id set of the p_user table. not in is the opposite.

【3】The relationship between exists and in

After the SQL is changed, 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 )

So when should we use exists or in?

**If the two tables being queried are of similar size, there is little difference between using in and exists. **

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

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

① The subquery table is Table B:

select * from A 
where cc in (select cc from B) 
//Low efficiency, using the index of column cc in table A;
 
select * from A 
where exists(select cc from B where cc=A.cc) 
//High efficiency, using the index of column cc in table B. 

② The subquery table is Table A:

select * from B 
where cc in (select cc from A) 
//High efficiency, using the index of column cc in table B;
 
select * from B 
where exists(select cc from A where cc=B.cc) 
//Low efficiency, using the index of column cc in table A.

If the query statement uses not in, both the inner and outer tables are scanned completely, and the index is not used. However, the subquery of not exists can still use the index on the table.

**So no matter which table is larger, using not exists is faster than not in. **

【4】any/some/all

① any, in, some, all are one of the subquery keywords respectively

any can be used in combination with =, >, >=, <, <=, and <> to represent equal to, greater than, greater than or equal to, less than, less than or equal to, and not equal to any of the data respectively.

all can be used in combination with =, >, >=, <, <=, and <> to represent all the data in the range of equal to, greater than, greater than or equal to, less than, less than or equal to, and not equal to.

Their syntax for subqueries is as follows:

operand comparison_operator any (subquery);
operand in (subquery);
operand coparison_operator some (subquery);
operand comparison_operator all (subquery);

The any, all keywords must be used with a comparison operator.

② The any keyword can be understood as "for any value in the column returned by the subquery, if the comparison result is true, return true."

For example:

select age from t_user where age > any (select age from t_user_copy);

Assume that there is a row in table t_user containing (10) and t_user_copy contains (21,14,6), then the expression is true; if t_user_copy contains (20,10), or table t_user_copy is empty, the expression is false. If table t_user_copy contains (null, null, null), the expression is unkonwn.

all means "for all values ​​in the column returned by the subquery, if the comparison evaluates to true, return true"

For example:

select age from t_user where age > all (select age from t_user_copy);

Assume that there is a row in table t_user containing (10). If the table t_user_copy contains (-5, 0, +5), the expression is true because 10 is greater than all three values ​​found in t_user_copy. If table t_user_copy contains (12, 6, null, -100), then the expression is false because there is a value 12 in t_user_copy which is greater than 10. If table t_user_copy contains (0, null, 1), the expression is unknown. If t_user_copy is an empty table, the result is true.

③ not in /in

not in is an alias for "<>all" and has the same usage.

The statement in is identical to "=any".

For example:

select s1 from t1 where s1 = any (select s1 from t2);
select s1 from t1 where s1 in (select s1 from t2);

The statement some is an alias of any and is used in the same way.

For example:

select s1 from t1 where s1 <> any (select s1 from t2);
select s1 from t1 where s1 <> some (select s1 from t2);

In the above query, some is easy to understand as "some s1 in table t1 is not equal to s1 in table t2". This statement is wrong when interpreted as any.

Summarize

This is the end of this article about the basic usage of exists, in and any in MySQL. For more relevant MySQL exists, in and any 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!