Several common forms of MySQL subqueries

Common mysql subqueries:
Copy codeThe Code is as follows:
Select * from xxx where col = [any | all] (select * from xxxx );

This syntax can be divided into two types: keyword addition and keyword addition. When a keyword is not added, the subquery statement returns a discrete value (note that it is a discrete value ), the query statement uses the result of a subquery statement as the condition of its where clause. The syntax can include any, all, some, and other keywords before the subquery statement, the subquery statement returns a set of discrete values. Any indicates that the query statement uses the value returned by the subquery as a range to query the value range, which is similar to the in keyword. The key of all is not well understood, indicates all matched items.
Copy codeThe Code is as follows:
Select * from xxx where col in (select * from xxxx );

The syntax is quite clear, that is, the where clause of the query statement uses the result of the subquery statement as its range, which is similar to the any clause of the previous syntax;
Copy codeThe Code is as follows:
Select row (value1, value2......) = [any] (selectcol1, col2 ..);

The statement is executed in essence: The execution result of the subquery statement matches the query result set. If the matching result can be found, false is returned, the result sets on both sides are discrete values;
Copy codeThe Code is as follows:
Select... where col = [not] exists (select ......);

This statement is poor, and the execution is like this: when a subquery performs an operation and a result is returned, this statement will be executed and how many results are there, the statement is executed several times;
Copy codeThe Code is as follows:
Select... from (select...) as name where ......

This syntax is rarely used at ordinary times and is not easy to understand. In fact, this is the case. A new table is constructed through the execution results of the subquery (this table is a derived data table and a virtual table ), it is used as the object of the main sentence query. This syntax function is very powerful and is often used in some complex queries.

Although the subquery is quite convenient, it has many shortcomings. It does not support limit, and it has been proved by experiments that its execution efficiency is quite unsatisfactory. In general, or subqueries are not recommended.