Basic use of subqueries in MySQL

1. Subquery definition

definition:

Subqueries allow you to nest one query within another.

A subquery, also called an inner query, is called an outer query compared to an inner query.

A subquery can contain any clause that a normal select can include, such as distinct, group by, order by, limit, join, and union; but the corresponding outer query must be one of the following statements: select, insert, update, delete, set, or do.

Position of subquery: in select, after from, in where. It has no practical significance in group by and order by.

2. Subquery Classification

Subqueries are divided into the following categories:
1. Scalar subquery: A scalar subquery that returns a single value, the simplest form.
2. Column subquery: The returned result set is N rows and one column.
3. Row subquery: The returned result set is one row with N columns.
4. Table subquery: The returned result set is N rows and N columns.

Available operators: = > < >= <= <> ANY IN SOME ALL EXISTS

A subquery returns a scalar (just a value), a row, a column, or a table. These subqueries are called scalar, row, column, and table subqueries.

If the subquery returns a scalar value (just one value), the outer query can use the symbols: =, >, <, >=, <=, and <> for comparison. If the subquery does not return a scalar value, and the outer query uses a comparison operator to compare the result of the subquery, an exception will be thrown.

1. Scalar subquery:

It means that the subquery returns a single value scalar, such as a number or a string, and is also the simplest return form in a subquery. You can use the operators = > < >= <= <> to compare the scalar results of subqueries. Usually the subquery is placed 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)

2. MySQL subquery:

It means that the result set returned by the subquery is N rows and one column. The result is usually returned from a query on a field of the table.
You can use operators such as => < => <= <> to compare the scalar results of a subquery. Usually, the subquery is placed on the right side of the comparison expression and you can use operators such as IN, ANY, SOME, and ALL. You cannot directly use operators such as => < => <= <> to compare scalar results.
Example:

 SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

NOT IN is an alias for <> ALL and they are the same.

Special circumstances

• If table2 is an empty table, the result after ALL is TRUE;
• If the subquery returns a result such as (0,NULL,1) where s1 is larger than the returned result but there are blank rows, the result after ALL is UNKNOWN.

Note: If table2 is empty, the following statements will return NULL:

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

3. MySQL row subquery:

It means that the result set returned by the subquery is one row and N columns. The result of the subquery is usually the result set returned by querying a row of data in the table.

example:

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
Note: (1,2) is equivalent to row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4. MySQL table subquery:

It means that the result set returned by the subquery is a table data with N rows and N columns.

example:

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

3. Examples of word search

1. ANY subquery

The any keyword means "for any value in the column returned by the subquery, if the comparison result is TRUE, return TRUE."

For example, "10 >any(11, 20, 2, 30)", since 10>2, this judgment will return TRUE; as long as 10 is compared with any one in the set and TRUE is obtained, it will return TRUE.

select table1.customer_id,city,count(order_id)
from table1join table2
on table1.customer_id=table2.customer_id
where table1.customer_id<>'tx' and table1.customer_id<>'9you'
group by customer_id
having count(order_id) >
any (
select count(order_id)
from table2
where customer_id='tx' or customer_id='9you'
group by customer_id);

The meaning of any is relatively easy to understand. It literally means any one. As long as any one of the conditions is met, TRUE is returned.

2. Use IN for subqueries

Using in for subqueries is something we often encounter when writing SQL in our daily lives. in means whether a specified value is in this set, if so, it returns TRUE; otherwise, it returns FALSE.

in is an alias for “=any”. We can use “in” to replace it wherever “=any” is used.

If there is in, there must be not in; not in does not have the same meaning as <>any, not in and <>all have the same meaning.

3. Use SOME for subquery

Some is an alias of any and is less commonly used.

4. Use ALL for subqueries

all must be used with a comparison operator. all means "for all values ​​in the column returned by the subquery, if the comparison evaluates to TRUE, return TRUE".

For example, "10 >all(2, 4, 5, 1)", since 10 is greater than all values ​​in the set, this judgment returns TRUE; and if it is "10 >all(20, 3, 2, 1, 4)", then since 10 is less than 20, the judgment will return FALSE.

The synonym of <>all is not in, which means not equal to all values ​​in the set. This is easily confused with <>any, so just pay more attention to it.

5. Scalar Subquery

According to the number of values ​​returned by the subquery, subqueries can be divided into scalar subqueries and multi-value subqueries. When using a comparison operator for a subquery, it must be a scalar subquery; if a comparison operator is used for a multi-value subquery, an exception will be thrown.

6. Multi-value subquery

The corresponding to the scalar subquery is the multi-value subquery, which returns a column, a row, or a table, which form a set. We generally use words such as any, in, all, and some to judge the results of the outer query and the subquery. If you use the words any, in, all, and some with a scalar subquery, you will get empty results.

7. Independent subquery

An independent subquery is a subquery that runs without relying on an outer query. What does it mean to rely on external queries? First look at the following two SQL statements.

SQL statement 1: Get the order numbers of all hangzhou customers.

