Four Connection Methods for tables in Oracle

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.