SQL: Internal connection, left outer join, right outer join, full connection, cross connect difference

There are two table A and table B. The structure of table A is as follows: Aid:int; identity seed, primary key, self-increment ID Aname:varchar data case, that is, the record with select * from A is shown in 1:

Figure 1:a Table Data
The structure of table B is as follows: Bid:int; identity seed, primary key, self-increment ID bnameid:int data case, that is, the record with select * from B is shown as 2:


Figure 2:b Table Data
In order to distinguish between bid and aid, there is no misunderstanding, so we set the starting seed of bid to 100. People with basic knowledge of SQL know that two tables to make a connection, you must have a connection field, from the data in the table above can be seen in a table in the aid and B table Bnameid is two connection fields. 3 illustrates the relationship between all the recordset that is connected:
Figure 3: Connection diagram
now we explain the internal and external connections one by one. 1. Internal connection: The record of the public part of two tables can be obtained by using the inner connection, that is, the recordset C Statement of Figure 3 is as follows: SELECT * from A joins B on A.aid=b.bnameid run results as shown in 4: actually select * from A, A where a.aid= B.bnameid and select * from A JOIN B on A.aid=b.bnameid run the same result.

Figure 4: Internal connection data

2. Outer joins: There are two types of outer joins, one is the left join and the right join
(1) LEFT join: That is, figure 3 public part Recordset c+ table a recordset A1.
The statement is as follows: SELECT * from A left JOIN B on A.aid=b.bnameid
The results of the operation are as follows: 5


Figure 5: Left JOIN Access data

Note: In the statement, A is on the left side of B, and it is a left join, so it is operated as follows: A record of connection B to the right = Figure 3 public part Recordset c+ table a recordset A1
In Figure 3, the aid that exists in Recordset C is: 2 3 6 7 8
In Figure 1, table A, the aid that exists in all Recordset A is: 1 2 3 4 5 6 7 8 9
The aid= in Table a recordset A1 (all aid in a table in Figure 1)-(in Figure 3, the aid that exists in Recordset C), is eventually obtained: 1 4 5 9
Thus the record of a left connection B in Figure 5 = Figure 3 Public part Recordset c+ table A recordset A1, the final result Figure 5 can be seen Bnameid and bid non-null records are in Figure 3 of the public part of the recordset C records; Bnameid and bid null aid is 1 4 5 The four records of 9 are the aid that exists in table a recordset A1.

(2) Right join: That is, figure 3 public part recordset c+ Table B recordset B1.

The statement is as follows: SELECT * from A right JOIN B on A.aid=b.bnameid run results as shown in 6:

Figure 6: Right Connection data
Note: In the statement, A is on the left side of B, and it is right join, so it is operated as follows: The record of the B is connected to a---figure 3 Public part recordset c+ Table B Recordset B1
In Figure 3, the aid that exists in Recordset C is: 2 3 6 7 8
In Figure 2, table B, all of which exist in Recordset B, are: 2 3 6 7 8 11 Bnameid
The bnameid= (all bnameid in table B) in table B Recordset B1 (in Figure 2, which is the aid that exists in Recordset C), is eventually: 11
This results in Figure 6 A right connection b record = Figure 3 public part recordset c+ Table B Recordset B1, the final result Figure 6 can be seen in the aid and aname non-null records are in Figure 3 public part of the record set C The record of aid and ANAME null for aid 11 is the Bnameid that exists in table B recordset B1.

(3) Full join or complete OUTER join (fully connected).
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

Select a.*,b.* from a full join B on a.id=b.parent_id

The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null

Cross connect: Two tables combined without conditions, number of bars = Figure 1 * Figure 2

Reprint: http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html

SQL: Internal connection, left outer join, right outer join, full connection, cross connect difference