Although there is a connection query in MySQL to implement a multi-table connection query, but the performance of the connection query is poor, so there is a subquery.
1, in theory, the subquery can appear anywhere in the query statement, but the actual application is more than now from after and where. A subquery result that appears after the from is usually a multi-line, multi-column, which acts as a temporary table, whereas a subquery result that appears in a where is usually a single-row column, serving as a condition:
2, where the sub-query as a condition of more and "=", "! = "," > "," < "and other comparison operators are used together. The result is usually a single row, but sometimes a single row of multiple columns is used, and in some cases a multiline column is returned. If it is a multi-line single-column case, it is used in conjunction with in, no, all, exists:
Where in means any one in the result set of the sub-query that is included in the back, the not-in representation is not included in any of the result sets behind. In the results of the subquery, the returned depart_id is 1, 2, 4, so the first query will find out that all depart_id in the EMP are 1 or 2 or 4, and the second one will find neither 1 nor 2 nor 4.
Any
The usage of =any is the same as the effect of in; >any is said to be larger than any one of the subquery result set can be, simple to understand is as long as the smallest in the result set is more than the one can be, <any is smaller than the subquery result set any one can be, The simple understanding is that as long as it is smaller than the largest one can. So here >any is as long as more than 1 on the line, and <any as long as less than 4 on the line.
All
>all that is greater than the subquery result set of all, simple understanding is bigger than the largest one, <all is smaller than the result set of all, simple understanding is smaller than the smallest. So the first query above is more than 4, and the second one is less than 1.
Exists
exists and not exists only care about the results of the subquery, but not the results of the subquery; for exists, if the subquery in the back has a result, then its value is true, and false if no result is found, and not The exists is exactly the opposite of this, with a value of false and no value of true. When their value is true, the result of the preceding query will be added to the result set of the main query, otherwise it will not be added to the result set of the main query. In the above query, the query statement will first query the EMP from the data, and then take this data depart_id and company in the depart_id comparison, when there are two IDs of the same data, there will be a result return, and then eventually return 6 data, and the second query the same reason, When there are no two data with the same ID, the main query returns only one piece of data.
MySQL Common basic operation Syntax (10) ~ ~ subquery "command line mode"
