Oralce database table connection category

Data Table connections include:
1. Inner join (natural join): Only the rows matching the two tables can appear in the result set.
2. external connections: including
(1) left Outer Join (the table on the left is not restricted)
(2) Right Outer Join (the table on the right is not restricted)
(3) Full outer join (no restrictions are imposed on both the left and right tables)

3. Self-join (the connection occurs in a base table)

Standard SQL Syntax:
Select table1.column, table2.column
From table1 [inner | left | right | full] join table2 on table1.column1 = table2.column2;

Inner join indicates inner join; left join indicates left Outer join; right join indicates right outer join; full join indicates full outer join;
The on clause is used to specify the connection conditions. Note: If you use the from clause to specify the internal and external connections, you must use the on clause to specify the connection conditions;
If you use the (+) operator to specify the outer join, you must use the where clause to specify the join condition.

If the primary key column of the master table has the same name as the external key column of the slave table, you can use the natural join keyword to automatically perform the internal join operation.
Select dname, ename from dept natural join emp;

Example:There are two tables (students and classes)

 Create table students (studentno number, studentname varchar2 (20), classname varchar2 (20 ))

Create table classes (id number, classname varchar2 (20 ));

1) left Outer Join:

When the data in the preceding two tables is

Students table:

Classes table

Execute the following left Outer Join statement:

Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid = B. classid (+ );

Result:

Note: The left link displays all the data in the left table.

2) Right outer join:

When the data in the preceding two tables is

Students table:

Classes table:

Execute the following right outer join statement:

Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid(+)= B. classid; -- pay attention to the position (+) Here. The outer right is connected to the left, that is, the opposite position.

Result:

Note: The right link displays all the data in the right table.

3) natural links

In the case of the data in the above two tables, execute the following natural link statement and the results are the same:

Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid = B. classid;

Result:

In short,

The left join shows all on the left and the right are the same as those on the left.
The right connection shows all and the left are the same as the right
Only matching conditions are displayed for internal connections!