5 ways of MySQL Sub Query

 MySQL supports the sub query function since version 4.1. Here are some common ways to write MySQL sub queries:

select * from xxx where col = [any|all](select * from xxxx);

This can be divided into the writing with and without keywords.

When no keyword is added, the sub query statement returns a discrete value (note one). The query statement will use the result of the sub query statement as the condition of its “where” clause.

“Any” means that the query statement takes the value returned by the sub query as a range, and queries within this range, similar to the “in” keyword.

select * from xxx where col in (select * from xxxx);

The “where” clause of a query statement takes the result of a sub query statement as its scope.

select row(value1,value2…..) = [any](selectcol1,col2..);

The result of the execution of the sub query statement matches the result set of the query. If a match can be found, it returns true. Otherwise, it returns false. The result sets on both sides are a set of discrete values.

select …. where col = [not] exists (select……);

The statement will only be executed when there are results returned from the sub query, and the statement will be executed several times as many results as there are.

select …. from (select …..) as name where ……

A new table is constructed from the results of sub query execution, which is used as the query object of the main sentence. This function is very powerful, and it is often used in some complex queries.

Although the sub query is very convenient, it has many shortcomings. It does not support limit, and its execution efficiency is not ideal through experiments. In general, it is not recommended to use sub query.