Oracle Database (Oracle DB) Select Multiple Table association Query method

Syntax and introduction of SELECT statement in Oracle database

SELECT [All | DISTINCT] < field expression 1[,< field expression 2[,...]

From < table name 1>,< table name 2>[, ...]

[WHERE < sieve selection criteria expression;]

[GROUP BY < grouping expressions > [having< grouping conditional expressions;]]

[ORDER by < Field >[ASC | DESC]]

Statement Description:

[] square brackets are optional

[GROUP BY < grouping expressions > [having< grouping conditional expressions;]]

Refers to grouping results by the values of < grouping expressions >, which are recorded as a group with "having"

The phrase will be output only if the group satisfies the specified criteria.

[ORDER by < Field >[ASC | DESC]]

Display results sorted by < field > value Ascending or Descending

The order in which the SQL clauses are executed:

1. From

2. WHERE

3. GROUP by

4. Having

5. SELECT

6. ORDER by

Www.169it.com

Multi-Table Association query

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 analyze four different ways to connect Oracle tables from the following typical examples:

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;

Source:Oracle Database Select Multi-Table association Query method