Internal Connection and external connection

Common connections in Oracle SQL statements include inner join and outer join. Internal Connections include equijoin, non-equijoin, and self-join; external connections are divided into left connections and right connections. The default value is the internal join equivalent.

To facilitate the creation of two simplest tables A and B, see the following table structure to analyze the differences between inner join and outer join.

Figure 1 Figure 2

 

To connect two tables, you must have a connection field. in tables A and B, the connection field is aid and bnamid, indicating the connection relationship diagram 3.

Figure 3

(1) Inner join: Use Inner join (equivalent) to obtain the blue public part C, that is, dataset C in Figure 3. The result set is as follows:

Figure 4

 

In fact, select * from a join B on. aid = B. bnamid; equivalent to select * from a, B where. aid = B. bnamid; Note: Non-equivalent join queries data in a specific range. A self-join queries data in two tables. (2) External join: divided into left Outer Join and right outer join the left Outer Join is the blue section of the public display C1 + shows the yellow record set A1, the display statement is equivalent to select * From, B Where. aid = B. bnamid (+ );

 

Figure 5

 

The right outer join is the blue section of the public display. C1 + shows the green B1. The display statement is equivalent to select * from a, B where a. Aid (+) = B. bnamid;

Figure 6

 

The situations of table A and table B are relative. The above experiments show that A is on the left. In fact, the results of a left join B and B right join a are the same.

 

 

Http://blog.csdn.net/hellowheat/article/details/4207467

Internal Connection and external connection