Tutorial on using subqueries and scalar queries in MySQL,

Tutorial on using subqueries and scalar queries in MySQL,

MySQL subquery
A subquery uses the query result of a SELECT statement as the intermediate result for calling by another SQL statement. MySQL supports all the subquery formats and operations required by the SQL standard, and also extends the unique features.
There is no fixed syntax for subqueries. An example of a subquery is as follows:

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

The two data tables are as follows:
Articles:

User table:

The query result is as follows:

In this example, the sub-query statement is used to query all UIDs whose status is 1. The actual query is similar:

SELECT * FROM article WHERE uid IN(1,2)

MySQL scalar Query
Scalar query refers to a scalar returned by a subquery as a single value, such as a number or a string. It is also the simplest return form in subquery.
An example of a scalar query is as follows:

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)

In this example, the subquery statement is as follows:

SELECT uid FROM user WHERE status = 1 ORDER BY uid DESC LIMIT 1

Returns a single number (for example, 2). The actual query statement is:

SELECT * FROM article WHERE uid = 2

Use subqueries for comparison
You can use the following operators to compare the scalar results of subqueries:

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)

Prompt
For comparisons using one of these operators, the subquery must return a scalar. The only exception is that = can be used together with row subqueries.
Subquery and table connection
In many cases, the effect of subqueries is similar to that of JOIN tables. However, in some special cases, subqueries must be used instead of tables. For example:

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)

And the following example:

SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

In this example, we need to find all the document records that the user has published two articles. The two data tables are as follows:
Articles:

User table:

The query result is as follows:

Articles you may be interested in:

• How to Use the FROM and EXISTS clauses in MySQL subqueries
• Tutorial on column-subquery and row-subquery operations in MySQL
• MySQL subquery and optimization learning tutorial
• Basic tutorial on table subqueries and associated subqueries in MySQL