Description of internal and external JOIN statements in MSSQL

Previously, I was not very clear about the data sets produced by the inner connection and outer connection in MSSQL. I have reviewed SQL books over the past few days. Now I think it is clear that I am going to give my understanding to you. I hope that my friends who are not familiar with SQL connection statements can help me. (Don't joke about this course, for example, D)

There are two tables A and B.
The structure of Table A is as follows:
Aid: int; identifies the seed, primary key, and auto-increment ID
Aname: varchar

The data, that is, the records from select * from A, are shown in 1:


Figure 1: data in Table

Table B has the following structure:
Bid: int; identifies the seed, primary key, and auto-increment ID
Bnameid: int

Data, that is, the records from select * from B, are shown in Figure 2:



Figure 2: data in Table B

In order to distinguish Bid from Aid, we do not want to misunderstand it, so we set the starting seed of Bid to 100.
Anyone with basic SQL knowledge knows that to connect two tables, a connection field is required. From the data shown in the table above, the Aid and Bnameid in Table B in Table A are two join fields.
3. describes the relationship between all connected record sets:



Figure 3: Connection Diagram

Now we will explain the internal connection and external connection one by one.
1. Inner join: Using Inner join, you can obtain the records of the public parts of the two tables, that is, the record set C in figure 3.
The statement is as follows: Select * from a join B on a. Aid = B. Bnameid
The running result is shown in Figure 4:



Figure 4: internal connection data

In fact, the running results of select * from A, B where A. Aid = B. Bnameid and Select * from a join B on a. Aid = B. Bnameid are the same.
2. Outer JOIN: There are two types of Outer JOIN: Left JOIN and Right JOIN)
(1) Left JOIN: in figure 3, the C + Table A record set A1 is A common record set.
Statement: select * from A Left join B on a. Aid = B. Bnameid
The running result is shown in Figure 5:



Figure 5: left join data

Note:
In A statement, A is on the Left of B and is Left Join. Therefore, the calculation method is as follows: records connecting A to B on the Left = Figure 3 Public part record set C + Table A record set A1
In Figure 3, the Aid in record set C is: 2 3 6 7 8
In Figure 1, the Aid in all record sets A in Table A is: 1 2 3 4 5 6 7 8 9
Aid = in Table A record set A1 (in Figure 1, all Aid in Table A)-(in Figure 3, Aid exists in record set C), the result is: 1 4 5 9
From this we can conclude that the record of A left connected to B in Figure 5 = Figure 3 Public part record set C + Table A record set A1,
The final result Figure 5 shows that records of Bnameid and Bid non-NULL are all records in C of record set in figure 3; the four records with a null Aid of Bnameid and Bid of 1 4 5 9 are the Aid in Table A record set A1.

(2) Right JOIN: Fig. 3 common record set C + Table B record set B1.
Statement: select * from A Right join B on a. Aid = B. Bnameid
The running result is shown in Figure 6:



Figure 6: Right join data

Note:
In A statement, A is on the left of B and is Right Join. Therefore, the calculation method is as follows: records connecting A to B = Figure 3 common part record set C + Table B record set B1
In Figure 3, the Aid in record set C is: 2 3 6 7 8
In Figure 2, The Bnameid in all record sets B in Table B is: 2 3 6 7 8 11
Bnameid = in Table B record set B1 (in Figure 2, all bnameids in Table B)-(in Figure 3, Aid exists in record set C), the result is: 11.
From this we can conclude that the record of right join B in Figure 6 = Figure 3 common part record set C + Table B record set B1,
The final result Figure 6 shows that the records of Aid and Aname non-NULL are all the records in the public part of record set C in figure 3; the record with the Aid and Aname NULL as 11 is the Bnameid in Table B record set B1.

Summary:

I believe many people have come to think that the above situation (including the diagram in figure 3) only describes the situation on the left of A on B,
What will happen to B on the right of A in the following statement ??
Select * from B Left join a on a. Aid = B. Bnameid
Select * from B Right join a on a. Aid = B. Bnameid

In fact, if you flip around figure 3, you can draw the following conclusion:
Select * from B Left join a on a. Aid = B. Bnameid and select * from A Right join B on a. Aid = B. Bnameid have the same set of records.
While
Select * from B Right join a on a. Aid = B. Bnameid and select * from A Left join B on a. Aid = B. Bnameid.

The first time I wrote a post similar to this tutorial, I hope that my friends who do not understand SQL connection statements will be helpful. Thank you for your support.