Section 5 nested SELECT statements

Section 5 nested select statements

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 '(Brazil.

Subqueries can appear in both the WHERE clause and the from clause as a temporary table,
It can also appear in the select list and be 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 the statement,
However, an error occurs when an external statement is executed.
Because the external statement is like select 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 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 contain the cmdils

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

OK,

Let's take a look at some specific instances,

1. Name of a country with more population than Russia (Russia)
SELECT name FROM bbc
WHERE population>
(SELECT population FROM bbc
WHERE name = 'Russia ')

2. provide all information about all countries in the region where 'India "and 'ira' (Iran) are located.
SELECT * FROM bbc
WHERE region IN
(SELECT region FROM bbc
WHERE name IN ('India ', 'irance '))

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

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

More Online exercises: http://www.sqlzoo.cn/1a.htm