Several common forms of mysql queries
mysql Several common ways of writing sub-queries:
The code is as follows:
select * from xxx where col=[any|all](select * from xxxx);
The syntax can be divided into the wording of adding keywords and not adding keywords.When no keywords are added, the sub-query statement returns a discrete value (note that it is one), the query statement Use the result of the subquery as the condition of its own where clause for query.This syntax can add any, all, some and other keywords before the subquery.At this time, the subquery returns a set of discrete values.Any means that the query statement takes the value returned by the subquery as a range, and the query is performed within this value range, which is similar to the in keyword; the all key is not well understood, which means that all matches.
The 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 takes the result of the subquery as its scope, which is similar to the any of the previous syntax;
The code is as follows:
select row(value1,value2.....)=[any](selectcol1,col2..);
The essence of the execution of the statement is: the result of the execution of the subquery statement matches the result set of the query, if a match is found, it returns true, otherwise it returns false, and the result sets on both sides are Is a set of discrete values;
the code is as follows:
select ....where col=[not] exists (select...);
This statement is lame, and its execution is like this: When the subquery is operating and there are results returned, the statement It will be executed, and the statement will be executed as many times as there are results;
The code is as follows:
select....from (select.....) as name where......
This syntax is less commonly used and is not easy to understand.In fact, it is the same thing.Construct a new table (this table is a derived data table, a virtual table) based on the results of the subquery execution, which is used as the object of the main sentence query, the The syntax function is very powerful, and it is often used in some complex queries.
Although subqueries are very convenient, they have many shortcomings.They do not support limit, and experiments have proved that their execution efficiency is quite unsatisfactory.Under normal circumstances, it is not recommended to use subqueries.
Tags
Technical otaku
Sought technology together
0 Comments