SQL Server connection

In sqlserver, we often use connections. Today we will summarize the basic knowledge of connections. Classification of connections: cross join innerjoin outer join {left outer join left [OUTER] JOIN; right outer join [OUTER] JOIN; full outer join full [outer] join} The following examples show

In SQL server, we often use connections. Today we will summarize the basic knowledge of connections. Classification of connections: cross join inner join outer join {left outer join left [OUTER] JOIN; right outer join [OUTER] JOIN; full outer join full [outer] join} The following examples show

In SQL server, we often use connections. Today we will summarize the basic knowledge of connections.
Connection category:

• CROSS JOIN
• INNER JOIN
• Outer join {left outer join left [outer] join; right outer join right [OUTER] JOIN; full outer join full [OUTER] JOIN}
• Self-connection

The following examples show the similarities and differences between connections:

There are two tables, Teacher and Course:

Cross join:

1. If the WHERE clause is not included, it will return the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows of the two tables;

select * from Course cross join Teacher

Result:

The result is the same as that of SELECT * FROM Course and Teacher.

2. If a WHERE clause exists, the data table with the product of the number of rows in two tables is usually selected based on the WHERE condition.

1. select * from Course, Teacher where Course. T # = Teacher. T #2. select * from Course cross join Teacher where Course. T # = Teacher. T #(Note: Only the where clause can be used for cross join and the on clause cannot be used)3. select * from Course inner join Teacher on Course. T # = Teacher. T #

Result:

In general, the Where statement may have the same efficiency as the Inner join statement in terms of efficiency. However, we do not recommend the where statement for multi-table join.
So if you can choose, we 'd better use Statement 3. Sometimes using Join statements can help check invalid or mistaken Join conditions in statements.

Internal Connection

The inner JOIN indicates the combination of tables on both sides that meet the conditions at the same time, which is equivalent to a common cross join, but the format is different,
Inner join has a Search Condition for the ON clause (equivalent to WHERE) following it to filter the returned rows.
The inner join is not as complex as the Cartesian Product. Therefore, the inner join is more efficient than the cross join of the Cartesian product.

External Connection

Only entries that meet the connection conditions are returned.
The outer join is different. The returned results include not only the rows that meet the connection conditions, but also the left table (when the left Outer Join is performed) and the right table (when the right join is performed) or all data rows connected by both sides (when all external connections.
1) left outer join left [OUTER] JOIN
The data rows that meet the conditions are displayed, and the data rows that do not meet the conditions on the left are displayed. No corresponding entries on the right are displayed as NULL.

select * from Course left outer join Teacher on Course.T#=Teacher.T#

Result:

2) right outer join right [OUTER] JOIN
The data rows that meet the conditions are displayed, and the data rows that do not meet the conditions are displayed on the right. No corresponding entries on the left are displayed as NULL.

select * from Course right outer join Teacher on Course.T#=Teacher.T#

Result:

3) full outer join: full [outer] join

The data rows that meet the conditions are displayed, and the left and right data rows that do not meet the conditions are displayed. The corresponding left and right data rows are NULL, indicating the Union of left, right, and inner connections.

select * from Course full outer join Teacher on Course.T#=Teacher.T#

Result:

Self-connection

In fact, we often use a connection-Self-connection in SQL Server.
The following example shows how to use a self-connection:
Table tree structure table tb_TestTreeView

Solution:Tree hierarchy display
/*
This is a region table that stores the region name and its parent region. Assume that you need to query the region and its parent region.
*/

Method 1 of Self-connection:

Select [Name] as 'region name', (select [Name] from tb_TestTreeView as a where a. ID = B. Parent) as 'parent region name' from tb_TestTreeView as B

Method 2 of Self-connection:

Select a. [Name] as 'region name', B. [Name] as 'Upper-level region name' from tb_TestTreeView as aleft join tb_TestTreeView as B on a. Parent = B. ID

Result:

Self-connection level 3 (left join ):

Select. [Name] as 'region name', B. [Name] as 'Upper-level region name', c. [Name] as 'Upper-level region name' from tb_TestTreeView as aleft join tb_TestTreeView as B on. parent = B. ID left join tb_TestTreeView as c on B. parent = c. id

Result:

Self-connection level 3 (inner join ):

Select. [Name] as 'region name', B. [Name] as 'Upper-level region name', c. [Name] as 'Upper-level region name' from tb_TestTreeView as ainner join tb_TestTreeView as B on. parent = B. ID inner join tb_TestTreeView as c on B. parent = c. id

Result:

Self-connection level 4 (left link ):

Select. [Name] as 'region name', B. [Name] as 'Upper-level region name', c. [Name] as 'Upper-level region name', d. [Name] as 'Upper-level region name' from tb_TestTreeView as aleft join tb_TestTreeView as B on. parent = B. ID left join tb_TestTreeView as c on B. parent = c. idleft join tb_TestTreeView as d on c. parent = d. id

Result:

Self-connection level 4 (internal link ):

Select. [Name] as 'region name', B. [Name] as 'Upper-level region name', c. [Name] as 'Upper-level region name', d. [Name] as 'Upper-level region name' from tb_TestTreeView as ainner join tb_TestTreeView as B on. parent = B. IDinner join tb_TestTreeView as c on B. parent = c. IDinner join tb_TestTreeView as d on c. parent = d. ID

Result: