connection types for SQL Server tables

1, INNER join (typical join operation, using a comparison operator like = or <>). Includes equality joins and natural joins.
Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines of the students and courses table with the same student identification number.

2, outer joins. An outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify it by one of the following sets of keywords:

1) left JOIN or left OUTER join
The result set of the left outer join includes all rows of the left table specified in the outer clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.

2) Right Join or right OUTER join
A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
3) Full join or full OUTER join
a full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

3. Cross Join
A cross join returns all the rows in the left table, with each row in the left table combined with all the rows in the right table. Cross joins are also called Cartesian product.
Example:
Table A
b table
Internal connection:
Use internal connections in tables A and B to query student names, schools, and occupations
Statement one: Using the WHERE clause
Selcet A.name,a.school,b.name,b.job from a b where a.name=b.name
Or: Use INNER JOIN ..... ON clause
Select A.name,a,school,b.name,b.job from A inner join B on A.name=b.name
Results

External connection:
Left Outer connection:
Select A.name,a.school,b.name,b.job from A left join B on A.name=b.name
Results

 右外连接：          select  A.name,A.school,B.name,B.job  from  A right join B  on A.name=B.name    结果

connection types for SQL Server tables