MySQL Table Sub-query

MySQL Table Sub-query

A table subquery is a table data in which the result set returned by a subquery is n rows n columns.

MySQL Table Sub-query instance

Here are two original data tables for the example:

Article table:

Aid	title	content	UID
1	Article 1	Article 1 body content ...	1
2	Article 2	Article 2 body content ...	1
3	Article 3	Article 3 body content ...	2
4	Article 4	Article 4 body content ...	3

Blog table:

Bid	title	content	UID
1	Log 1	Log 1 body content ...	1
2	Article 2	Article 2 body content ...	1
3	Article 3	Article 3 body content ...	2

SQL is as follows:

SELECT * FROM article WHERE (title,content,uid) in (select Title,content,uid from blog)

The results of the query return are as follows:

Aid	title	content	UID
2	Article 2	Article 2 body content ...	1
3	Article 3	Article 3 body content ...	2

Compared to the previous row subquery example, the row subquery in the WHERE bid=2 condition limit is removed, the data returned is a table record (of course, if the qualifying record only one, and become a row subquery records, but we think this is a table subquery).

The meaning of this SQL is to find all the rows in the article table that also exist in the blog table (Note = The comparison operator is replaced by in), which is actually equivalent to the following conditional 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 for MySQL and more efficient, or can be implemented using a MySQL join table connection. This example is used only for the purposes of describing the use of table subqueries.

Derived data table sub-query

The table subquery applies more cases to the derived data table subquery, which is to execute the subquery first to generate the temporary table, and the outer main query to get the data in this temporary table. See MySQL from sub-query for details.

MySQL Table Sub-query