Types of table joins:
1. INNER JOIN (inner join): is the most commonly used connection method, only returns the rows of matching relationships between two data sets, and joins the data rows within the overlapping portions of the two intersecting data sets.
Example: Use internal links in tables A and B to query student names, schools and occupations.
Implementation method One:
Select a. Name, A. School, b. Occupation from a, a where A. Name =b. Name
Meaning: The result shows the name of table A and the occupation of the school, table B, from the list of a, a, query condition is that table A's name equals table B's name.
Implementation method Two:
Select a. Name, A. School, b. Occupation from a inner join B on a. Name =b. Name
Meaning: Table A joins table B, the inner join condition is that the name of table A equals the name of Table B.
Note: The results of the inner join show only the rows of names that are co-owned by Table A and table B, with different names being discarded.
2. Outer connection: An extension of the internal connection, showing unmatched data.
1). Left outer join (leave join or ieft outer join): The result preserves all rows of the left table, and if a row in the left table does not have a matching row in the right table, the null value is returned for the right table, otherwise the corresponding value is returned.
Example: Use left outer joins in table A and table B to query student names, schools and occupations.
Select a. Name, A. School, B. Name, B. Occupation from a left join B on a. Name =b. Name
Meaning: The result shows the number of all the names in table A on the left, there are no matching names in table B, and the names and occupations are displayed as null values (NULL).
2). Right outer join (true join or outer join): Instead of the left outer join, the result returns all rows from the right table, and if a row in the right table does not have a matching row in the left table, it will return the null value for the left table, otherwise the corresponding value is returned.
Example: Use right outer joins in table A and table B to query student names, schools and occupations.
Select a. Name, A. School, B. Name, B. Occupation from a right join B on a. Name =b. Name
Meaning: The result shows all the name rows of table B on the right, there are no matching names in table A, names and schools are displayed as null values (NULL).
3). Fully connected/full outer join (full join or fully outer join): The result returns all rows of the left and right tables, and returns the value if one row does not have a matching row in another table, and the other table returns a value of NULL.
Example: Use right outer joins in table A and table B to query student names, schools and occupations.
Select a. Name, A. School, B. Name, B. Occupation from a full join B on a. Name =b. Name
Meaning: The result shows all the name rows of table A and B, there is no match in table B, the name of a and the school are displayed as null (NULL). Table B does not match the name in the a table, and B's name and occupation are displayed as null values (NULL).
3. Cross join: There is no association between the table, the result of the query is to return each row of the left table with each row of the right table to join each other, the equivalent of multiplying. Application of occasions less, as understood.
Example: Use cross-linking in table A and table B to query student names, schools and occupations.
Select a. Name, A. School, B. Name, B. Occupation from A full join B
Meaning: Each row in a table is combined with each row in table B to become a new row, resulting in a row number equal to a, multiplied by the number of rows in B
Implementing multi-table queries using T-SQL
