Analysis: inlining left outreach right outreach full connection cross connection

  • 2020-05-27 07:20:27
  • OfStack

The connection is divided into: internal connection, external connection and cross connection
1. Internal connections -- most commonly used
Definition: only the rows in two tables that satisfy join conditions are combined as a result set.
In an inner join, only rows that match in both tables can appear in the result set
Key words: INNER JOIN
Format: SELECT column table FROM table name 1 [INNER] JOIN table name 2 ON or WHERE conditional expression
Description:
(1) the column name in the column table may come from the following two tables, but if there is a same column name in the two tables, the source shall be indicated before the column name, and the format shall be: table name. Column name
(2) if the names of two joined tables are too long, you can give them an alias. The format is: table name AS alias
(3) INNER is the default mode, which 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 an earlier join syntax
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"

2. The outer join
1. Left (outside) connection
Definition: include all unqualified data rows in the left table, based on the inner join, and fill in NULL in the right table column
Keywords: 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 inner join query, whether it is added to the join clause or to the where clause, the effect is exactly the same, but it is different for the outer join. When a condition is added to the join clause, SQL Server, Informix returns all the rows of the outer join table, and then returns the rows of the second table using the specified condition. If you put the condition in the where clause, SQL Server will first join and then filter the rows after joining using the where clause. The following two queries show the effect of conditional placement on execution results:
The condition is 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 is:
inst_no inst_name inst_no teller_no teller_name
5801 tianhe district 5801 0001 tom
5801 tianhe district 5801 0002 david
Yuexiu district 5802
5803 baiyun district

The condition is 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 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 (outside) connection
Definition: include all unqualified data rows in the right table, based on the inner join, and fill in NULL in the left table column
Keywords: RIGHT JOIN
3. Fully connected
Definition: include all unqualified data rows in the two tables on the basis of the inner join, and fill in NULL in the left and right table columns
Keywords: FULL JOIN

3. Cross connection
Definition: combine all the rows of two tables, and the number of joined rows is the product of the two tables. (cartesian product)
Key words: CROSS JOIN
Format: FROM table name 1 CROSS JOIN table name 2

4. Self connection
Self join refers to the self join with a table. This meta-join is often used to extract data from reflexive relationships (also known as recursive relationships). For example, the employee-boss relationship in a human resources database.
The following example is looking up information about your organization and your parent organization 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
Tianhe district, guangzhou 5801, China
Yuexiu district, guangzhou 5802, China
Baiyun district, guangzhou 5803, China


Related articles: