A concise tutorial of SQL statements for Linux---table connection

Now let's introduce the concept of connection (join). To understand the connection, we need to use many of the instructions we have described earlier. Let's just assume we have the following two tables,

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

And we need to know the turnover (sales) of each district (Region_name). Geography This form tells us which stores are in each district, and Store_information tells us the turnover of each store. If we want to know the turnover of each district, we need to concatenate the data from these two different tables. When we have a closer look at these two tables, we will find that they can be connected by an identical field, Store_name. We will first list the SQL sentences and then discuss the meaning of each sentence:

SELECT A1. Region_name region, SUM (A2. Sales) Sales
From Geography A1, Store_information A2
WHERE A1. Store_name = A2. Store_name
GROUP by A1. Region_name;

Results:

Region	SALES
Eas	700
West	2050

In the first line, we tell SQL to select two fields: The first field is the Region_name field in the Geography table (we take an alias called region), and the second field is store_information table (the alias is sales). Notice here that we have a table alias: the alias of theGeography table is A1, and the alias of thestore_information table is A2. If we don't use a table alias, the first line will become

SELECT Geography.region_name Region, SUM (store_information.sales) Sales

Obviously, it's a lot more complicated. Here we can see the function of the table alias: it makes the SQL sentence easy to understand, especially when the SQL sentence contains several different tables.

Next we look at the third line, which is the where clause. This is where we describe the conditions of the connection. Here, we want to confirm that the value of the Store_name field in the Geography table is equal to the value of the Store_name field in the store_information table. This WHERE clause is a connected soul character because its role is to determine the connection between the two tables is correct. If the WHERE clause is wrong, we are most likely to get a Cartesian connection (Cartesian join). The Cartesian connection causes us to get all the possible combinations of all two tables between each of the two rows. In this example, the Cartesian connection gives us a result of 4 x 4 = 16 rows.

Linux measured results:

1. Create a Geography table first

2. Inserting the response data in the phase table

3. Connection Query

A concise tutorial of SQL statements for Linux---table connection