Multi-table join (inner join/left join/right join)

Inner join

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.