Nested SELECT statement for SQL tutorial (from network)

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 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 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" (India) and "Iran" (Iran) are located.

Select * from BBC

Where region in

(Select region from BBC

Where name in ("India", "Iran "))

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

Select name from BBC

Where region = "Europe" and GDP/population>

(select GDP/population from BBC
where name = "United Kingdom")