MySQL inside the subquery

One, sub-query definition

　　Defined:

Subqueries allow a query to be nested within another query.

subqueries, also called internal queries, are called external queries, as opposed to internal queries, which contain internal queries.

A subquery can contain any clauses that a normal select can include, such as: Distinct, group BY, order by, limit, join, and union, and so on, but the corresponding external query must be one of the following: SELECT, INSERT, update , delete, set, or do.

　　Location of sub-query:
Select in, from, and where. There is no practical meaning in group by and order by.

Second, sub-query classification

　　Sub-queries fall into the following categories:
1. Scalar Quantum query: Returns a single value in the simplest form.
2. Column query: The result set returned is a row of N rows.
3. Row subquery: The result set returned is a row of N columns.
4. Table subquery: The result set returned is n rows n columns.
Available operators: = > < >= <= <> any in SOME all EXISTS

A subquery returns a scalar (in the case of a value), a row, a column, or a table called scalar, row, column, and table sub-queries.

If a subquery returns a scalar value (in the case of a value), the external query can be compared using: =, >, <, >=, <=, and <> symbols, and if the subquery returns a scalar value, An exception is thrown when an external query is compared with the results of a comparer and a subquery.

　　1. Scalar Quantum query:

Refers to a scalar of a single value returned by a subquery, such as a number or a string, and is the simplest form of return in a subquery. You can use the = > < >= <= <> These operators to compare the scalar results of subqueries, usually the position of the subquery is on the right side of the comparison
Example:

SELECT * FROM article WHERE uid = (select uid from user where Status=1 ORDER by uid DESC LIMIT 1) SELECT * from T1 WHERE Co Lumn1 = (select MAX (column2) from T2) SELECT * from article as t where 2 = (select COUNT (*) from article WHERE Article.uid = T.uid)

2. MySQL query:

The result set returned by the subquery is a column of N rows, which is typically returned from a field query on a table.
You can use the = > < >= <= <> These operators to compare the scalar results of subqueries, usually the position of the subquery is on the right side of the comparison
　　　　You can use the in, any, SOME, and all operators, and you cannot directly use the = > < >= <= <> These operators that compare scalar results.
Example:

Not in is the alias of <> all, and the same is the same.
Special cases
If Table2 is an empty table, the result after all is TRUE;
If the subquery returns the result of a blank row, such as (0,null,1), although the S1 is larger than the returned result, then all the result is UNKNOWN.
Note: For table2 empty tables, the following statement returns null:

Select S1 from table1 where s1 > (select s2 from table2) SELECT S1 from table1 WHERE S1 > All (select MAX (S1) from TA Ble2)

3. MySQL Row Sub-query:

The result set returned by a subquery is a row of N columns, which is usually the result set returned by querying a row of data for a table.
Example:

SELECT * FROM table1 where (in) = (SELECT column1, column2 from table2) Note: (a) is equivalent to row select * from article WHERE (t ITLE,CONTENT,UID) = (SELECT title,content,uid from blog WHERE bid=2)

4. MySQL Table Sub-query:

The result set returned by a subquery is a table data of n rows n columns.
Example:

SELECT * FROM article WHERE (title,content,uid) in (select Title,content,uid from blog)

three, the word query example to give

1. Any sub-query

　　The Any keyword means "returns true for any one of the columns returned by the subquery if the comparison evaluates to True."

Like "Ten >any (11, 20, 2, 30)", because of 10>2, the judgment returns True, as long as 10 is compared with any one in the collection, and true is returned when True is obtained.　　

Select Table1.customer_id,city,count (order_id) from table1 join Table2on Table1.customer_id=table2.customer_idwhere Table1.customer_id<> ' TX ' and table1.customer_id<> ' 9you ' GROUP by customer_idhaving count (order_id) > Any (select count (order_id) from Table2where customer_id= ' tx ' or customer_id= ' 9you ' Group by customer_id);

Any of the meaning is better understood, literal translation is any one, as long as the condition satisfies any one, returns True.

　　2. Subqueries using in

Using in for subqueries, which we often encounter when writing SQL everyday. In means that the specified value is in this set, and returns True if it is, otherwise it returns false.

In is the alias of "=any", where "=any" is used, we can use "in" to replace.

With in, there must be no in;not in and <>any is not the same meaning, not in and <>all is a meaning.

　　3. Subqueries using some

Some is the alias of any, with less.
　　

　　4. Subqueries with all

All must be used with the comparison operator. All means "returns true for all values in the column returned by the subquery if the comparison evaluates to True."

Like "Ten >all (2, 4, 5, 1)", because 10 is greater than all values in the set, this judgment returns True, and if it is "ten >all (20, 3, 2, 1, 4)", then the judgment returns false because 10 is less than 20.

The synonym of <>all is not in, which means that it is not equal to all the values in the collection, this is easy to mix with <>any, usually more than a few snacks to stay.

　　5. Scalar Quantum Query

Depending on the number of values returned by the subquery, the subquery can be divided into a scalar subquery and a multi-valued subquery. When using a comparer for subqueries, it is required to be a scalar subquery, or, in the case of a multi-valued subquery, an exception is thrown when the comparer is used.

　　6. Multi-valued subquery

A multivalued subquery corresponds to a scalar subquery, and a multivalued subquery returns a column, a row, or a table that makes up a set of values. We generally use the words "any", "in", "All" and "some" to determine the results of the external query and subquery. If you query the word "any", "in", "All" and "some" with the scalar quantum, you will get an empty result.

　　7. Independent sub-query

A standalone subquery is a subquery that runs without relying on an external query. What does it mean to rely on external queries? Let's look at the following two SQL statements.

SQL statement 1: Get the order number of all Hangzhou customers.　

Select order_id from table2 where customer_id in          (select customer_id from           table1           where city= ' Hangzhou ');

