Four table Connection Methods in Oracle Database (1)

A table connection refers to the association between tables in an SQL statement to retrieve relevant data from one or more tables, in general, the table and table connections can be divided into four types: equal connection, external connection, unequal connection, and self-connection, this article mainly analyzes the four Connection Methods of Oracle tables from the following typical examples:

1. Equal connection

Two columns with the same meaning can be used to create equal join conditions. Only rows with the same value in the two tables of the connected column will appear in the query results.

For example, query the employee information and the department information of the corresponding employee:

SELECT * FROM EMP,DEPT; 　SELECT * FROM EMP,DEPT 　WHERE EMP.DEPTNO = DEPT.DEPTNO;

REM displays information about employees whose salaries exceed 2000 and their department names.

2. External Connection

For external connections, "(+)" can be used in Oracle, and "LEFT/RIGHT/full outer join" can be used in 9i. The following describes the external connections with instances. In addition to the information that matches the same connection condition, the information of a table that cannot match the same connection condition is displayed.

External connections are identified by (+.

A) left condition (+) = right condition;

In addition to displaying information that matches equal connection conditions, it also displays information that cannot match equal connection conditions in the table where the right condition is located. This is also called "right Outer Join". Another representation method is:

SELECT... FROM table 1 right outer join table 2 on join conditions

B) left condition = right condition (+ );

In addition to displaying information that matches equal connection conditions, it also displays information that cannot match equal connection conditions in the table where the left condition is located. This is also called "left Outer Join ".

SELECT... FROM table 1 left outer join table 2 on join conditions

Example: displays employee information and Department Information

-- Unable to display employee information without a department

-- Unable to display department information without employees

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

-- Directly perform equal connections:

SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

REM displays employee information and corresponding department information, and displays department information without employee

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

REM displays employee information and corresponding department information, and displays employee information without Department

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;