What are the five forms of MySQL subquery?

MySQL is a relational database management system developed by the Swedish Mysqlab Company and currently belongs to Oracle products. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (Relationaldatabasemanagementsystem, relational database management system) application software for Web applications.

MySQL support subquery from version 4.1, before this version, can use join to write a table query to replace, but it is not recommended to write, rather cumbersome. Here are a few common ways to do MySQL subqueries:

1.select*fromxxxwherecol=[any|all] (select*fromxxxx);

The syntax can be divided into keywords and non-keyword writing,

When you don't add a keyword,

A subquery statement returns a discrete value

(Note is a), the query statement will be the result of the subquery as its own WHERE clause of the query, the syntax can be added to the subquery before the exists, all, and other keywords, when the subquery returns a set of discrete values. Any then indicates that the query

A statement is a range of values returned by a subquery, which is queried within the range of values, similar to the in keyword;

2.select*fromxxxwherecolin (SELECT*FROMXXXX);

This syntax is fairly clear, that is, the query statement WHERE clause is the result of the subquery as its scope, and the previous syntax of any similar.

3.selectrow (Value1,value2 ...) =[any] (selectcol1,col2.);

The essence of the execution of this statement is:

The result of the execution of a subquery statement matches the result set of the query.

If a matching return is found, false is returned, and the result set on both sides is a set of discrete values.

4.select....wherecol=[not]exists (select ...);

The statement is rather lame, and its execution is this: the statement executes when the subquery is in action and returns the result.

and the number of results, the statement is executed several times.

5.select....from (SELECT ...) Asnamewhere ...

This syntax, used in peacetime less, and not good understanding. In fact, this is the case, through the results of sub-query execution to construct

Create a new table (this table is a derived data table, is a virtual table), which is used as the main sentence of the query object,

This syntactic function is very powerful and is often used in complex queries.

The conclusion of the small series:

Although the sub-query is very convenient, but it has many shortcomings, it does not support limit, and the experiment proves that its execution efficiency is quite unsatisfactory, in general, or is not recommended to use sub-query.

What are the five forms of MySQL subquery?