Basic how-to tutorials for using subqueries and scalar queries in Mysql _mysql

MySQL Sub-query
A subquery is an intermediate result of the query result of a SELECT statement, which is invoked by another SQL statement. MySQL supports all of the subquery formats and operations required by the SQL standard, and also extends several unique features.
The subquery has no fixed syntax, and the example of a subquery is as follows:

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

The corresponding two data tables are as follows:
Article Article table:

User table:

The query returns the results shown below:

In this example, the UID of all Status=1 is queried first through the subquery, and the actual query is similar to the following:

SELECT * from article WHERE uid (1,2)

MySQL Standard Quantum Query
a scalar quantum query is one in which a subquery returns a single value, such as a number or a string, and is the simplest form of return in a subquery.
An example of a standard quantum query is as follows:

SELECT * FROM article WHERE uid = (select uid to user where Status=1 order by uid DESC LIMIT 1)

In this example, a subquery statement:

SELECT uid from user WHERE status = 1 ORDER by uid DESC LIMIT 1

Returns a single number (such as 2), and the actual query statement is:

SELECT * FROM article WHERE uid = 2

Using Subqueries for comparisons
You can use the = > < >= <= <> These operators to compare the scalar results of subqueries, usually where the subquery is located on the right side of the comparison:

SELECT * from t1 WHERE column1 = (select MAX (column2) from T2)

Tips
For comparisons that take one of these operators, the subquery must return a scalar. The only exception is = can be used concurrently with row subqueries.
Subqueries and table joins
In many cases, the effect of a subquery is similar to a join table connection, but in some special cases, a subquery must not be connected with a table, such as:

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)

This example is to find out all the article records that the user has published 2 articles. The corresponding two data tables are as follows:
Article Article table:

User table:

The query returns the results shown below: