what is mysql subquery

MySql's subquery is an important part of multi-table query. It is often used together with join query and is the basis of multi-table query. The following article will take you through subquery, I hope it will be helpful to you.

What is a subquery?

Subquery, also called internal query. When a query is a condition of another query, it is called a subquery. Subqueries can use several simple commands to construct powerful compound commands. Subqueries are most commonly used in the WHERE clause of a SELECT-SQL command.

A subquery is a SELECT statement that is nested within a SELECT, SELECT…INTO statement, INSERT…INTO statement, DELETE statement, or UPDATE statement or within another subquery.

2. Subquery classification

Subqueries are divided into the following categories:

1. Scalar subquery: return A single-valued scalar, in its simplest form.

2. Column subquery: The returned result set is N rows and one column.

3. Row subquery: The returned result set is a row with N columns.

4. Table subquery: The returned result set is N rows and N columns.

Operators that can be used: = > < >= <= <> ANY IN SOME ALL EXISTS

A subquery will return a 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 a value), then the outer query can use: =, >, <, >=, <= and <> symbols for comparison Judgment; if the subquery returns not 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:

means that the subquery returns a scalar with a single value, such as a number or a string. It is also the simplest subquery. return form. You can use = > < >= <= <> These operators can be used to compare the scalar results of subqueries. Usually the position of the subquery is on the right side of the comparison expression

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 column subquery:

means that the result set returned by the subquery is N rows and one column. The results usually come from the table A certain field query returns.

You can use = > < >= <= <> These operators compare the scalar results of the subquery. Usually the position of the subquery is in the comparison expression. On the right side

you can use IN, ANY, SOME and ALL operators, but cannot be used directly = > < >= <= <> These operators 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 of <> ALL, they are the same.

Special case:

If table2 is an empty table, the result after ALL is TRUE;

If the subquery returns such as (0, NULL, 1), although If s1 is larger than the returned results, but there are blank rows, the result after ALL is UNKNOWN.

Note: For the case of table2 empty table, 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:

refers to the subquery The result set returned by the query is a row with N columns. The result of the subquery is usually the result set returned by querying a certain row of data in the table.

Example:

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
//注：(1,2) 等同于 row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4. MySQL table subquery:

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

Example:

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

The above is the detailed content of what is mysql subquery. For more information, please follow other related articles on the PHP Chinese website!