"Database" easy to understand the left and right connections

has been to the database inside the outside connection some doubts, understanding is not very thorough, today finally himself built a table, the detailed comparison of several kinds of writing, finally thoroughly to figure out.

Basic Concepts

Left join Connect, right connection is the key word in the database left join, and then, of course, in Oracle can also add (+) after a field to indicate the outer join

The left connection retains the tuple that appears in the relationship to the left of the remaining join, which is to allow the right relationship to be empty; Popular point: The left join returns all of the rows from the table, even if no matching items are found in the right table.

The right connection retains the tuple that appears in the relationship to the right of the join, that is, allows the left relationship to be empty,and right joins will always return all rows in the table, even if no matching items are found in the left table.

In Oracle

SELECT * from a LEFT join B on a.xxx=b.xxx

and the

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

is equivalent.

examples Show

Again difficult to understand the point of knowledge put into practice to operate, immediately can understand a lot.

Let's say we have two tables A1 and A2.

A1:

A2:

The two tables have a common field ID representing the same meaning, and the Bcde field can be viewed as a different 4 column;

You can see that both tables have an identical ID of 1; what does the Cartesian product of A1 and A2 look like without making any choices?

Try executing the following statement:

SELECT * FROM A1, A2

you will get the following results:

The Cartesian product is a full product, but it is useless to get a Cartesian product, because the set is large, and many rows have no practical significance, and you need to add a where statement to select a column that is really useful.

Try executing the following statement:

Select a.ID, B, C, D, E from A, where  a.id=b.id;

Note that if the field in the Cartesian product is unique, it can not be marked at the time of the Select, that is, do not write select A.C, B.D, B.E, etc.

The result must be only 1 rows, which is

It's easy to understand, and it's what we use most often, so what if we want all the IDs in the A1 table, and whether he's not in A2?

Try any of the following statements:

Select A.id,b,c,d,e from A, a where a.id=b.id (+), select A.id,b,c,d,e from a LEFT join B on a.id = b.ID;

What results will you get?

Yes, you will find that we have selected all the IDs in the A1 table, and if this ID does not appear in the A2 table, then this line will be left blank.

See here, a more understandable understanding of the outside connection is the trick: the+ sign appears on the side of the allowed null

Of course, we can change the corresponding null value to the data we want by adding the NVL (d,0) statement to the D and E fields.

Objective:

Here, I believe we have a clear understanding of the external connection, but why do we need an external connection??

For a simple example, if the A1 table is a Student information table, A2 indicates that students choose a timetable

Because of a special reason, the student number is 201400002 of the female classmate is not selected a course, whether she is 1 years of school leave or has been full of credit, is wayward, a course is not repaired, if we want to statistics student's name, age, course information and so on, if not use outside the connection, It is easy to leave this female classmate out, and a a.id=b.id (+) is enough to avoid this special situation caused by the mistake!

"Database" easy to understand the left and right connections