Parsing: inline, left outer, right outer, full connection, cross connection difference _mysql

The connection is divided into: internal connection, outer connection, cross connection
One, inner connection--most commonly used
definition: only the rows in two tables that satisfy the join condition are grouped as result sets.
In an inner join, only rows that match in two tables can appear in the result set
Key Words: INNER JOIN
format: SELECT Column Name table from table name 1 [INNER] JOIN table name 2 on or where condition expression
Description
(1) The column names in the column name table can be from the following two tables, but if the two tables have the same name, the list should be marked with the source in the form: Table name. Column Name
(2) If the two table names of the connections are too long, they can be individually named. Format is: Table name as Alias
(3) Inner is the default way, you can omit
eg
SELECT *
From T_institution I
INNER JOIN T_teller t
On i.inst_no = T.inst_no
where i.inst_no = "5801"
Where inner can be omitted.
Equivalent to the earlier connection syntax
SELECT *
From t_institution I, T_teller t
where i.inst_no = T.inst_no
and i.inst_no = "5801"

Second, outer connection
1, left (outside) connection
Definition: On the basis of inner joins, also contains all data rows in the left table that do not meet the criteria, and fill in the right table column with NULL
Key word: Left JOIN
eg:
SELECT *
From T_institution I
Left OUTER join T_teller T
On i.inst_no = T.inst_no
where outer can be omitted.
Note:
When you add a condition in the join query, whether you add it to the join clause or add it to the WHERE clause, the effect is exactly the same, but it is different for the outer join. When a condition is added to a join clause, SQL Server, Informix returns all rows of the outer join table, and then returns the row of the second table with the specified criteria. If you place a condition in the WHERE clause, SQL Server will first connect and then use the WHERE clause to filter the rows after the connection. The following two queries show the effect of conditional placement on the execution result:
Condition in the 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:
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 the 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:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 Tom
5801 Tianhe District 5801 0002 David

2, right (outside) connection
Definition:On the basis of the inner join, also contains all the data rows in the right table that do not meet the criteria, and fill in the left table column with null
Key Words:Right JOIN
3. Full connection
Definition:On the basis of an inner join, you also include all data rows in two tables that do not meet the criteria, and fill in the Left and right table columns with NULL
Key Words:Full JOIN

Iii. Cross-linking
definition: All rows of two tables are grouped, and the number of rows after the connection is the number of two tables. (Cartesian product)
Key Words: CROSS JOIN
format: From table name 1 CROSS JOIN table Name 2

four, self connection
Self-connection means that the same table connects itself to itself. This unary connection is typically used to extract data from a reflexive relationship (also known as a recursive relationship). For example, the relationship between employees and bosses in the human resources database.
The following example is the information that is found in the organization table for the organization and the parent body.
Select S.inst_no superior_inst, S.inst_name sup_inst_name, I.inst_no
From T_institution I
Join T_institution S
On i.superior_inst = S.inst_no

The result:
Superior_inst sup_inst_name inst_no Inst_name
800 Guangzhou city 5801 Tianhe District
800 Guangzhou City 5802 Yuexiu District
800 Guangzhou City 5803 Baiyun District