Connection (cross connection, inner connection, outer connection, self-connect)

This article is not original

Can be divided into: cross-connection, internal connection, external connection, self-connection

1. Use Cross connect:

It is an unrestricted connection, which is the combination of two tables without any conditions,
That is, all records of the first table are connected to each record in the second table, respectively.
Combined into a new record, the number of rows of the result set after the connection is the product of the number of rows of two tables,
Listed as the sum of the two table columns.

Syntax: (1) SELECT list List from table name 1 cross Join table name 2
(2) SELECT list List from table name 1 (alias), table name 2

Note: When you display some fields, you specify them with the table name.

Example: Select A.book_name, B.book_num from table1 A, table2 b

2. Use the Inner connection:

An inner join is the display of a record that satisfies a condition in two tables, that is, a cross-connected
Based on the addition of conditions, the general use of "PRIMARY KEY = Foreign key" form.

Syntax: (1) SELECT list List from table name 1 [inner] Join table name 2 on conditional expression

(2) Select column list from table name 1, table name 2 where conditional expression

Example: SELECT * FROM table1 a joins table2 b on a.book_name = B.book_name

3. Use an external connection:

Outer joins: Left outer connection, right outer connection, full outer connection

(1) Left outer connection:

It combines all the data in the left table with each piece of data in the right table,
The result set shows the non-conforming bars in the left table in addition to the records that are inside the join
and fill in the Null value on the corresponding column in the right table.

Syntax: SELECT * FROM table name 1 left [outer] Join table name 2 on conditional expression

(2) Right outer connection:

It combines all the data in the right table with each piece of data in the left table,
The result set shows the non-conforming bars in the right table in addition to the records that are in the connected
and fill in the Null value on the corresponding column in the left table.

Syntax: SELECT * FROM table name 1 right [outer] Join table name 2 on conditional expression

(3) Full outer connection:

The full outer join is the combination of all the records in the left table, respectively, with each of the right tables.
In addition to returning internal connection records, the result set shows two tables that do not conform to the
All records of the condition and adds a null value to the corresponding position in the left table and right table.

Syntax: SELECT * FROM table name 1 full join table name 2 on conditional expression

4. Using self-connect:

A self-connection is a connection between two copies of a table, which is the same table
different rows are connected; When using self-joins, you must specify two aliases for this table.
Make it logically a two table.

Example: Select A.biao_jia, A.biao_liang from Qian a join Qian b
On A.biao_jia = B.biao_jia and A.biao_liang <> B.biao_liang

5. Merging result sets:

Use the Union statement to combine more than two result sets into a single result set.

Syntax: Select ......
Union
Select .......
：
：

The following three-point limit is imposed when merging result sets:
(1) The number of columns for the two SELECT statements of the Union merge result set is the same,
The data type of the column to be used corresponds to the same;

(2) The column names in the final result set are from the first SELECT statement;

(3) When merging result sets, duplicate rows are deleted from the final result set by default.
Unless you use keywords.

Example: Select Book_name, book_id into #new_1 from YI
Go
Select Egg_name, Egg_shu to #new_2 from egg
Go
SELECT * FROM #new_1
UNION ALL
SELECT * FROM # new_2

Connection (cross connection, inner connection, outer connection, self-connect)