MySQL Learning Share--query---subquery

A subquery is the nesting of another query statement in a query statement.

The query part outside the subquery is referred to as an external query.

The subquery must contain parentheses.

Any, in, some

The Any keyword must be used with a comparison operator, which means "to compare the values of the returned columns in a subquery, and to return true if one of the values is true", let's look at a query instance,

Select Id_temp from T_user_collect where commodity_id > A (select id from t_commodity); Get the following results,

We look at the data of the two tables to analyze why the above results are obtained, the data of the T_user_collect table is as follows,

The data for the T_commodity table is as follows

First we have defined the meaning of the Any keyword, and the commodity_id column in the T_user_collect table is compared to the value of the ID column in the t_commodity table, as long as commodity_id is greater than one of the IDs, commodity The Id_temp column value for the row where the _id column value is located is queried.

As can be seen from the data in the table, the value of commodity_id column 1 cannot be greater than any one of the values in the ID column, so the value of the row (in this case, commodity_id column) is not queried, and the other commodity_id column values (6, 3, 4) Values greater than are found in the ID column, so they (referred to as commodity_id columns) are associated with Id_temp column values that are queried.

The meaning of the In keyword is "Only return records containing these values", which is equivalent to "= any", so the following two SQL is equivalent,

Select Id_temp from t_user_collect where commodity_id = any (select id from t_commodity);

Select Id_temp from T_user_collect where commodity_id in (select ID from t_commodity);

The result of the query is,

These results are obtained because the values in the commodity_id column (1, 6, 3, 4) are included in the ID column.

Why say "in" and "= any" are equivalent, let us analyze, "= any" means "as long as any one of the values in the specified value will return true", and the obvious t_user_collect table in the COMMODITY_ID column value can be t_ The ID column of the commodity table matches the value to equal.

The Some keyword and the Any keyword are equivalent, and the reason for using some under some conditions is to express the meaning of "partial inequality", so the following SQL is equivalent,

Select Id_temp from T_user_collect where commodity_id <> A (select ID from t_commodity);

Select Id_temp from T_user_collect where commodity_id <> some (select ID from t_commodity);

The query results are,

Here is how to understand, in fact, when the COMMODITY_ID column value matches the value in the ID column, as long as there is an inequality, its (this refers to the commodity_id column) the row of the Id_temp column value will be queried.

The meaning of "<> any" here is "return true if not equal to any of the values in the specified value."

The meaning of "<> some" here is "return true if not equal to some of the values in the specified value."

All

The all keyword, which also needs to be used with the comparison operator, means "to compare the values in the returned columns of a subquery, all of which are true, to return true", to look at an instance,

Select Uc.id_temp from T_user_collect UC where uc.commodity_id > All (select C.id from t_commodity c);

The query results are,

Because the maximum value of the T_user_collect table commodity_id column is 6, and the maximum value of the T_commodity table ID column is 9, the value of each commodity_id column cannot be greater than all the ID column values, so the result is null

When we use the <> symbol for all, we want to look at an instance of the data that is not included in the subquery in the outer query.

Select Uc.id_temp from T_user_collect UC where uc.commodity_id <> all (select C.id from t_commodity c);

The query results are,

Let us understand that only if the value in the commodity_id column is not equal to the value in all the ID columns, the id_temp column value of the row in which the commodity_id column is located is queried, but commodity_ The value in the ID column has a matching relationship with the value in the ID column, so the result of the query is null.

In fact <> all also has an alias, is not in, the latter is clearly easier to understand, that is, does not contain, not inside the meaning, then for the first SQL can be so understood, "see commodity_id column in the value of what is not in the ID column", So the following two SQL are equivalent,

Select Uc.id_temp from T_user_collect UC where uc.commodity_id <> all (select C.id from t_commodity c);

Select Uc.id_temp from T_user_collect UC where uc.commodity_id not in (select C.id from t_commodity c);

Classification of sub-queries

Sub-queries can be divided into: scalar subquery, multivalued subquery, according to the number of expected values

Depending on the dependencies on the external query, you can divide the subquery into: independent subquery, correlated subquery

The relationship between them is a scalar subquery and a multivalued subquery can be a standalone subquery or a correlated subquery

The scalar subquery returns a single value, and if the scalar subquery returns multiple values, then MySQL throws an error "the subquery returns more than one row of data"; The multivalued subquery returns a multi-valued collection.

A standalone subquery is a subquery that runs without relying on an external query. How to understand that this subquery can be run alone without any interaction with the external query.

MySQL for an in clause optimization, if it is not an explicit list definition, such as in (1,2,3,4,5), then the IN clause will generally be converted to the EXISTS clause, the process is to convert the independent subquery to the relevant subquery.

A correlated subquery refers to a subquery that references an outer query column, such as the following SQL,

Select Id_temp from T_user_collect UC where exists (SELECT * from t_commodity c where c.id=uc.commodity_id);

The "c.id=uc.commodity_id" in the subquery refers to the commodity_id column of the external query, and explain can see that the select_type of the subquery is dependent subquery (correlated subquery),

Correlated subqueries Compare the columns of an external query more than once, so for the optimization of correlated subqueries We should start by reducing the number of comparisons with external queries. Based on this, sometimes we can rewrite SQL by deriving a table. So when we determine if we need to optimize for subqueries, we can use explain to look at the SQL execution plan, not to be fooled by the illusion that SQL execution is short due to the small amount of data, and try to test your SQL with the largest amount of data possible.

EXISTS

Used to check whether a specified query produces some rows, exists is typically followed by a subquery, and the subquery associates an external query. Depending on whether the subquery returns rows, exists only returns TRUE or false, without returning unknown.

It is not generally recommended to use * in SQL, but in exists we can do this because exists only cares if the row exists, not the values of the columns.

Additional Knowledge points

"A SQL optimization recommendation to replace the in statement with exists" is that a familiar phrase? Is it a truth? It may be useful in some older versions of MySQL, but at this stage MySQL has the same execution plan for in and exists in the vast majority of cases, so you will find that even if you replace in with the exists effect is the same. Therefore, we have to the others summed up the theorem, it is best to maintain a proof of the heart, let the facts to persuade themselves.

Select Id_temp from T_user_collect UC where commodity_id in (select ID from t_commodity t);

Select Id_temp from T_user_collect UC where exists (SELECT * from T_commodity t where t.id=uc.commodity_id);

Derived tables

Derived tables are also referred to as table subqueries, which means that derived tables appear in the FROM clause just like other tables.

How did the derived table come about? It comes from a virtual table derived from a subquery. Therefore it is completely virtual and is not physically stored on disk. Because this feature may cause explain to be executed, it can take a long time because the optimizer does not know the information for this table.

It will have some restrictions on use,

The name of the ① column must be unique, and the following SQL is not supported for derived tables.

Select Id_temp from T_user_collect, (select Id_temp as a, user_id_temp as a from t_user_collect) as T; MySQL will prompt for error, "duplicate column name".

② in some cases, the derived table does not support the use of limit, such as when an external query uses in, all, any, some;

③ each derived table must have its own alias, the following SQL will prompt this error for MySQL,

Select Id_temp from T_user_collect, (select Id_temp as a, user_id_temp as a from t_user_collect);

Additional Knowledge points

What is the relationship between a subquery and a derived table?

In fact, the two from the name of the larger difference, the former is a query statement, and the latter is a virtual table. We can rewrite the subquery as a derived table. When you are sure that you need a derived table, you should give it an alias, and you do not need to do this for subqueries.

MySQL Learning Share--query---subquery