Oracle connection query, thoroughly understand the outer connection (left OUTER join & Right outer connection) __oracle

There are 3 Oracle connection queries: cross-Connect, inner-join, outer-join.

The result of a cross join is a superset of the results of other connections, and the outer join result is a superset of the result of the inner join.

Take the example of Departments_v, Employees_v two View data (4 Department data, 9 personnel data)

1. Cross-linking: also known as the Cartesian product connection, is an unconditional connection between two or more tables, so it connects every piece of data in table 1 with every piece of data in table 2, so the result will be 4*9=36 data

2. Internal connection: The connection query is based on the specified connection condition, so the data that satisfies the join condition appears in the result set.

Two tables in the inner JOIN query, first find the first record of table 1, and then scan table 2 from beginning to end, the records that meet the join conditions and table 1 The first record is connected to the result;

When table 2 is scanned, the second record in table 1 begins, scanning table 2 from beginning to end, and connecting the second record of table 1 with the join condition to the result ...

:

Until all records in table 1 have been processed.

In Oracle, the two types of inner joins are as follows

3. Outer joins: on the basis of the connection, a record of a connection table that does not conform to the join condition is added to the result set.

In Oracle, there are two ways to connect the left and right outer joins.

⑴ first see the standard SQL statement connection method

"Left outer join", on the basis of the connection, the table on the left-hand side of the join operator is added to the result set, and the table on the right is filled with null. (personally think this is more appropriate to explain the way standard SQL statements are connected)

The following figure is "Dept table left join EMP table" At this time can be the Dept table as a benchmark, the join condition is two table department number is the same and the department number is 10;

On the Left Table Dept 10th Department Records and the right-hand table EMP 10th department's employee records meet the connection condition, therefore joins the result set;

Left Table Dept 20th Department Records and the right-hand table EMP 20th Department's employee record does not satisfy the join condition, but this query is the left connection, therefore will add the record in the left table dept20 number department The result set, the right table is null fills;

...

The following figure is "EMP table LEFT JOIN Dept Table" can be analyzed by the above

The right outer join joins the result set with records that do not meet the join condition on the right table of the join operator, and the left table is filled with null. (personally think this is more appropriate to explain the way standard SQL statements are connected)

"The following figure is the Dept table right join EMP table" At this time can be the EMP table as a benchmark, the join condition is the same two-table department number and the department number is 10;

Right-hand table the employee record of the EMP 10th Department and the record in the left table dept 10th meet the connection conditions, so the result set is added;

Right-hand table the employee record of the EMP 20th department and the left Table Dept 20th Department Records do not meet the join conditions, but the query is a right connection, so the right-hand table EMP 20th Department's employee records are added to the result set, the left table is null padding;

...

"The following figure is the EMP table right Join Dept Table" can be analyzed according to the above situation

⑵oracle Extended Connection Mode

"The following figure is dept (+) EMP"(right outer connection)

The right outer join joins the result set with records that do not meet the join condition on the right table of the join operator, and the left table is filled with null.

Therefore, it can be resolved to: the join condition, which is the table with null padding, which table columns are used (+), the other side of the table will not meet the join conditions of the record into the result set, the following figure of the EMP table

The order of the Exchange tables is Employees_v EMP, Departments_v Dept (now called the Left outer join), but the result is the same as the column order of the result is exchanged, for employee_id, first_name, department_id, DEPARTMENT_ID, Department_name

"The following figure is Dept EMP (+)"(left outer join)

"Left outer join", on the basis of the connection, the table on the left-hand side of the join operator is added to the result set, and the table on the right is filled with null.

Therefore, it can be resolved to: the join condition, which is the table with null padding, which table columns are used (+), the other side of the table will not meet the join conditions of the record to join the result set, the following figure of the Dept table

The order of the Exchange tables is Employees_v EMP, Departments_v Dept (now called the Right outer join), but the result is the same as the column order of the result is exchanged, for employee_id, first_name, department_id, DEPARTMENT_ID, Department_name

!!! If the Dept table column should be full plus (+), but the following figure, it will not work, just as the internal connection query.

4. All-Outside connection: on the basis of the connection operator, the table that does not conform to the join condition is added to the result set.

A full outer join is only represented by a standard SQL statement's connection.

Analysis: Select the record that satisfies the condition, then add the result set to the record that does not meet the join condition in the one side of the EMP table, and finally add the result set to the record of the other Side Dept table that does not meet the join condition.

The following figure is to remove a condition and the analysis can be inferred according to the above figure.

The last record is more than "inner join" with the same join condition:

Summary: (individual understanding way)

standard SQL statement connection method:

"A LEFT join B" is a reference to table A on the left-hand side. In the inner JOIN operation mode, a record with the join condition is encountered to add the result set, and a record of the non conforming condition in A is added (because it is the standard), but the record of the corresponding B table is filled with null;

A Right Join B is A reference to the right-hand table B. In the inner JOIN operation mode, a record that meets the join condition is added to the result set, and a record in B that does not meet the condition is added (because it is the standard), but the record of the corresponding table A is filled with null;

Oracle Extended Connection mode: Join conditions which table column (+), then its record is filled with null value (also can be null as a placeholder, nothing empty occupy position), the other side of the table will not meet the join conditions of the records are also added to the query results.