[Database] supports easy-to-understand connection between left and right databases.

[Database] supports easy-to-understand connection between left and right databases.

I had been confused about the external connections in the database, but I didn't have a thorough understanding of it. Today I finally created a table and compared several methods in detail. I finally figured it out completely.

Basic Concepts

Left join and right join are the keywords left join and right join in the database. Of course, you can add (+) to the end of a field in oracle to indicate the outer join.

The left join is retained in the relation on the left of left join, that is, it allows null in the right link:Left join returns all rows from the Left table, even if no matching item is found in the right table.

Similarly, the right join retains the tuples in the link on the right of right join, that is, the left link is allowed to be empty;Right join always returns all rows in the Right table, even if no matching item is found in the left table.

In Oracle

select * from a left join b on a.xxx=b.xxx

And

select * from a, b where  a.xxx = b.xxx(+)

Is equivalent.

Example

The hard-to-understand knowledge points can be put into practice for immediate understanding.

Assume that there are two tables a1 and a2.

A1:

A2:

The two tables share the same field id, which can be viewed as different four columns;

We can see that the two tables have the same id 1. If no choice is made, what is the Cartesian product of a1 and a2?

Try to execute the following statements:

select * from a1, a2

You will get the following results:

Cartesian product is a product of all, but it is useless to obtain Cartesian product only because this set is large and many rows have no practical significance, in this case, you need to add the where statement to select the truly useful column.

Try to execute the following statements:

select a.id, b, c, d, e from a,b where  a.id=b.id;

Note: If the fields in Descartes are unique, they do not need to be specified during select, that is, select a. c, B. d, B. e, and so on.

At this time, the result must be only one line, that is

This is easy to understand and is also the most commonly used. If we want to get all the IDs in table a1, no matter whether they are in Table a2, what should we do?

Try any of the following statements:

select a.id,b,c,d,e from a,b where a.id=b.id(+);select a.id,b,c,d,e from a left join b on a.id = b.id;

What results do you get?

Yes, you will find that all IDs in table a1 are selected. If this id does not appear in Table a2, this row will be left blank.

Here, an easy-to-understand external connection is:+ NULL is allowed on the side that appears

Of course, we can add the nvl (d, 0) Statement in the d and e fields to change the corresponding null value to the data we want.

Purpose:

At this point, I believe we have a clear understanding of external connections, but why do we need external connections ??

For example, if the a1 table is a student information table, a2 indicates the student selection table.

For some special reason, the girl whose student ID is 201400002 does not choose a course. Whether she has been absent for one year or has completed full credits, she is willful and does not take a course, if we want to count the student's name, age, course selection information, and so on, if we do not use external connections, it is easy to miss this girl, and. id = B. id (+) is enough to avoid errors caused by such special circumstances!