Two Connection Methods for tables in Oracle databases

This article describes two different connection methods for tables in Oracle databases. If you want to learn more about these two connection methods, you may wish to browse the relevant content of the following articles and believe it will bring you better comments or methods.

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:

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.

External Connection

The other two connection modes of tables in Oracle databases are external connections. For external connections, "(+)" can be used in Oracle, 9i can use LEFT/RIGHT/full outer join, which will be introduced in detail in the following example. 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;  
 
 

The above content describes the Two Connection Methods of tables in the Oracle database. I hope it will help you in this regard.