Common table Connection Methods in Oracle databases

We have introduced the Connection Methods of the first two tables in the Oracle database in two ways of table connection. The following article introduces the last two expressions of the Oracle database, the following describes the specific content of the article.

 
 

  
  
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;  
 
 

Unequal connection

The following describes the related content of the two tables in the last two expressions of the Oracle database. The two columns in the last two tables are not connected. The comparison symbol is generally>, <,..., BETWEEN .. AND ..

 
 

  
  
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 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;  
 
 

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 above content is an introduction to the last two expressions of the Oracle database. I hope you will have some gains.