SQL Statement 2: Get the city for Hangzhou, and there are orders for the user.

SELECT * FROM table1 where city= ' Hangzhou ' and exists                (SELECT * from                 table2                 where table1.customer_id= TABLE2.CUSTOMER_ID);

The above two SQL statements, although the examples are not very appropriate, but enough to illustrate the problem here.

For SQL statement 1, we copy the subquery separately, and we can do it separately, that is, the subquery has nothing to do with the external query.

For SQL Statement 2, we will not be able to execute the subquery separately, because the subquery of SQL Statement 2 relies on some fields of the external query, which results in the dependency of the subquery on the external query.

For subqueries, there are many times when efficiency issues are taken into account. When we execute a SELECT statement, we can add the Explain keyword to see the query type, the index used in the query, and so on. For example, use this:

Explain select order_id from   table2   where customer_id in            (select customer_id from             table1             where city= ' Hangzhou ');

With a stand-alone subquery, if the subquery part has a maximum traversal of n for the collection, and the maximum number of traversal for an external query is m, we can remember this as: O (m+n). If you use a correlated subquery, it may traverse the number of O (m+m*n). It can be seen that efficiency will be doubled; So, when you use subqueries, be sure to consider the relevance of subqueries.

　　8. Related sub-query

A correlated subquery is a subquery that references an outer query column, that is, a subquery evaluates each row of an external query once. However, within MySQL, dynamic optimization will be performed, depending on the situation. Using correlated subqueries is where performance is most likely to occur. And about the optimization of SQL statements, this is a very big topic, only through the actual accumulation of experience in order to better understand how to optimize.

9.EXISTS predicates

Exists is a very bull-fork predicate that allows the database to efficiently check whether a specified query produces some rows. The predicate returns TRUE or false, depending on whether the subquery returns a row. Unlike other predicates and logical expressions, exists does not return unknown, and unknown is false for exists, regardless of whether the input subquery returns rows. Or the above statement, get the city for Hangzhou, and there are orders for the user.

SELECT * FROM table1 where city= ' Hangzhou ' and exists                (SELECT * from                 table2                 where table1.customer_id= TABLE2.CUSTOMER_ID);

The main difference between in and exists is in the judgment of the three-valued logic. exists always returns TRUE or false, and for in, it is possible to return unknown to a null value in addition to the true and false values. In the filter, however, the unknown is handled in the same way as false, so using in is like using exists, and the SQL Optimizer chooses the same execution plan.

Speaking of in and EXISTS is almost the same, but you have to talk about not in and not EXISTS, for the input list contains null values, the difference between not EXISTS and not is very large. When the input list contains null values, in always returns True and UNKNOWN, so not-in will get not true and not UNKNOWN, that is, false and UNKNOWN.

　　10. Derived tables

As mentioned above, it is also possible to return a table in the value returned by the subquery, which becomes a derived table if the virtual table returned by the subquery is entered again as the FROM clause. The syntax structure is as follows:

From (subquery expression) as Derived_table_alias

Because derived tables are full virtual tables, they are not and cannot be physically materialized.

Iv. optimization of sub-query

Subqueries are required in many queries. You can use subqueries to do many SQL operations that logically require multiple steps to complete, and also to avoid transaction or table locking. Subqueries can make query statements very flexible, but the execution of subqueries is not efficient.

subquery, MySQL needs to establish a temporary table for the query results of the inner query statement. The outer query statement then queries the record in the temporary table. After the query is complete, MySQL needs to revoke these temporary tables. As a result, the speed of the subquery is affected somewhat. If the amount of data queried is large, the effect increases accordingly.

In MySQL, you can use connection queries to replace subqueries. Connection queries do not need to establish temporary tables, which are faster than subqueries.

Use connection (join) instead of subquery
Such as:

Example 1:select * from T1where t1.a1 No in (select A2 from T2) optimized: SELECT * from T1 left joins T2 on T1.a1=t2.a2 WHERE t2.a2 I S NULL   

Example 2:select * from Article WHERE (title,content,uid) on (select Title,content,uid from blog) Optimized: SELECT * from Articleinner Join blog on (Article.title=blog.title and Article.content=blog.content and Article.uid=blog.uid)

Sub-queries that cannot be optimized:
1, MySQL does not support sub-query merging and aggregation function sub-query optimization, MARIADB to the aggregate function sub-query materialized optimization;
2, MySQL does not support the from sub-sentence query optimization, mariadb to the from sub-sentence query sub-query pull-up optimization;
3, MySQL and mariadb sub-query expansion to provide limited support, such as the operation of the primary key to the pull-up sub-query optimization;
4, MySQL does not support exists sub-query optimization, MARIADB exists correlated sub-query semi-connection optimization, exists non-correlated subquery is not further optimized;
5, MySQL and mariadb do not support NOT EXISTS sub-query optimization;
6, MySQL and mariadb to in sub-query, to satisfy the semi-connection semantics of the query semi-connected optimization, and then based on the cost evaluation of the optimization, the cost of the two-link evaluation of the choice of different ways;
7, MySQL does not support the non-in sub-query optimization, MARIADB is not associated with the sub-query using materialized optimization, does not optimize the association of not in sub-query;
8. mysql and mariadb use the Max function for >all non-correlated subqueries, and <all non-correlated subqueries Use the MIN function to use exists optimization for =all and non-correlated subqueries;
9. For >some and >any non-correlated subqueries using the Min function, the Max function is used for <some and <any non-correlated subqueries, and =any and =some subqueries are optimized using semi-joins for >some and > Any associated subqueries and <some and <any associated subqueries are only exists optimized.

Http://www.cnblogs.com/loveyouyou616/archive/2012/12/21/2827655.html

MySQL inside the subquery