Resolution: inline, left outer, right outer, full join, cross join

Connections include: internal connection, external connection, and cross connection.
1. Internal Connection-the most common
Definition:Only the rows in the two tables that meet the connection conditions are combined as the result set.
In the internal join, only matching rows in two tables can appear in the result set.
Keywords:INNER JOIN
Format:SELECT column table FROM table name 1 [INNER] JOIN table name 2 ON or WHERE condition expression
Note:
(1) The column names in the column names can be from the following two tables. However, if the two tables have the same column names, the source should be indicated before the column names. Format: Table name. Column name
(2) If the names of the two connected tables are too long, you can create an alias for them. Format: Table name AS Alias
(3) INNER is the default mode and can be omitted.
Eg:
Select *
From t_institution I
Inner join t_teller t
On I. inst_no = t. inst_no
Where I. inst_no = "5801"
Inner can be omitted.
Equivalent to the early connection syntax
Select *
From t_institution I, t_teller t
Where I. inst_no = t. inst_no
& I. inst_no = "5801"

Ii. External Connection
1. Left (outer) join
Definition: Based on the internal join, it also contains all data rows that do not meet the conditions in the left table, and fill in NULL in the right table column.
Keyword: LEFT JOIN
Eg:
Select *
From t_institution I
Left outer join t_teller t
On I. inst_no = t. inst_no
Outer can be omitted.
Note:
When a condition is added to an internal join query, whether it is added to the join clause or the where clause, the effect is the same, but the external join condition is different. When conditions are added to the join clause, SQL Server and Informix return all rows of the Outer join table, and then return the rows of the second table using the specified conditions. If conditions are placed in the where clause, SQL Server first performs the join operation, and then uses the where clause to filter connected rows. The following two Queries show the impact of conditional placement on execution results:
Condition in join clause
Select *
From t_institution I
Left outer join t_teller t
On I. inst_no = t. inst_no
And I. inst_no = "5801"

The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david
5802 Yuexiu District
5803 Baiyun District

Condition in where clause
Select *
From t_institution I
Left outer join t_teller t
On I. inst_no = t. inst_no
Where I. inst_no = "5801"

The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david

2. Right (outer) Connection
Definition:On the basis of the internal join, it also contains all the data rows that do not meet the conditions in the right table, and fill in NULL in the left table column.
Keywords:RIGHT JOIN
3. Full connection
Definition:On the basis of the internal join, it also contains all the data rows that do not meet the conditions in the two tables, and fill in NULL in the left table, and the right table column.
Keywords:FULL JOIN

Iii. Cross join
Definition:Combine all rows in two tables. The number of joined rows is the product of the two tables. (Cartesian Product)
Keywords:CROSS JOIN
Format:FROM table name 1 cross join table name 2

4. Self-connection
Self-join means that the same table is connected to itself. This type of unary join is usually used to extract data from the self-inverse (also known as recursive) relationship. For example, the relationship between employees and bosses in the HR database.
The following example shows the information of the institution and its parent institution in the organization table.
Select s. inst_no superior_inst, s. inst_name sup_inst_name, I. inst_no, I. inst_name
From t_institution I
Join t_institution s
On I. superior_inst = s. inst_no

The result is:
Superior_inst sup_inst_name inst_no inst_name
800 Tianhe District, Guangzhou 5801
800 Yuexiu District, Guangzhou 5802
800 Guangzhou 5803 Baiyun District