Several common ways to find MySQL subqueries:
Copy Code code as follows:
SELECT * from xxx where col = [Any|all] (SELECT * from XXXX);
This syntax can be divided into keyword and without keyword writing, when the keyword is not added, the subquery returns a discrete value (note is a), the query statement will be the result of the subquery as its own WHERE clause query, the syntax can be added to the subquery before the any, all, Some, at which point the subquery returns a set of discrete values. Any means that the query statement is a range of values returned by the subquery, which is queried in the range of values, similar to the in keyword; all key is not very well understood, which means all matches.
Copy Code code as follows:
SELECT * from xxx where col into (select * from XXXX);
The syntax is quite clear that the WHERE clause of the query statement is scoped by the result of the subquery, similar to any of the previous syntax;
Copy Code code as follows:
Select Row (value1,value2 ...) = [any] (selectcol1,col2 ...);
The execution of the statement is as follows: The result of the execution of the subquery is matched with the result set of the query, if the matching return true is found, the return is false, and the result set on both sides is a set of discrete values;
Copy Code code as follows:
Select ... where col = [NOT] exists (select ...);
The statement is rather lame, its execution is this: when the subquery to operate, there are return results, the statement will be executed, and how many results, the statement executed several times;
Copy Code code as follows:
Select .... from (select ...) as name where .....
The syntax is less used in peacetime and is not well understood. In fact, this is the case, the result of the subquery execution is to construct a new table (the table is a derived data table, a virtual table) that is used as the object of the query for the main sentence, which is very powerful and is often used in some complex queries.
Although the subquery is very convenient, but it has a lot of shortcomings, it does not support limit, and the experiment proved that its execution efficiency is quite unsatisfactory, in general, or is not recommended to use subqueries.
