Several types of SQL connections: internal, leftist, right, full, cross connection

&http://www.aliyun.com/zixun/aggregation/37954.html ">nbsp; SQL connections can be divided into internal, external, and cross joins.

Database data:

Book Table Stu Table

1. Internal connection

1.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.

1.2. Non-equivalent connection: The column values of the connected columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, >=, <=, <,!>,!<, and <>.

1.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 which columns are included in the query result collection, and deletes duplicate columns from the attached table.

INNER JOIN: The inline 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 joins can be used in both of the above ways, wherein the second method of inner can be omitted.

The connection result, as shown above, is connected according to A.stuid = B.stuid.

2. Outer connection

2.1. Left join: is based on chart, the A.stuid = B.stuid of the data to connect, and then the left table does not have a corresponding item, the right table column null

2.2. Right connection: is the right table as the benchmark, the A.STUID = B.stuid of the data to connect, but to the right table does not have the corresponding item, the left table column null

2.3. Full connection: Full outer joins return all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there are matching rows between the tables, the entire result set row contains the data values for the base table.

3. Cross-Connect

Cross-joins: A cross join returns all rows from 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.