Four ways to connect the tables in Oracle

Table connection refers to a SQL statement through the Relationship between table and table, from one or more tables to retrieve the relevant data, the main table and the connection between the table can be divided into four kinds, respectively, equal connection, outer connection, unequal connection and self-connection, this article will mainly from the following typical examples to analyze Oracle four different ways to connect the table:

　　1. Equal connections

You can establish an equal join condition by using two tables that have the same meaning.

Only rows that appear in two tables on the Join column and have equal values appear in the query results.

Example query employee information and the corresponding employee's department information;

SELECT * from Emp,dept; SELECT * from Emp,dept WHERE EMP. DEPTNO = DEPT. DEPTNO;

REM displays employee information with a salary of more than 2000 and the department name of the corresponding employee.

　　2. External connection

For outer joins,Oracle can use "(+)" to indicate that 9i can use Left/right/full OUTER JOIN, which is described in conjunction with the example below. In addition to displaying information that matches an equal join condition, information about a table that cannot match an equal join condition is displayed.

Outer joins are identified by (+).

A) Left condition (+) = right condition;

Represents information that does not match the equal join condition in the table in which the right condition is located, in addition to displaying information that matches an equal join condition.

This is also called a "right outer join". Another way to represent this is:

SELECT ... From table 1 right OUTER JOIN table 2 on Join condition

B) Left condition = right condition (+);

Represents information that does not match the equal join condition in the table in which the left condition is located, in addition to displaying information that matches the equal join condition.

This is also referred to as "left outer join".

SELECT ... From table 1 left OUTER JOIN table 2 on Join condition

Example shows employee information and the corresponding department information

--Unable to display employee information without department

--Unable to display departmental information without employees

--select * from Emp,dept WHERE EMP. DEPTNO = DEPT. DEPTNO;

--Make equal connections directly:

SELECT * from emp JOIN DEPT on EMP. DEPTNO = DEPT. DEPTNO;

REM displays employee information and the corresponding department information, showing no employee's department information

--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 the corresponding department information, showing no department of employee information

--select * from Emp,dept WHERE EMP. DEPTNO = DEPT. DEPTNO (+); SELECT * from EMP left OUTER JOIN DEPT on EMP. DEPTNO = DEPT. DEPTNO;

　　3. Unequal connections

The related two columns in the two tables are unequal, and the comparison symbols are generally >,<,..., between. and..

REM Salgrade --desc Salgrade; --select * from Salgrade;

REM Displays the employee's number, name, salary, and the level of the salary.

SELECT empno,ename,sal,salgrade.* from Salgrade,emp WHERE EMP. SAL between Losal and Hisal;

REM Displays the employee's number, name, salary, wage scale, and the name of the department in which it is located;

SELECT Empno,ename,sal,grade,dname from Emp,dept,salgrade WHERE EMP. DEPTNO = DEPT. DEPTNO and EMP. SAL between Losal and Hisal;

　　4. Self-connect

Self-connection is a frequently used connection in a database, and a self-connection can be used to treat one mirror of its own table as another, allowing for some special data to be obtained. The following describes the self-connected methods:

Copy the original table as another table, and the two tables are equally connected by the Cartesian card.

Example shows the employee's number, name, and the name of the employee's manager

SELECT WORKER. Ename,worker. Mgr,manager. Empno,manager. Ename from EMP worker,emp MANAGER

The WHERE WORKER. MGR = MANAGER. EMPNO;

Four ways to connect the tables in Oracle