How to perform subquery in MySQL database

Subquery is a query statement nested in another query statement. The query results of the inner query statement can be used as the outer query statement to provide query conditions. The subquery may include keywords such as IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS, as well as comparison operators such as "=", "!=", etc. How to perform a subquery? The specific operations are as follows:

#1. First, the query results in a query are used as the conditions of the outer query. You can use the IN keyword. The code is as follows:

SELECT * FROM city WHERE CountryCode IN (SELECT Code FROM country);

As shown below:

2. Secondly, the conditions of the outer query are not the results of the inner query, you can use the NOT IN key word, the code is as follows:

SELECT * FROM city WHERE CountryCode NOT IN (SELECT Code FROM country);

as shown in the figure below:

3. When using the EXISTS keyword query, the inner query The statement does not return the query record, but returns a Boolean value; when the value returned by the inner query is true, the outer query statement will query. If false is returned, the query will not be performed or the query result will be empty. The code is as follows :

SELECT * FROM city WHERE EXISTS (SELECT Name FROM country);

As shown in the figure below:

4. From the third step, it can be seen that the opposite of EXISTS is NOT EXISTS. When the value returned by the inner query is false, the outer query statement will query. If true is returned, the query will not be performed or the query result will be empty. The code is as follows:

SELECT * FROM city WHERE NOT EXISTS (SELECT Name FROM country);

As shown in the figure below Display:

5. If any of the conditions is met, the outer query statement can be executed through the condition, using the keyword ANY, the code is as follows:

SELECT * FROM city WHERE Population >= ANY (SELECT Population FROM country);

As shown below:

6. The subquery also contains comparison operators, including ">= ", "<=", "!=" and other keywords, the code is as follows:

SELECT * FROM city WHERE Population >=  (SELECT Population FROM country WHERE Name = 'Afghanistan');
SELECT * FROM city WHERE Population <=  (SELECT Population FROM country WHERE Name = &#39;Afghanistan&#39;);
SELECT * FROM city WHERE Population !=  (SELECT Population FROM country WHERE Name = &#39;Afghanistan&#39;);

As shown below:

##7. If all conditions are met, all results will be returned only if the inner query statement is met. The code is as follows:

SELECT * FROM city WHERE Population >= ALL (SELECT Population FROM country WHERE Name = 'Afghanistan');

is as follows As shown in the figure:

Notes

Pay attention to the usage of subquery

Pay attention to the difference between each keyword in the subquery

The above is the detailed content of How to perform subquery in MySQL database. For more information, please follow other related articles on the PHP Chinese website!