A brief introduction to mysql subquery

The basic meaning of a subquery is a "query statement" that appears in certain positions inside a query statement (select statement). This article mainly shares with you a brief introduction to mysql subqueries, hoping to help everyone.

Example:

After modification:

If the subtitle of "select 5000" is The query statement, replaced by "the average price of all selected products", has practical meaning.

• The subquery serves the main query

• The main query is executed only after the subquery obtains certain result data. Query;

In form, it can be expressed as follows:

• selelct field or expression or subquery [as alias] from table name Or link results or subqueries where fields or expressions or conditional judgments of subqueries

That is, subqueries can appear in these positions (having actually can also be used, because It has the same meaning as where);

Subquery, divided by position (occasion):

Type	Form
#As the result data of the main query	select c1,(select f1 from tab2) as f11 from tab1; #The subquery here should only have A piece of data (one row, one column, scalar subquery)
As the conditional data of the main query	select c1 from tab1 where c1 in (select f1 from tab2); #here The subquery can be multiple data (multiple rows and one column, column subquery)
As the source data of the main query	select c1 from (select f1 as c1, f2 from tab2) as t2; #The subquery here can be any query result (table subquery)

Common subqueries

Subqueries in comparison operators

Form:

• Operand comparison operators (scalar subquery);

Explanation:

• The operand is actually one of the two data of the comparison operator, usually A field name;

  select ....  from XXX where  id > 5;

Example:

• Find the highest priced product;

select * from product  where  price = (select  max(price)  from  product );

Subquery using in

• Previous usage of in:

XX  in  (值1，值2，值3，....）；

• Then the in subquery is:

XX  in  (列子查询)

Example:

• Find all products with the word "electricity" in all category names;

select  *  from  product  where  protype_id  in( 
select  protype_id  from  product_type  where  protype_name  like ‘%电%’
);

Subquery using any

Form:

操作数 比较运算符 any  (列子查询)；

Meaning:

• When a certain operand (field) satisfies the comparison operator for any value of the column subquery, the condition is satisfied;
  Example:

select  *  from  tab1  where  id >  any  (select  f1  from  tab2);

Subquery using all:

Form:

操作数  比较运算符 all   (列子查询)；

Meaning:

• When a certain operand (field) For all data values ​​of the column subquery, the comparison operator must be satisfied to satisfy the condition;

Example:

select  *  from  tab2  where  f1  >  all  ( select  id  from   tab1 );

Use some subquery Query

• One sentence: some is a synonym of any.

Query using exists

Form:

where  exists( 子查询 )

Meaning:

• If the subquery "has data", the results of exists is true, otherwise it is false

Explanation:

Because, the exists subquery Meaning, the main query often has a situation like this: either all of them are taken out, or none of them are taken out. If it is limited to this meaning (usage situation), it basically loses its practical significance.

So:

In actual applications, this subquery is often not an independent subquery, but needs to be followed by the data source (table of the "main query") ), establish some kind of relationship - usually a connection relationship. The way of establishment is "implicit", that is, the relationship is not reflected in the code, but there is the "substance" of its connection internally.

• This implicit connection method is usually reflected in the where conditional statement in the subquery, using the data (fields) in the main query table;

Example:

• Query all products with the word "electricity" in their category names in the product table;
  

The result is:

Note:
- This kind of subquery statement cannot "exist independently (run independently)", but must be used together with the main query;
- Other subqueries can be run independently and will get a running result.
- The conditions in this subquery should be set to have a certain correlation with a certain field of the main query. Usually this judgment is the "original connection condition" of the two tables

Finally A conclusion:

If a query requirement can be obtained using a join query or a subquery, it is usually recommended to use join interpolation, which is more efficient.

Related recommendations:

Detailed introduction to MySQL subquery

##What is a mysql subquery? How to filter using subqueries?

Optimization of MySQL subquery

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