How to Use the FROM and EXISTS clauses in MySQL subqueries,

How to Use the FROM and EXISTS clauses in MySQL subqueries,

FROM subquery
Subquery in the from clause
MySQL FROM subquery refers to the FROM clause as a subquery statement. The primary query then obtains the required data FROM the subquery results. The FROM subquery syntax is as follows:

SELECT ... FROM (subquery) AS name ...

The subquery generates a temporary table. Because each table in the FROM clause must have a name, the AS name is required. A FROM subquery is also called a subquery of a derived data table.
FROM subquery instance
Table1:

s1 s21  52  123  20

The FROM subquery SQL statement is as follows:
SELECT s1, s2 FROM (SELECT s1, s2 * 2 AS s2 FROM table1) AS temp WHERE s1> 1
The query result is as follows:

s1 s22 243 40

Prompt
Subqueries In the MySQL FROM clause can return scalar, column, row, or table, but cannot be associated subqueries.

MySQL subquery EXISTS and NOT EXISTS
MySQL EXISTS and not exists subqueries
The subquery Syntax of MySQL EXISTS and not exists is as follows:

SELECT ... FROM table WHERE EXISTS (subquery)

This syntax can be understood as: Put the data of the primary query into the subquery for conditional verification, and determine whether the data results of the primary query are retained based on the verification result (TRUE or FALSE.
MySQL EXISTS subquery instance
The following is an example of an EXISTS subquery. The original data table is as follows:
Articles:

User table:

We need to find the data in the article table, but the uid must exist in the user table. The SQL statement is as follows:

SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)

The returned query result is as follows:

The statement execution result shows that the first record in the article table is not retained because the data returned by this record in the subquery is FALSE.
When the preceding SQL statement uses NOT EXISTS, the query result is that the uid in the article table does NOT exist in the user table.
Prompt
EXISTS (subquery) Only returns TRUE or FALSE. Therefore, SELECT * in the subquery can be SELECT 1 or another. The official statement is that the SELECT list is ignored during actual execution, so there is no difference.
The actual execution process of the EXISTS subquery may have been optimized rather than compared one by one on our understanding. If you are concerned about efficiency, you can perform a practical test to determine whether the problem is efficient.
EXISTS subqueries can also be replaced by conditional expressions, other subqueries, or JOIN queries. What kind of optimization needs to be analyzed in detail.

Articles you may be interested in:

• Tutorial on using subqueries and scalar queries in MySQL
• 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