What is the difference between inner join, left outer join, right outer join and cross join?

The difference between inner joins, left outer joins, right outer joins, and cross joins

Before, I was not very clear about the data sets obtained by inner joins and outer joins in MSSQL. I have reviewed the SQL books again in the past few days, and my thinking should be very clear now. Now I will share my understanding for everyone to review. I hope that friends like me who don't understand the SQL connection statement can help. (Please stop laughing at me for posting a tutorial on such a dish, haha ​​:D) There are two tables A and B. The structure of table A is as follows: Aid: int; identification seed, primary key, auto-increment ID Aname: varchar The data situation, that is, the record situation obtained by using select * from A is shown in Figure 1 below:

Figure 1: Table A data
The structure of table B is as follows: Bid: int; identification seed, primary key, auto-increment ID Bnameid: int The data situation, that is, the record situation using select * from B is as follows Shown in Figure 2:

Figure 2: Table B data
In order to distinguish Bid and Aid, we do not allow everyone to have Misunderstanding, so set the starting seed of Bid to 100. Anyone with basic knowledge of SQL knows that to connect two tables, there must be a connection field. From the data in the above table, we can see that Aid in table A and Bnameid in table B are two connection fields. . Figure 3 below illustrates the relationship between all connected record sets:

Figure 3: Connection diagram
Now we connect internally and outer joins are explained one by one. 1. Inner join: Use inner join to obtain the records of the common part 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 as shown in Figure 4. As shown: In fact, the operation 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.

Figure 4: Inner join data

2. Outer join: There are two types of outer joins, one is left join (Left JOIN) and right join (Right JOIN)
(1) Left join (Left JOIN): that is, the public part record set C + table A record set A1 in Figure 3.
The statement is as follows: select * from A Left JOIN B ON A.Aid=B.Bnameid
The running results are shown in Figure 5 below:

Figure 5: Left join data

Explanation: In the statement, A is on the left side of B, and it is a Left Join, so the operation method is: A left join B records = Figure 3 public part record set C + table A record set A1
In Figure 3, that is, the Aid that exists in record set C is: 2 3 6 7 8
In Figure 1, that is, the Aid that exists in all record sets A of table A is: 1 2 3 4 5 6 7 8 9
The Aid that exists in the record set A1 of table A=(In Figure 1, it is all the Aid in table A)-(In Figure 3, it is the Aid that exists in the record set C). The final result is: 1 4 5 9
From this, we can conclude that the records of A left connected to B in Figure 5 = the public part record set C in Figure 3 + the record set A1 of table A. The final result can be seen in Figure 5 that Bnameid and Bid are not NULL. The records are all in the record set C in the public part of Figure 3; the four records with Bnameid and Bid as NULL and Aid as 1 4 5 9 are the Aids that exist in the record set A1 of table A.

(2) Right JOIN: That is, the public part record set C in Figure 3 + the record set B1 of table B.

The statement is as follows: select * from A Right JOIN B ON A.Aid=B.Bnameid The running result is shown in Figure 6 below:

Figure 6: Right join data
Description: In the statement, A is to the left of B and is a Right Join, so the operation method is: A right join B records = Figure 3 public part record set C + table B record set B1
In Figure 3, the Aid that exists in record set C is: 2 3 6 7 8
In Figure 2, the Bnameid that exists in all record sets B of table B is: 2 3 6 7 8 11
Table The Bnameid that exists in the B record set B1 = (in Figure 2, that is, all the Bnameids in the B table) - (in Figure 3, that is, the Aid that exists in the record set C). The final result is: 11
This leads to Figure 6 The records of the right connection of A in B = the public part record set C in Figure 3 + the table B record set B1. The final result can be seen in Figure 6 that the non-NULL records of Aid and Aname are all in the public part record set C of Figure 3. Record; the record with Aid and Aname as NULL and Aid as 11 is the Bnameid that exists in the record set B1 of table B.​​
Cross connection: When two tables are joined without conditions, the number of entries = Figure 1 * Figure 2

The above is the detailed content of What is the difference between inner join, left outer join, right outer join and cross join?. For more information, please follow other related articles on the PHP Chinese website!