A concise tutorial of SQL statements for Linux---external connections

The LEFT join we saw earlier, also known as an internal join (inner join). In this case, the same value should be found in all two tables, and the information will be selected. What if we want to list each of the data in a table, regardless of whether its value appears in another table? At this point, we need to use the SQL OUTER join(external connection) instructions.

The syntax for an external connection differs depending on the database. For example, on Oracle, we would add a "(+)" to the table in the where clause to select all the data, and we would say all the information in the table.

Let's say 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

We need to know the turnover of each store. If we use a normal connection, we will miss the ' New York ' store because it does not exist in the store_information form. So, in this case, we need to concatenate these two tables with an external connection:

SELECT A1. Store_name, SUM (A2. Sales) Sales
From Georgraphy A1, Store_information A2
WHERE A1. Store_name = A2. Store_name (+)
GROUP by A1. Store_name;

We are here to use the external connection syntax of Oracle.

Results:

Store_name	SALES
Boston	700
New York
Los Angeles	1800
San Diego	250

Note: SQL returns a NULL value when the second table has no relative data. In this example, ' New York ' does not exist in the store_information table, so its "SALES" field is NULL.

Linux measured results:

Reprint please specify: Xiao Liu

A concise tutorial of SQL statements for Linux---external connections