Differences between inner connection, left outer connection, right outer connection, and Cross Connection

Differences between inner connection, left outer connection, right outer connection, and Cross Connection
Inner join, left Outer Join, right Outer Join, cross join difference http://blog.csdn.net/cnham/archive/2008/06/25/2584936.aspx
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. (Let's take A look at this course. You can't joke about it. Oh, D) There are two tables A and B. Table A has the following structure: Aid: int; identifies the seed, primary key, and auto-incrementing ID Aname: varchar data, that is, records from select * from A, as shown in 1:


Figure 1: data in Table
Table B has the following structure: Bid: int; identifies the seed, primary key, and auto-incrementing 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: You can use the inner JOIN to obtain the records of the public parts of the two tables. That is, the C statement of the record set in Figure 3 is as follows: Select * from a join B ON. aid = B. the execution result of Bnameid is shown in Figure 4: select * from A, B where. aid = B. bnameid and Select * from a join B ON. aid = B. the running result of Bnameid is the same.

Figure 4: internal connection data

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 Left Join, so the operation 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.

The statement is as follows: select * from A Right join B on a. Aid = B. Bnameid:
 

 

 

Figure 6: Right join data
Note: In A statement, A is on the left of B and Right Join is used. Therefore, the operation 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.

Cross join: When two tables are joined without conditions, the number of entries = Figure 1 * Figure 2