Nested SELECT statement (subquery)

The nested SELECT statement is also called a subquery, for example, select name from BBC where region = (select region from BBC where name = 'Brazil ') the query result of one SELECT statement can be used as the input value of another statement. The preceding SQL statement is used to obtain all countries in the same region as 'Brazil '(Brazil. Subqueries can appear not only in the WHERE clause, but also in the from clause. They can be used as a temporary table or in the select list and returned as a field value. This section describes only subqueries in the where clause. When subqueries are used in the WHERE clause, an error that is easy to make in actual use is described here. Generally, as in the preceding example, nested statements are always compared with a value. Statement (select region from BBC where name = 'Brazil ') should return only one region, that is, 'americas '. however, if we insert another record with the Region Europe and country name Brazil in the table, what will happen? This will cause a statement running error. Because the syntax of this SQL statement is correct, the database engine starts to execute it, but an error occurs when it is executed to an external statement. The external statement is like select name from BBC where region = ('americas', 'Europe'). Of course, an error is returned. Is there a way to solve this problem, of course. Some SQL query conditions allow you to operate on the list values (that is, multiple values. For example, the "in" operator can test whether a value is in a list. The following statement can be executed securely without errors. No matter how many records in the table that contain cmdils, the following are referenced content: select name from BBC where region in (select region from BBC where name = 'Brazil ') let's take a look at some specific examples. 1. Given a population larger than Russia (Russia) select name from bbcwhere population> (select population from bbcwhere name = 'Russia ') 2. Give 'India ), all information about all countries in the region of 'ira' (Iran) is referenced as follows: Select * From bbcwhere region in (select region from bbcwhere name in ('India ', 'irance') 3. European countries whose GDP per capita exceeds 'United Kingdom '(UK. Select name from bbcwhere region = 'Europe' and GDP/population> (select GDP/population from bbcwhere name = 'United Kingdom ')