A Learning guide for column subqueries and ROW subquery operations in Mysql _mysql

MySQL Query and use of the in, any, SOME, and all operators
MySQL Query
An in-line query is the result set returned by a subquery that is a column of N rows, usually from a field query returned to a table.
An example of a column subquery is as follows:

SELECT * from article where UID (select uid from user where Status=1)

Use the in, any, SOME, and all operators in the subquery query

Because the result set returned by the subquery query is N rows, it is not possible to use the = > < >= <= <> operators of these comparison scalar results directly. You can use the in, any, SOME, and all operators in the subquery query:

• In: Within the specified item, in (item 1, item 2,...).
• Any: is used in conjunction with the comparison operator to indicate that any value returned by the subquery is true, and returns True.
• Some:any aliases, less used.
• All: Use with comparison operators to return TRUE if all values returned by the subquery are true.

The following are the original data tables:

Table1:

S1
2
10

Table2:

S2
5
20

Any operator
The Any keyword must be followed by a comparison operator to indicate that any value returned by the subquery is true, and returns True. An any example is as follows:
Select S1 from table1 WHERE S1 > No (select s2 from table2)
The query returns the results shown below:

S1
10

In a subquery, all S2 column results (5,12,20) for table2 are returned, and then the S1 in Table1 is compared to the value, as long as any value greater than S2 is represented as TRUE and the query condition is met.
In is the alias of = Any, both of which are the same, but the alias of not in is not the <> any but <> SOME.
Special cases
If Table2 is an empty table, the result of any is FALSE;
If the subquery returns the result of a null (null,null,null) column, the result of any is UNKNOWN.
All operator
The all keyword must be followed by a comparison operator to indicate that all values returned by the subquery are true, and return true. An all example is as follows:
Select S1 from table1 WHERE S1 > All (select S2 from table2)
The query does not return any results because there is no value in S1 that is larger than S2 all values.
In this example query, of course, all the values of S2 are returned, and you can add any criteria in the subquery to limit the results of the query returned without having to return all of them.
Not in is an alias for <> all, both of which are identical.
Special cases
If Table2 is an empty table, the result of all is TRUE;
If the subquery returns the result of a blank row, although the S1 is larger than the return result, such as (0,null,1), the result of all is UNKNOWN.
Note: For table2 empty tables, the following statements return NULL:

Select S1 from table1 where s1 > (select s2 from table2)
select S1 to table1 where S1 > All (select MAX (S1) FR OM table2)

MySQL Row subquery
a row subquery is the result set returned by a subquery that is a row N column, and the result of the subquery is usually the result set returned by querying a row of data in the table.
An example of a row subquery is as follows:

SELECT * FROM table1 WHERE (1,2) = (select Column1, column2 from Table2)

In this example, if the subquery returns a single row of data, the query evaluates to TRUE if Column1=1 and column2=2.
MySQL Row Constructor
In the example above, where the 1,2 is called a row constructor, you can also write row (1,2). Row constructors are typically used to compare to subqueries that can return two or more columns.
MySQL Row Sub Query instance
Here are two raw data tables for the example:
Article table:

Blog table:

SQL is as follows:

SELECT * from article where (title,content,uid) = (select Title,content,uid from blog where bid=2)

The query returns the results shown below:

In the row subquery example, the article table Title,content,uid fields are compared one by one with the row records returned by the subquery, and if they are equal, they are listed (in theory, there may be more).