A concise tutorial of SQL statements for Linux---subquery

We can put another SQL statement in one SQL statement. When we insert another SQL statement in the where clause or HAVING clause, we have a subquery schema. What is the role of subquery ? First, it can be used to connect tables. In addition, sometimes subquery is the only way to connect two tables.

The syntax for subquery is as follows:

Select "Field 1"
From "Table"
WHERE "Field 2" [comparison operator]
(select "Field 1"
From "Table"
WHERE "condition");

[comparison operator] can be the equivalent of an operator, for example =, A;, <, >=, <=. This can also be an operand to a literal, such as "like".

Let's use the example we just used to illustrate the SQL connection:

store_information Form

Store_name	Sales	Txn_date
Los Angeles	1500	05-jan-1999
San Diego	250	07-jan-1999
Los Angeles	300	08-jan-1999
Boston	700	08-jan-1999

Geography Form

Region_name	Store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

We need to use subquery to find out the turnover of all stores in the west. We can use the following SQL to achieve our goal:

SELECT SUM (Sales) from store_information
WHERE Store_name in
(SELECT store_name from Geography
WHERE region_name = ' West ');

Results:

SUM (Sales)

2050

In this example, we did not directly connect the two tables and then calculate the turnover of each West Side store directly. What we do is to find out which stores are in the west side, and then figure out how many of these stores are in total turnover.

Linux testing:

Reprint please specify: Xiao Liu

A concise tutorial of SQL statements for Linux---subquery