Connection to SQL Server

In SQL Server, we can often use the connection, and today we summarize the basics of connectivity.
Categories of connections:

• Cross Join
• Inner connection INNER JOIN
• Outer JOIN {LEFT outer connection [OUTER] join, right outer join; [OUTER] join; full outer join [OUTER] Join}
• Self-connect

Here are some examples of the similarities and differences of each connection:

There are two Tables teacher table and course table:

Cross Connect:

1. If you do not have a WHERE condition clause, it will return the Cartesian product of the two tables connected, and the number of rows returned is equal to the product of two table rows;

SELECT * from Course Cross join Teacher

The result is:

The result shows that the result is the same as the SELECT * from Course,teacher.

2. If there is a WHERE clause, the data table, which is usually the product of two table row numbers, is then selected from 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: After cross join conditions can only be used where, not on)
3. Select * FROM Course inner join Teacher on course.t#=teacher.t#

The result is:

In general, in terms of efficiency, where may have the same efficiency as inner join, but we do not recommend the where statement in a multi-table connection.
So if you can choose, we'd better use statement 3, sometimes using Join statements to help check for invalid or mis-written association conditions in a statement.

Internal connection

An inner join represents a combination of both sides of a table that matches a condition, which is equivalent to a normal cross JOIN, except that the format is different.
The INNER join has a search condition followed by an ON clause (equivalent to where) to filter the returned rows.
The inner connection is not as complex as the Cartesian product. The data table of the product of the number of rows, so the efficiency of the inner connection is higher than the cross-connection of the Cartesian product.

External connection

Specifies the inner join of the condition, returning only the entry that meets the join condition.
The outer joins are different, and the returned results include not only rows that meet the join criteria, but also all rows of data that have the left table (when left outer joins), the right table (when connected right), or both sides (when connected at all).
1) left outer connection [OUTER] Join
Displays the data rows that match the criteria, displaying the data rows on the left side of the data table that do not match the criteria, and no corresponding entry on the right to display null.

SELECT * from Course left outer joins Teacher on course.t#=teacher.t#

The result is:

2) Right-side connection-[OUTER] Join
Displays the data rows that match the criteria, displaying the data rows on the right side of the data table that do not match the criteria, and no corresponding entry on the left showing null.

SELECT * FROM Course right outer join Teacher on course.t#=teacher.t#

The result is:

3) Full-outer connection complete [outer] Join

Displays the data rows that match the criteria, and displays the left and right rows of data that are not eligible, showing null on both sides of the left side, which shows the left join of the connection, the correct join, and the inner join.

SELECT * FROM Course full outer join Teacher on course.t#=teacher.t#

The result is:

Self-connect

In fact, in SQL Server, we also often use a connection-self-connection.
Use the following example to learn about self-linking:
Table tree-shaped structure table Tb_testtreeview

solve the problem: tree-level hierarchy display
/*
This is a regional table, which contains the area name and its parent area, assuming that the region and its parent area should now be queried.
*/

Method 1 for self-join:

Select [Name] as ' region name ',
  (select [Name] from Tb_testtreeview as a
    where a.id = b.parent) as ' Superior region name '
From Tb_testtreeview as B

Method 2 for self-join:

Select A.[name] as ' region name ',
       B.[name] as ' superior area name '
From Tb_testtreeview as a
Left Join Tb_testtreeview as B
     On a.parent = b.ID

The result is:

Self-connecting level three (left join):

Select A.[name] as ' region name ',
       B.[name] as ' superior area name ',
       C.[name] as ' upper area name '
From Tb_testtreeview as a
Left Join Tb_testtreeview as B
     
Left join Tb_testtreeview as C
     On B.parent=c.id

The result is:

Self-connecting level three (inner join):

Select A.[name] as ' region name ',
       B.[name] as ' superior area name ',
       C.[name] as ' upper area name '
From Tb_testtreeview as a
INNER JOIN Tb_testtreeview as B
     
INNER JOIN Tb_testtreeview as C
     On B.parent=c.id

The result is:

Self-connecting level four (left link):

Select A.[name] as ' region name ',
       B.[name] as ' superior area name ',
       C.[name] as ' upper area name ',
       D.[name] as ' upper area name '
From Tb_testtreeview as a
Left Join Tb_testtreeview as B
     
Left join Tb_testtreeview as C
     On B.parent=c.id
Left join Tb_testtreeview as D
     On C.parent=d.id

The result is:

Self-connecting level four (inner link):

Select A.[name] as ' region name ',
       B.[name] as ' superior area name ',
       C.[name] as ' upper area name ',
       D.[name] as ' upper area name '
From Tb_testtreeview as a
INNER JOIN Tb_testtreeview as B
     On a.parent = b.ID
INNER JOIN Tb_testtreeview as C
     On b.parent = C.id
INNER JOIN Tb_testtreeview as D
     On c.parent = D.id

The result is:

Connection to SQL Server