SQL nested SELECT statement

Nested SELECT statements are also called subqueries, 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.

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 ')Only one region, that is, 'americas', should be returned '. 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 the statement, but an error occurs when it is executed to an external statement.

Because the external statement is likeSELECT name FROM bbc WHERE region = ('americas', 'Europe ')Of course, this statement reports an error.

Is there a way to solve this problem, of course. Some SQL query conditions allow you to operate on the List Value (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 contain the cmdils

Reference content is as follows:

SELECT name FROM bbc WHERE region IN (SELECT region FROM bbc WHERE name = 'Brazil')

Let's take a look at some specific instances.

1. Name of a country with a population greater than Russia

Reference content is as follows:

SELECT name FROM bbcWHERE population>(SELECT population FROM bbcWHERE name='Russia')

2. All information about all countries in the region where 'India ' are provided.

Reference content is as follows:

SELECT * FROM bbcWHERE region IN(SELECT region FROM bbcWHERE name IN ('India',''))

Iii. European countries with GDP per capita exceeding 'United Kingdom.

Reference content is as follows:

SELECT name FROM bbcWHERE region='Europe' AND gdp/population >(SELECT gdp/population FROM bbcWHERE name='United Kingdom')

1. Distributed DBA: SQL stored procedure knowledge Summary
2. Usage of SQL statements in Oracle
3. Popularity of Sybase SQL Server logs