Shulou(Shulou.com)06/01 Report--

The following is about what is MySQL subquery and how to use it. The secret of the text lies in being close to the topic. So, no gossip, let's just read the following, I believe you will benefit from reading what is MySQL sub-query and how to use this article.

Example:

After modification:

If the sub-query statement of "select 5000" is replaced with "average price of all goods from select", it will have a practical meaning.

The subquery serves the main query

It is only after the subquery obtains certain result data that the main query is executed.

In form, it can be expressed as follows:

Selelct field or expression or subquery [as alias] from table name or link result or subquery where field or expression or subquery condition judgment

That is, subqueries can appear in these locations (where having is also possible, because it has the same meaning as where)

Sub-query, divided by location (occasion):

Type form as the result data of the main query select C1, (select F1 from tab2) as f11 from tab1; # here the subquery should have only one data (one row, one column, scalar quantum query) 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 # here the subquery can be any query result (table subquery) common subquery

Subqueries in comparison operators

Form:

Operand comparison operator (scalar quantum query)

Description:

Operand, which is actually one of the two data of the comparison operator, is usually a field name

Select.... From XXX where id > 5

Example:

Find out the goods with the highest price

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

Subqueries that use in

The use of the previously used in:

XX in (value 1, value 2, value 3.)

Then the in subquery is:

XX in (column subquery)

Example:

Find out all the goods 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'% electricity%')

Subqueries that use any

Form:

Operand comparison operator any (column subquery)

Meaning:

When an Operand (field) satisfies the comparison operator for any one of the values of the column subquery, the condition is satisfied.

Example:

Select * from tab1 where id > any (select F1 from tab2)

Subqueries that use all:

Form:

Operand comparison operator all (column subquery)

Meaning:

The condition is satisfied when an Operand (field) satisfies the comparison operator for all the data values of the column subquery.

Example:

Select * from tab2 where F1 > all (select id from tab1)

Subqueries that use some

In a word: some is synonymous with any.

Queries using exists

Form:

Where exists (subquery)

Meaning:

If the subquery has data, the result of exists is true, otherwise it is false

Description:

Because of the meaning of the queries subquery, it often occurs in the main query that either all or none of them are taken out. If it is limited to this meaning (use case), it basically loses its practical use meaning.

So:

In practical application, the subquery is often not an independent subquery, but needs to establish some kind of relationship with the data source (table) of the "main query"-usually a join relationship. The way of establishment is "implicit", that is, it does not reflect the relationship in the code, but has the "essence" of its connection internally.

This implicit join is usually reflected in the where conditional statement in the subquery, using the data (fields) from the main query table.

Example:

Query all goods with the word "electricity" in their category names in the commodity table

The result is:

Note:

This seed query statement cannot "exist independently (run independently)", but must be used with the main query

-other subqueries can be run independently and get a running result.

-the condition in the subquery should be set to have a certain correlation with a field of the main query, which is usually the "original join condition" of the two tables.

What are the above MySQL subqueries and how to use the relevant content, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.