Basic tutorial on table subqueries and associated subqueries in MySQL,

Basic tutorial on table subqueries and associated subqueries in MySQL,

MySQL table subquery
Table subquery refers to a table with N rows and N columns returned by a subquery.
MySQL table 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) IN (SELECT title,content,uid FROM blog)

The query result is as follows:

The significance of this SQL statement is to find all the rows IN the blog table that contain the fields specified IN the article table (note = the comparison operator is replaced with IN). It is actually equivalent to the following condition statement:

SELECT * FROM article,blog WHERE (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)

In fact, the subsequent statements are optimized by MySQL and are more efficient. Alternatively, you can use the MySQL JOIN table connection. This example is used to describe the usage of table subqueries.

MySQL associated subquery
An associated subquery is a subquery that contains reference to a table. The table is also displayed in an external query. Generally speaking, the subquery references the data of the primary query.
To understand the associated subquery in an actual example:
Articles:

User table:

We need to find the data in the article table, but it requires that one or more fields in the article table have a logical relationship with the user table fields (in this example, the uid is equal ). The SQL statement is as follows:

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

The returned query result is as follows:

Compare this SQL statement with the following statement to see the difference between the associated subquery and the common subquery:

SELECT * FROM article WHERE uid IN(SELECT uid FROM user)

In this example, although the results returned after two SQL statements are the same, their implementation process is completely different. The latter (Common subquery) is actually executed as follows:

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

However, subquery statements cannot be executed separately in associated subqueries. The actual process is roughly as follows:

• First perform an external master query;
• Pass the value of the primary query to the subquery and execute it;
• The subquery then returns the query results to the primary query. The primary query completes the final query based on the returned results.

This execution process is similar to the EXISTS subquery. In fact, in some cases, MySQL overwrites the associated subquery to the exists subquery for execution.

MySQL associated subquery Efficiency
Obviously, in general, the efficiency of associated subqueries is relatively low. In fact, this example only demonstrates the principles and usage of associated subqueries. If possible, JOIN or other queries should be used instead of associated subqueries. In this example, the SQL statement replaced by INNER JOIN is:

SELECT article.* FROM article INNER JOIN user ON article.uid = user.uid

Note: This is just to demonstrate how to replace the associated subquery with inner join. It is not optimal for table name processing.

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
• Tutorial on column-subquery and row-subquery operations in MySQL
• MySQL subquery and optimization learning tutorial