Location of sub-query:
Select in, from, and where. There is no practical meaning in group by and order by.
Sub-queries fall into the following categories:
1, scalar quantum query: Returns the single value of scalars, 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
Scalar Quantum query: refers to a subquery that returns a single value, 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 column1 = (select MAX (column2) from T2)
SELECT * from article as t where 2 = (SELECT COUNT (*) from article WHERE article.uid = T.uid)
MySQL column query: The result set returned by the subquery is a row of N rows, which is usually returned from a field query on the 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
Example: You can use the in, any, SOME, and all operators, and you cannot directly use the = > < >= <= <> These operators that compare scalar results.
Example:
SELECT * FROM article WHERE uid in (SELECT uid from user where Status=1)
Select S1 from table1 WHERE S1 > No (select s2 from table2)
Select S1 from table1 WHERE S1 > All (select S2 from table2)
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 table2)
MySQL Row subquery: The result set returned by the subquery is a row of N columns, the result of 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: (up to) equals row
SELECT * from article where (title,content,uid) = (select Title,content,uid from blog where bid=2)
MySQL Table subquery: 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)
Sub-query Optimizations:
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 T1
WHERE t1.a1 not in (SELECT A2 from T2)
After optimization:
SELECT * from T1
Left JOIN T2 on T1.A1=T2.A2
WHERE T2.a2 is NULL
Example 2:
SELECT * FROM article WHERE (title,content,uid) in (select Title,content,uid from blog)
After optimization:
SELECT * FROM article
INNER 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.
