Implementation of multi-table query in Oracle database

A multiple-table query means getting the appropriate data from multiple tables.

(1) Cartesian product "The join condition is invalid or omitted, all rows of the two tables are connected, and the combination of all rows is returned (N*M)"

Sql> Select E.ename,d.dname from emp e,dept d;//Invalid

Sql> Select E.ename,d.dname from emp e,dept D

2 where e.ename= ' SCOTT ';/effective

(2) Equivalent connection (inner connection or simple connection)

The column values for a two-table join condition must be equal, typically such a connection contains a primary key and a foreign key

Sql> Select E.ename,d.dname from emp e,dept D

2 where E.deptno=d.deptno

3 and E.ename= ' SCOTT ';

(3) connections with more than two tables

Sql> CREATE TABLE Manager

2 AS

3 Select Ename,deptno,sal,job

4 from EMP; Create manager table

Sql> Select E.empno,m.ename,m.deptno,d.loc

2 from EMP E,manager m,dept D

3 where M.deptno=d.deptno

4 and M.ename=e.ename

5 and E.job=upper (' manager ');

(4) Non-equivalence connection "Using other connection operators"

Sql> Select E.empno,e.ename,e.sal,s.grade

2 from EMP E,salgrade s

3 where E.sal

4 between S.losal and Hisal;