Commonly used basic operation syntax of mysql (10)~~Subquery [command line mode]-Mysql Tutorial-php.cn

Commonly used basic operation syntax of mysql (10)~~Subquery [command line mode]

黄舟

Release： 2017-03-03 14:22:38

Original

1202 people have browsed it

Although there are join queries in mysql to implement multi-table join queries, the performance of join queries is very poor, so subqueries appear.

1. Theoretically, subqueries can appear anywhere in the query statement, but in practical applications they often appear after from and after where. The subquery results that appear after from are usually multi-row and multi-column, acting as temporary tables; while the subquery results that appear after where are usually single rows and single columns, acting as conditions:

2. Subqueries as conditions after where are often used with comparison operators such as "=", "!=", ">", and "<". Although the result is usually a single row and a single column, sometimes a single row and multiple columns are used, and sometimes multiple rows and a single column are returned. If it is the case of multiple rows and single columns, it is often used in combination with in, any, all, exists:

in and not in:

Among them, in means any one included in the subsequent subquery result set, and not in means any one not included in the subsequent result set. In the result of the subquery in the above figure, the returned part_id is 1, 2, and 4. Therefore, the first query will find out all the part_id in emp is 1 or 2 or 4, and the second query will find out that it is neither 1 nor 4. 2 is not 4 either.

any:

=The usage of any is the same as the effect of in; >any means it is greater than the subquery result set Any one is fine. A simple understanding is that it only needs to be greater than the smallest one in the result set; any only needs to be greater than 1, and

all:

##>all means to be greater than all the subquery result sets. A simple understanding is that it is larger than the largest one. Big;

exists:

##exists and not exists only care about whether the subsequent subquery has results, and It doesn't matter what the result of the subquery is; for exists, if the subsequent subquery has a result, its value is true, otherwise if no result is found, it is false, and not exists is just the opposite of this situation, and the value is false, no value is true. When their values are true, the results of the previous query are established and will be added to the result set of the main query. Otherwise, they will not be added to the result set of the main query.

In the above query, the query statement will first query a piece of data from emp, and then compare the part_id of this data with the part_id in company. When there are two data with the same ID, it will There are results returned, and then 6 pieces of data are finally returned. The same reason is true for the second query. When there are no two data with the same ID, the main query will return results and only one piece of data.

The above is the content of mysql common basic operation syntax (10) ~~ subquery [command line mode]. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!