Oracle external connections (key points)

In the previous articleArticleIn the internal connection discussion, we used the self-connection to find out the names of all people and their superiors. However, there is a problem: King is the boss of the entire Organization and he has no superiors, no king is displayed in the query result. We hope that such a person will be displayed in the results, and the corresponding boss will be blank. For such requirements, we can useExternal Connection.

External ConnectionDivided:Left Outer Join, right outer join, and full outer join.

UseLeft Outer JoinImplementation:

You can also use Outer right connection Implement this query:

Query results of left Outer Join or right Outer Join = results of inner join + a combination of records that cannot be matched in the matching table and a null record.

LearningAll external connectionsBefore that, let's take a look at the data in the following two tables:

We can see that there are no employees in Department 40, and no department has been assigned to employee zhangsan. We use left Outer Join firstQuery the ing between the owner name and the department name:

It can be seen that only the names of all departments are queried for the left outer join, and the names of all departments are not queried. You can useAll external connectionsSolve this problem:

We can see that the full outer connection not only queries employees who have not assigned a departmentZhangsan, You can also query departments without employeesOperations.