Oracle Database basic learning (5) Multi-Table query, oracle Database

Oracle Database basic learning (5) Multi-Table query, oracle Database

Multi-Table query:Two or more tables are required for the query result. Multi-table join is required to generate multi-Table query.

 

1. Internal Connections (equivalent connections)

 

Example: display the contents of a connected table

select * from dept d, emp e where d.deptno = e.deptno;

Note: The content is displayed only when the d. deptno = e. deptno condition is met. Otherwise, the content is not displayed.

 

2. Outer Join: Let the equivalent value determine that data on both sides of the left and right can all be displayed, and use the "(+)" when using the outer join method )"

Format:

Left Outer Join: field = field (+)

Outer right join: Field(+)= Field

 

Example: display the employee name, position, and lead name

 

Analysis:

Confirm the data to be used:

 

1. employee name in the emp table, position (alias: e)

 

2. Lead name of the emp table (alias: m)

 

Confirm the connection relationship:

 

E. mgr = m. empno;

 

If an employee has no leadership, its content will not be displayed. In this case, an external connection is required, so that the table on one side can be displayed without leadership.

The left and right outer connections can be separated, but the most important thing is to display the content to be displayed.

 

select e.ename, e.job, m.enamefrom emp e, emp m where e.mgr = m.empno(+);