Tutorial on column-subquery and row-subquery operations in MySQL,

Tutorial on column-subquery and row-subquery operations in MySQL,

MySQL column subquery and use of the in, ANY, SOME, and ALL operators
MySQL column subquery
A column subquery is a result set returned by a subquery that contains N rows and one column. This result is usually returned by a table field query.
An example of a column subquery is as follows:

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

Use the IN, ANY, SOME, and ALL operators IN the column subquery.

Because the result set returned by a column subquery is N rows and one column, you cannot directly use the ==<=<> operators that compare scalar results. The IN, ANY, SOME, and ALL operators can be used IN column subqueries:

• IN: same as IN (item 1, Item 2,…) within a specified item ,...).
• ANY: used in combination with comparison operators, indicating that if the value returned by the subquery is TRUE, TRUE is returned.
• SOME: alias of ANY, which is rarely used.
• ALL: used together with the comparison operator, indicating that if ALL values returned by the subquery are TRUE, TRUE is returned.

The original data table is as follows:

Table1:

s1210

Table2:

s251220

ANY operator
The ANY keyword must be followed by a comparison operator, indicating that it is TRUE when compared with ANY value returned by the subquery. TRUE is returned. An example of ANY is as follows:
SELECT s1 FROM table1 WHERE s1> ANY (SELECT s2 FROM table2)
The query result is as follows:

s110

In the subquery, all s2 column results (, 20) of table2 are returned, and then the s1 value in table1 is compared with it, if any value greater than s2 is TRUE, the query conditions are met.
IN is the alias of = ANY, which is the same, but not in is NOT <> ANY but <> SOME.
Special Cases
If table 2 is empty, the result after ANY is FALSE;
If the subquery returns results such as the (NULL, NULL, NULL) column as NULL, the result after ANY is UNKNOWN.
ALL Operator
The ALL keyword must be followed by a comparison operator, indicating that if it is TRUE compared with ALL values returned by the subquery, TRUE is returned. An example of ALL is as follows:
SELECT s1 FROM table1 WHERE s1> ALL (SELECT s2 FROM table2)
No results are returned for this query, because s1 does not have a value greater than all s2 values.
Of course, all values of s2 are returned in this example query. You can add any conditions in this subquery to limit the returned query results without returning all the results.
Not in is the alias of <> ALL.
Special Cases
If table 2 is empty, the result after ALL is TRUE;
If the subquery returns results such as (0, NULL, 1), although s1 is greater than the returned results, but there are empty rows, the result after ALL is UNKNOWN.
Note: For empty tables in table 2, the following statements return 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
Row-based subquery refers to a row of N columns returned by a subquery. The result of this subquery is usually the result set returned when a row of data in the table is queried.
An example of a row subquery is as follows:

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)

In this example, if column1 = 1 and column2 = 2, the query result is TRUE if the subquery returns data of a single row.
MySQL line Constructor
In the preceding example, the (1, 2) following the WHERE clause is called the ROW constructor. You can also write ROW (1, 2 ). The row constructor is usually used to compare with subqueries that can return two or more columns.
MySQL row subquery instance
The following are two original data tables used in the example:
Article table:

Blog table:

The SQL statement is as follows:

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

The query result is as follows:

In the subquery example of this row, compare the title, content, and uid fields of the article table with the Row Records returned by the subquery one by one. If they are equal, these equal records are listed (theoretically more than one record may exist ).

Articles you may be interested in:

• How to Use the FROM and EXISTS clauses in MySQL subqueries
• Tutorial on using subqueries and scalar queries in MySQL
• MySQL subquery and optimization learning tutorial
• Basic tutorial on table subqueries and associated subqueries in MySQL