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

Let's talk about the MySQL subquery Subquery. The secret of the text is that it is close to the topic. So, no gossip, let's just read the following, I believe you will benefit from reading this article of MySQL sub-query Subquery.

Subquery means that there is a SELECT query embedded in the SQL statement, the SELECT is called the inner query, and the SQL containing SELECT is called the outer query. According to whether the inner query depends on the outer query, it can be divided into independent subquery and related subquery.

For the convenience of demonstration, there are test tables tb1 and tb2. The data are as follows:

Mysql > SELECT * FROM tb1

+-+

| | col1 |

+-+

| | 3 |

| | 9 |

+-+

2 rows in set (0.00 sec)

Mysql > SELECT * FROM tb2

+-+

| | col1 |

+-+

| | 2 |

| | 3 |

| | 8 |

+-+

3 rows in set (0.00 sec)

Independent subquery means that there is no association between inner and outer queries and there is no need for federated queries. There are three sets of keywords related to it: ANY/ IN/ SOME, ALL and EXISTS.

The syntax of ANY/ IN/ SOME is:

Operand comparison_operator ANY (subquery)

Operand IN (subquery)

Operand comparison_operator SOME (subquery)

A. the ANY keyword means that for any value in the column returned by the subquery, TRUE. TRUE is returned if the result of the expression is TRUE.

For the following SQL, the table tb1 contains (3,9), the tb2 contains (2,3,8), and the result of the expression is TRUE.

Mysql > SELECT col1 FROM tb1 WHERE col1 > ANY (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 3 |

| | 9 |

+-+

2 rows in set (0.00 sec)

b. In the subquery, the alias of = ANY is IN, and the following two SQL returns are the same.

Mysql > SELECT col1 FROM tb1WHERE col1 = ANY (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT col1 FROM tb1WHERE col1 IN (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

C. the alias of ANY is SOME, and the following two SQL returns are the same.

Mysql > SELECT col1 FROM tb1WHERE col1 ANY (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 3 |

| | 9 |

+-+

2 rows in set (0.00 sec)

Mysql > SELECT col1 FROM tb1WHERE col1 SOME (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 3 |

| | 9 |

+-+

2 rows in set (0.00 sec)

The syntax of ALL is:

Operand comparison_operator ALL (subquery)

A. the ALL keyword means that for all values in the column returned by the subquery, TRUE. TRUE is returned if the result of the expression is TRUE.

Mysql > SELECT col1 FROM tb1WHERE col1 > ALL (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

B. the alias of ALL is NOT IN, and the following two SQL returns are the same.

Mysql > SELECT col1 FROM tb1 WHERE col1 ALL (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT col1 FROM tb1WHERE col1 NOT IN (SELECT col1 FROM tb2)

+-+

| | col1 |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

The last set of keywords is EXISTS.

A. EXISTS means that if the subquery returns a non-empty set, EXISTS is TRUE and NOT EXISTS is FALSE. The following SQL is only for ease of understanding of EXISTS and is generally not used in this way.

Mysql > SELECT col1 FROM tb1 WHERE EXISTS (SELECT * FROMtb2)

+-+

| | col1 |

+-+

| | 3 |

| | 9 |

+-+

Rows in set (0.00 sec)

b. The following exists subquery is close to the actual situation and gets the same records in tables tb1 and tb2. You can see that the inner layer is associated with the outer table, which is the related subquery described below.

Mysql > SELECTcol1 FROM tb1 WHERE EXISTS (SELECT * FROM tb2 WHERE tb2.col1 = tb1.col1)

+-+

| | col1 |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

Related subquery means that the inner query needs to be associated with the table of the outer query. We have seen examples of related subqueries above and carefully understand the syntax differences between them and independent subqueries.

In addition, if the above SELECT col1 FROM tb1 WHERE EXISTS (SELECT * FROM tb2WHERE tb2.col1 = tb1.col1) is equivalent to the above SELECT col1 FROM tb1 WHERE col1 IN (SELECT col1 FROM tb2), then the independent subquery of IN and the related subquery of EXISTS can be rewritten each other, so what's the difference between them?

Add a record (NULL) to the table tb2. The data is as follows, but the tb1 remains unchanged:

Mysql > SELECT * FROM tb2

+-+

| | col1 |

+-+

| | 2 |

| | 3 |

| | 8 |

| | NULL |

+-+

4 rows in set (0.00 sec)

Find out the records in table tb1, not in tb2. The visual result should be 9, but the return is empty.

Mysql > SELECT * FROM tb1 WHERE col1 NOTIN (SELECT col1 FROM tb2)

Empty set (0.00 sec)

Why, the test is as follows: in the case of null, NOT IN only returns NOT TRUE and NULL, that is, FALSE. It is in the case of NULL that NOT IN never returns a result.

Mysql > SELECT'a 'NOT IN (' axiom, 'breadth, null)

+-+

| |'a 'NOT IN (' averse, 'baked, NULL) | |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT 'c'NOT IN ('axioms,' baubles, null)

+-+

| |'c 'NOT IN (' averse, 'baked, NULL) | |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

If you want NOT IN to return results, you need to filter out the NULL value, which explains why it is not recommended to save data as NULL.

Mysql > SELECT * FROM tb1 WHERE col1 NOTIN (SELECT col1 FROM tb2 WHERE col1 IS NOT NULL)

+-+

| | col1 |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

If you use EXISTS to write, because it is a related subquery, there is no special consideration for NULL.

Mysql > SELECT * FROM tb1 WHERE NOTEXISTS (SELECT col1 FROM tb2 WHERE tb2.col1 = tb1.col1)

+-+

| | col1 |

+-+

| | 9 |

+-+

1 row in set (0.00 sec)

The performance optimization of subqueries and rewriting to JOIN will be sorted out later. If you are interested, please follow Subscription account's "Database Best practices" (DBBestPractice).

For the above MySQL subquery Subquery related 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.