Detailed introduction to MySQL subquery

MySQL is a relational database management system developed by the Swedish company MySQLAB and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In terms of web applications, MySQL is the best RDBMS (Relational Database Management System) application software.

## MySQL has supported the subquery function since version 4.1. Before this version, you can use join to write a table query instead, but it is not recommended to write this way, which is quite troublesome. . The following are several common ways to write mysql subqueries:

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

This syntax can be divided into adding keywords and not How to write keywords,

When no keywords are added,

The subquery statement returns a discrete value

(note that it is one), and the query statement will Use the result of the subquery statement as the condition of your where clause to query. This syntax can add keywords such as any, all, exists before the subquery statement. At this time, the subquery statement returns a set of discrete values. any means, query

The statement uses the value returned by the subquery as a range, and queries within this value range, which is similar to the in keyword; the all key is not easy to understand, and it means what all matches of.

2.select*fromxxxwherecolin(select*fromxxxx);

The syntax is quite clear, that is, the where clause of the query statement uses the result of the subquery statement as its scope, which is the same as the above A grammatical similarity to any.

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

The execution essence of this statement is:

The result obtained from the execution of the subquery statement matches the result set of the query.

If a match can be found, it returns true, otherwise it returns false, and the result sets on both sides are a set of discrete values.

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

This statement is lame, and its execution is like this: when the subquery is executed Operation, the statement will be executed only when there is a result returned,

And the statement will be executed as many times as there are results.

5.select....from(select....)asnamewhere......

This syntax is rarely used in daily life and is not easy to understand. . In fact, this is the case. A new table is constructed through the results of subquery execution (this table is a derived data table, a virtual table), which is used as the main table. The query object of the sentence,

This syntax is very powerful and is often used in some complex queries.

Editor’s conclusion:

Although subquery is very convenient, it has many shortcomings. It does not support limit, and experiments have proved that its execution efficiency is quite unsatisfactory. Under normal circumstances, , it is still not recommended to use subqueries.

The above is the detailed content of Detailed introduction to MySQL subquery. For more information, please follow other related articles on the PHP Chinese website!