select order_id
from table2
where customer_idin
          (select customer_id
          from table1
          where city='hangzhou');
   SQL statement 2: Get users whose city is hangzhou and who have orders.
 
select *
from table1
where city='hangzhou' and exists
                (select *
                from table2
                where table1.customer_id=table2.customer_id);

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

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

For SQL statement 2, if we copy the subquery separately, we cannot execute it separately. Since the subquery of SQL statement 2 depends on certain fields of the outer query, the subquery depends on the outer query, which creates a correlation.

For subqueries, efficiency is often a consideration. When we execute a select statement, we can add the explain keyword to view the query type, the index used during the query, and other information. For example, use this:

explainselect order_id
  from table2
  where customer_idin
            (select customer_id
            from table1
            where city='hangzhou');

Using independent subqueries, if the maximum number of traversals of the subquery part to the collection is n, and the maximum number of traversals of the outer query is m, we can record it as: O(m+n). If a correlated subquery is used, the number of traversals may reach O(m+m*n). As you can see, the efficiency will drop exponentially; therefore, when using subqueries, you must consider the relevance of the subqueries.

8. Correlated Subqueries

A correlated subquery is a subquery that references columns in an outer query, that is, the subquery is calculated once for each row in the outer query. However, dynamic optimization is performed inside MySQL, which may vary depending on the situation. Using correlated subqueries is where performance issues are most likely to occur. As for the optimization of SQL statements, this is a very large topic. Only through the accumulation of practical experience can we better understand how to optimize.

9.EXISTS predicate

EXISTS is a very powerful predicate that allows the database to efficiently check whether a given query produces certain rows. This predicate returns TRUE or FALSE, depending on whether the subquery returns rows. Unlike other predicates and logical expressions, EXISTS will not return UNKNOWN, regardless of whether the input subquery returns rows. For EXISTS, UNKNOWN is FALSE. Using the above statement, we can get users whose city is hangzhou and who have orders.

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 lies in the judgment of three-valued logic. EXISTS always returns TRUE or FALSE, while for IN, in addition to TRUE and FALSE values, it may also return UNKNOWN for NULL values. However, in filters, UNKNOWN is treated the same as FALSE, so the SQL optimizer will choose the same execution plan when using IN as when using EXISTS.

We have mentioned that IN and EXISTS are almost the same, but we have to talk about NOT IN and NOT EXISTS. When the input list contains NULL values, the difference between NOT EXISTS and NOT IN becomes very significant. 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, a table may also be returned in the value returned by the subquery. If the virtual table returned by the subquery is used as the input of the FROM clause again, the virtual table of the subquery becomes a derived table. The grammatical structure is as follows:

FROM (subquery expression) AS derived_table_alias

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

4. Subquery Optimization

Many queries require the use of subqueries. Using subqueries can complete many SQL operations that logically require multiple steps to complete at one time, and can also avoid transaction or table locks. Subqueries can make query statements very flexible, but the execution efficiency of subqueries is not high.

When using a subquery, MySQL needs to create a temporary table for the query results of the inner query statement. Then the outer query statement queries the records in the temporary table. After the query is completed, MySQL needs to drop these temporary tables. Therefore, the speed of the subquery will be affected to a certain extent. If the amount of data being queried is large, this impact will increase accordingly.

In MySQL, you can use join queries instead of subqueries. A join query does not require the creation of a temporary table and is faster than a subquery.

Use JOIN instead of subquery

like:

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
innerjoin blog
on (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)

Subqueries that cannot be optimized:
1. MySQL does not support subquery merging and aggregate function subquery optimization, while MariaDB performs materialized optimization on aggregate function subqueries;
2. MySQL does not support from sub-clause query optimization, while MariaDB performs sub-query pull-up optimization on from sub-clause queries;
3. MySQL and MariaDB provide limited support for subquery expansion. For example, only operations on primary keys can be used to optimize pull-up subqueries.
4. MySQL does not support EXISTS subquery optimization. MariaDB performs semi-join optimization on EXISTS associated subqueries, but does not further optimize EXISTS non-associated subqueries.
5. MySQL and MariaDB do not support NOT EXISTS subquery optimization;
6. MySQL and MariaDB perform semi-join optimization on IN subqueries and queries that satisfy semi-join semantics, and then optimize based on cost evaluation. The two have different cost evaluation selection methods for semi-joins;
7. MySQL does not support not in subquery optimization. MariaDB uses materialized optimization for non-correlated not in subqueries and does not optimize correlated not in subqueries.
8. MySQL and MariaDB use the max function for non-correlated subqueries > all, the min function for non-correlated subqueries < all, and the exists optimization for = all and non-correlated subqueries;
9. Use the min function for >some and >any non-correlated subqueries, use the max function for <some and <any non-correlated subqueries, use semi-join for optimization of =any and =some subqueries, and only use the exists optimization for >some and >any correlated subqueries and <some and <any correlated subqueries.

This is the end of this article about the basic use of subqueries in MySQL. For more relevant MySQL subqueries, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!