Oracle DatabaseMediumTableOfConnectionIt refers to the connection between a table and a table in an SQL statement. Relevant data is retrieved from one or more tables. In general, the connection between a table and a table can be divided into four types, they are equal connections, external connections, unequal connections, and self-connections. This article will analyze the four different connection methods in the Oracle table from the following typical examples, I hope that the content mentioned below will be helpful to you.
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 employee information and 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 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;
3. Unequal connections
The two related columns in the two tables are not connected. The comparison symbols are generally >,<,..., ..
REM SALGRADE
Desc salgrade;
SELECT * from salgrade;
REM displays the employee's ID, name, salary, and level.
Select empno, ENAME, SAL, SALGRADE. * from salgrade, EMP
Where emp. sal between losal and hisal;
REM displays the employee ID, name, salary, salary level, and department name;
Select empno, ENAME, SAL, GRADE, dname from emp, DEPT, SALGRADE
Where emp. DEPTNO = DEPT. deptno and emp. sal between losal and hisal;
4. Self-connection
Self-join is a frequently used connection method in databases. Using Self-join, you can treat an image of your own table as another table to get some special data. The following describes the self-connection method:
Copy one copy of the original table as another table, and connect the two tables to the same flute.
For example, the employee ID, name, and manager name are displayed.
Select worker. ENAME, WORKER. MGR, MANAGER. EMPNO, MANAGER. ename from emp worker, EMP MANAGER
Where worker. MGR = MANAGER. EMPNO;
The Connection Methods for tables in Oracle databases are divided into four types described above. Mastering the connection methods for tables in Oracle databases is essential for everyone to learn the basic knowledge of Oracle databases, I hope everyone will be able to get some gains from the content mentioned above.