Oracle's multi-table query joins with tables

Tag: is the image result into the inside connection src Cartesian product generated display

One, multi-table query in any multi-table query will certainly produce Cartesian product problem, but, the results of Cartesian product for the user is meaningless, is the duplication of useless data.    Therefore, we need to eliminate the Cartesian product, then in the Multi-table query, you must have the associated field.     Example: Emp.deptno=dept.deptno; The associated fields in the two tables will eliminate the Cartesian product SELECT * from EMP, dept WHERE Emp.deptno=dept.deptno; Multi-table query, it is recommended to do more exercises to increase proficiency. Second, the table connected within the connection: the use of the WHERE clause to eliminate the Cartesian product, this is an internal connection.    Only data that satisfies the criteria will be displayed. Outer connection: Divided into left outer connection, right outer connection, whole outer connection.

In order to better observe the difference between connections, it is now available in the Dept table for a department with no employees 40, while adding a non-departmental employee to the EMP table.

INSERT into EMP (EMPNO,ENAME,DEPTNO) VALUES (8989, ' HELLO ', null);

The EMP table is currently as follows: Hello this employee has no department

Observation one: The inner connection realizes the effect

SELECT E.empno, E.ename, D.deptno, D.dname

From EMP E, Dept D

WHERE E.deptno = D.deptno;

At this time, no department employees and no employees of the department information is not present, because null judgment is not satisfied.

Observation two: Use Left (outside) connection, hope all employee information is displayed, even if he has no department.

SELECT E.empno, E.ename, D.deptno, D.dname

From EMP E, Dept D

WHERE E.deptno = D.deptno (+);

There were no employees in the department at this time. That is, the data in the left table is all displayed.

Observation Three: Use the right (outside) connection to display all parts of the information.

SELECT E.empno, E.ename, D.deptno, D.dname

From EMP E, Dept D

WHERE E.deptno (+) = D.deptno;

An inner join refers to the occurrence of all data that satisfies the association condition and does not appear to be satisfied. An outer join is one that specifies that all data in a data table is displayed, but there is no corresponding other table data, and the contents are null.

Oracle's multi-table query joins with tables