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