Reviewed ~~~ Previously, I was not very clear about the data set obtained from the inner connection and outer connection in. 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. (For example
Reviewed ~~~ Previously, I was not very clear about the data set obtained from the inner connection and outer connection in. 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. (For example
Reviewed ~~~
Previously, I was not very clear about the data set obtained from the inner connection and outer connection in. 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:
: Table A Data
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:
: Table B data
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:
: 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 common parts of the two tables, that is, the record set C.
The statement is as follows: Select * from a join B on a. Aid = B. Bnameid
The running result is shown in 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: This is the A1 record set of the C + Table A record set of the common part.
Statement: select * from A Left join B on a. Aid = B. Bnameid
The running result is shown in Figure 5:
: Left join data
Note:
In A statement, A is on the Left of B and is Left Join. Therefore, the operation mode is as follows: record of A Left Join B = record set of public part C + Table A record set A1
The Aid that exists in the record set C is: 2 3 6 7 8
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 (all Aid in Table A)-(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 = A1 of the C + Table A record set of public part,
In the final result, we can see that the records with Bnameid and Bid non-NULL are all records in the common part of record set C; 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: that is, the common part of the 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:
: Right join data
Note:
In A statement, A is on the left of B and is Right Join. Therefore, the operation mode is as follows: record of A Right Join B = common part record set C + Table B record set B1
The Aid that exists in the record set C is: 2 3 6 7 8
The Bnameid in all record sets B in Table B is: 2 3 6 7 8 11
In table B, the Bnameid = (all bnameids in Table B)-(the Aid exists in record C) exists in Table B1, and the result is: 11.
From this, we can conclude that the record of right-connected B in A = B1 in the C + Table B record set of public part,
In the final result, we can see that the records of Aid and Aname non-NULL are all records in the common part of record set C; the record with the Aid and Aname NULL as 11 is the Bnameid in Table B record set B1.
Summary:
Through the above calculation explanation, I believe many people have thought that the above situation (including the relationship diagram) only describes the situation where A is on the left of 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, the following conclusions can be drawn after turning the left and right:
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.
