MySQL---where subquery, form subquery, exists subquery

1. What are sub-queries?

When a query is a condition of another query, it is called a subquery.

2. What are the benefits of sub-queries?

Subqueries can use a few simple commands to construct powerful composite commands.

Well, now let's learn the subquery together.

Sub-query of model 3.where

Give it a definition: the Where sub-query is the result of the inner query as the condition of the outer query.

Now, let's look at the most commented articles in each set of topic categories in the article table.

The given table is as follows:

CREATE TABLE article (
article_id Int (3),
Article_title varchar (50),
Article_content text,
article_comments Int (3),
articlecategory_id Int (3)
);
INSERT into article values (1, "fff1", "contteee", 55, 1);
INSERT into article values (2, "fff2", "conttffffffeee", 15,2);
INSERT into article values (3, "FFF3", "conttdgfdfdsfeee", 515, 1);
INSERT into article values (4, "Fff4", "Conttesdfsdfsee", 505, 1);
INSERT into article values (5, "Fff5", "Conttesdfsdfee", 545,2);
INSERT into article values (6, "Fff6", "Conttesdfsee", 575,2);
INSERT into article values (7, "Fff7", "Conttesdfsdee", 5, 1);
INSERT into article values (8, "Fff8", "Conttesdfsdfee", 77, 1);

Example: Select Article_id,article_title,article_content from article where article_comments in (select Max (article_comments) From article group by articlecategory_id);

4.from Sub-query

Definition: The From subquery is the result of a query (a table in memory) that is treated as a temporary table and then processed.

From subquery solves the problem above

such as: Select Tmp.article_id,tmp.article_content,article_comments from (SELECT * from article ORDER by articlecategory_id, Article_comments desc) as TMP GROUP by TMP.ARTICLECATEGORY_ID;

5.exists Sub-query

Definition: Exists sub-query is the outer layer of the table to cycle, and then internal query internal table. Similar to In (), but they are still different. The main point is to see the extent of the two table size difference.

If the subquery table is larger with exists (inner index), the Sub-query table is small with in (outer index);

The difference between efficiency is that the index used (where the variable is the index we use) is different, and we can use the index to improve the search speed for large tables.

MySQL---where subquery, form subquery, exists subquery