SQL internal and external connection usage and differences

The data tables are connected with:

1. Inner connection (natural connection): only two rows matching the table can appear in the result set

2. External connection: including

(1) Left outer connection (the table on the left is unrestricted)

(2) Right outer connection (no restriction on the right table)

(3) Full-outer connection (no restriction on both the left and right tables)

3. Self-Connection (connection occurs within a base table)

Select A.studentno, A.studentname, B.classname

From students A, classes B

where A.CLASSID (+) = B.classid;

Studentno Studentnam CLASSNAME

---------- ---------- ------------------------------

1 A first Grade first class

2 B first Grade class two

Grade three class one year

The above statement is the right connection:

That is, the other side of the position of "(+)" is the direction of the connection, and the right connection describes all

Records are displayed regardless of whether they are matched on the left. That is to say, the above example, no

Will there be a class without a student's case, the class name will be in

Appears in the query structure.

Instead:

Select A.studentno, A.studentname, B.classname

From students A, classes B

where A.classid = B.classid (+);

Studentno Studentnam CLASSNAME

---------- ---------- ------------------------------

1 A first Grade first class

2 B first Grade class two

3 C

Is the left connection, whether or not the student has a department number that can be matched in a class,

The student's record will be displayed.

Select A.studentno, A.studentname, B.classname

From students A, classes B

where a.classid = B.classid;

This is the usual internal connection, showing that both tables meet the criteria of the record

Anyway

Left join to show the left and right side of the same

The right connection shows all the right and left and right

The inner connection is only displayed to meet the criteria!

SQL internal and external connection usage and differences