Left JOIN, right connection, cross connection, full outer connection

Left join: to the Left; Right connection: to the right

The first part, the connection inquiry

One, inner connection

The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the column values of the connected columns using comparison operators. The inner connection is divided into three kinds:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query results list all the columns in the connected table, including the repeating columns.

2. Unequal connection: The column values of the concatenated columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, >=, <=, <,!>,!<, and <>.

3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses a select list to indicate the columns included in the query result collection, and deletes the duplicate columns from the attached table.

Second, outer connection

Returns to the query result collection that contains not only rows that meet the conditions of the join, but also all rows of data in the left table (when left outer joins), right table (when the right outer join), or two side tables (full outer joins).

Iii. Cross-linking

A cross join does not take a WHERE clause, which returns the Cartesian product of all rows of data in the two tables that are connected, and the number of rows returned to the result set equals the number of rows in the first table that match the query criteria multiplied by the number of rows in the second table that match the query criteria. For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross joins equals 6*8=48 rows.

Part II, example illustration

Book Table: Student table:

One, inner connection

SELECT *

From [book] as b,[student] as s

where B.studentid=s.studentid

Equivalent to the following (or do not inner keyword, this is the system default)

SELECT *

From [book] as b inner join [Student] as S

On B.studentid=s.studentid

The results are:

Execution process

Equal to the right connection of the inner join. Based on the right of the from [book] inner join [Student] equation, the s of the Student table (the equation right table, s table). StudentID as the benchmark, traversing the B.studentid that matches the Book table (the equation left table, Book table), and then stitching it back. The result contains duplicate columns, B.studentid and S.studentid.

Description

This is not related to where B.studentid=s.studentid or S.studentid=b.studentid locations. It just means satisfying the conditions and not determining who is the benchmark. The following outer joins, the same operation Cross Connect.

Second, outer connection

1. Left Outer connection

Code

SELECT *

From [book] as B left join [Student] as S

On B.studentid=s.studentid

Results

Execution process

That is, the Book table of the From [book] left JOIN [Student] is the base, that is, b in the Book table (table B). StudentID as the benchmark. Traverses the B in the Student table (s table). StudentID. If B. StudentID contains S.studentid matches, the stitching is then traversed to the next s.studentid of the student table, and when the query is finished, enter the next B.studentid. If B. StudentID there are no corresponding S.studentid matches, the items on the left table are displayed, and the items in the right table are displayed as null.

2, right outer connection

Code

SELECT *

From [book] as B right join [Student] as S

On B.studentid=s.studentid

Results

Execution process

That is, the Student table of the from [book] right join [Student] is the base, that is, the s of the Student table (s table). StudentID as the benchmark. Iterate through the S in the Book table (table B). StudentID. If S. StudentID contains B.studentid matches, the stitching is then traversed to the next b.studentid of the Book table, and when the query is completed, the next S.studentid is entered. If S. StudentID there is no corresponding B.studentid match, the item on the right table is displayed, and the entry to the left table is displayed as null.

3. Full outer connection

Code

SELECT *

From [book] as B full outer join [Student] as S

On B.studentid=s.studentid

Results

Execution process

That is, a left outer join is first made in the Book table in the From [book] full outer join [Student], and then a right outer join is performed in the Student table.

Iii. Cross-linking

Code

SELECT *

From [book] as B CROSS Join [Student] as a

ORDER BY B.bookid

Results

Execution process

That is, according to the order of the ID, the right table to join the unconditional stitching over. This is done sequentially so that the record will be the Cartesian product of the two-table record.

Turn from: http://www.cnblogs.com/LeoTerry/archive/2010/03/26/1696988.html