MSSQL internal and external connection (INNER join) statement detailed _mssql

Before that, I was not very clear about the data set from the inner and outer joins of MSSQL. These days to review the SQL of the book, now the idea should be very clear, now send their own understanding of the next. Friends like me who don't understand SQL's connection statements well can help. (The course of this dish, you big guys don't laugh at me, OH: D ）

There are two table A and table B.
Table A is structured as follows:
Aid:int identification seed, primary key, self-increasing ID
Aname:varchar

Data, that is, the record in the case of select * from A is shown in Figure 1 below:


Figure 1:a Table Data

Table B is structured as follows:
Bid:int identification seed, primary key, self-increasing ID
Bnameid:int

Data, that is, the record with select * from B is shown in Figure 2 below:



Figure 2:b Table Data

In order to distinguish between bid and aid, we should not misunderstand, so we set the starting seed of bid to 100.
Have the basic knowledge of SQL know that two tables to do the connection, you must have a connection field, from the data in the table above can be seen, in a table in the aid and B table Bnameid is two connection fields.
Figure 3 below illustrates the relationship between all the recordsets of a connection:



Figure 3: Connection diagram

Now we're going to talk about one by one of our internal and external connections.
1. Inner connection: Use the inner connection to get the record of the public part of the two tables, that is, the recordset C of Figure 3
The statements are as follows: Select * from A JOIN B on A.aid=b.bnameid
The results of the operation are shown in Figure 4 below:



Figure 4: Internal connection data

In fact, the SELECT * from A,b where A.aid=b.bnameid is the same as the run result of the SELECT * from A JOIN B on A.aid=b.bnameid.
2. Outer joins: The outer joins are divided into two kinds, one is the left join and the right join
(1) LEFT join: Figure 3 common part Recordset c+ table a recordset A1.
The statements are as follows: SELECT * from ' A left JOIN B ' on A.aid=b.bnameid
The results of the operation are shown in Figure 5 below:



Figure 5: Left JOIN Access data

Description
In the statement, A is on the left side of B, and it is a left join, so the operation is: a The record of a left-hand connection B = Figure 3 common part Recordset c+ table a recordset A1
In Figure 3, the presence of aid in Recordset C is: 2 3 6 7 8
In Figure 1, the aid available in all Recordset A of Table A is: 1 2 3 4 5 6 7 8 9
Table A recordset A1 exists in aid= (in Figure 1, all aid in Table a)-(in Figure 3, the aid that exists in Recordset C) and ends with: 1 4 5 9
This leads to the record of a left connection B in Figure 5 = Figure 3 Common part Recordset c+ table a recordset A1,
The resulting results in Figure 5 show that both Bnameid and bid records are records in the common part of Figure 3, and that the four records of Bnameid and bid with null aid for 1 4 5 9 are the aid that exists in the table a recordset A1.

(2) Right join: Figure 3 common part Recordset c+ Table B recordset B1.
The statements are as follows: SELECT * from ' right ' JOIN B on A.aid=b.bnameid
The results of the operation are shown in Figure 6 below:



Figure 6: Right Connection data

Description
In the statement, A is on the left side of B, and is the right Join, so it is calculated by: A right-hand connection b record = Figure 3 common part Recordset c+ table B Recordset B1
In Figure 3, the presence of aid in Recordset C is: 2 3 6 7 8
The bnameid that exist in Figure 2 of all Recordset B in table B are: 2 3 6 7 8 11
The bnameid= that exists in table B Recordset B1 (in Figure 2, all bnameid in table B)-(in Figure 3, the aid that exists in Recordset C), finally comes to: 11
This leads to the record of a right connection B in Figure 6 = Figure 3 Common part Recordset c+ Table B Recordset B1,
The resulting results Figure 6 shows that the records of aid and Aname are all recorded in Figure 3 common part of Recordset C, and that the records of aid and Aname for 11 are the Bnameid in table B recordset B1.

Summarize:

Through the above explanation, I believe many people have thought that the situation above (including the diagram in Figure 3) shows only the case of a on the left side of B,
What will happen to the right of the following statement B??
SELECT * from B left JOIN A on A.aid=b.bnameid
SELECT * from B right JOIN A on A.aid=b.bnameid

In fact, flip around figure 3, you can draw the following conclusions:
The recordset derived from the select * from B-left JOIN A on A.aid=b.bnameid and select * from right join B on A.aid=b.bnameid is the same
and
The same is true for the recordset derived from the select * from B right join a on A.aid=b.bnameid and the select * from a LEFT join B on A.aid=b.bnameid.

The first time to write a similar tutorial, I hope the same as I do not understand the SQL connection statement friends can help. Thank you for your support.