ORACLE-多表关联

1、交叉连接（cross join）

CROSSJOIN连接用于生成两张表的笛卡尔集。

在sql中cross join的使用：

1）返回的记录数为两个表的记录数乘积。

2）将A表的所有行分别与B表的所有行进行连接。

结果表： 列= 原表列数相加

行= 原表行数相乘

SQL> select *from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ------------------- ----- ----------- --------- --------- ------

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20

 7839 KING      PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10

15 rows selected

 

 

SQL> select *from dept;

DEPTNO DNAME          LOC

-------------------- -------------

    10 ACCOUNTING     NEW YORK

    20 RESEARCH       DALLAS

    30 SALES          CHICAGO

    40 OPERATIONS     BOSTON

 

SQL> select *from emp a cross join dept;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC

----- ------------------- ----- ----------- --------- --------- ------ ------ ---------------------------

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20     10 ACCOUNTING     NEW YORK

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30     10 ACCOUNTING     NEW YORK

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30     10 ACCOUNTING     NEW YORK

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20     10 ACCOUNTING     NEW YORK

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30     10 ACCOUNTING     NEW YORK

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30     10 ACCOUNTING     NEW YORK

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10     10 ACCOUNTING     NEW YORK

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20     10 ACCOUNTING     NEW YORK

 7839 KING      PRESIDENT       1981/11/17    5000.00               10     10 ACCOUNTING     NEW YORK

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30     10 ACCOUNTING     NEW YORK

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20     10 ACCOUNTING     NEW YORK

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30     10 ACCOUNTING     NEW YORK

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20     10 ACCOUNTING     NEW YORK

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10     10 ACCOUNTING     NEW YORK

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10     10 ACCOUNTING     NEW YORK

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20     20 RESEARCH       DALLAS

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30     20 RESEARCH       DALLAS

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30     20 RESEARCH       DALLAS

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20     20 RESEARCH       DALLAS

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30     20 RESEARCH       DALLAS

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC

----- ------------------- ----- ----------- --------- --------- ------ ------ ---------------------------

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30     20 RESEARCH       DALLAS

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10     20 RESEARCH       DALLAS

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20     20 RESEARCH       DALLAS

 7839 KING      PRESIDENT       1981/11/17    5000.00               10     20 RESEARCH       DALLAS

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30     20 RESEARCH       DALLAS

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20     20 RESEARCH       DALLAS

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30     20 RESEARCH       DALLAS

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20     20 RESEARCH       DALLAS

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10     20 RESEARCH       DALLAS

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10     20 RESEARCH       DALLAS

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20     30 SALES          CHICAGO

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30     30 SALES          CHICAGO

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30     30 SALES          CHICAGO

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20     30 SALES          CHICAGO

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30     30 SALES          CHICAGO

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30     30 SALES          CHICAGO

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10     30 SALES          CHICAGO

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20     30 SALES          CHICAGO

 7839 KING      PRESIDENT       1981/11/17    5000.00               10     30 SALES          CHICAGO

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30     30 SALES          CHICAGO

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20     30 SALES          CHICAGO

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO DEPTNO DNAME          LOC

----- ------------------- ----- ----------- --------- --------- ------ ------ ---------------------------

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30     30 SALES          CHICAGO

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20     30 SALES          CHICAGO

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10     30 SALES          CHICAGO

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10     30 SALES          CHICAGO

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20     40 OPERATIONS     BOSTON

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30     40 OPERATIONS     BOSTON

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30     40 OPERATIONS     BOSTON

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20     40 OPERATIONS     BOSTON

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30     40 OPERATIONS     BOSTON

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30     40 OPERATIONS     BOSTON

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10     40 OPERATIONS     BOSTON

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20     40 OPERATIONS     BOSTON

 7839 KING      PRESIDENT       1981/11/17    5000.00               10     40 OPERATIONS     BOSTON

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30     40 OPERATIONS     BOSTON

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20     40 OPERATIONS     BOSTON

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30     40 OPERATIONS     BOSTON

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20     40 OPERATIONS     BOSTON

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10     40 OPERATIONS     BOSTON

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10     40 OPERATIONS     BOSTON

60 rows selected

 

select * from emp a cross join dept;的运行机制是首先将a表中的所有记录逐条查出，然后与dept表中的每一行分别进行组合。

 

 

2、内连接--inner join（join）

 

SQL> select *from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ------------------- ----- ----------- --------- --------- ------

 7369 SMITH     CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN     SALESMAN   7698 1981/2/20     1760.00   300.00     30

 7521 WARD      SALESMAN   7698 1981/2/22     1250.00   500.00     30

 7566 JONES     MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN    SALESMAN   7698 1981/9/28     1250.00  1400.00     30

 7698 BLAKE     MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK     MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT     ANALYST    7566 1987/4/19     3000.00               20

 7839 KING      PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER    SALESMAN   7698 1981/9/8      1500.00      0.00    30

 7876 ADAMS     CLERK      7788 1987/5/23     1100.00               20

 7900 JAMES     CLERK      7698 1981/12/3      950.00               30

 7902 FORD      ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER    CLERK      7782 1982/1/23     1300.00               10

 8888 测试数据  旺旺       1888 1982/1/23     1300.00               10

15 rows selected

 

SQL> select *from dept;

DEPTNO DNAME          LOC

-------------------- -------------

    10 ACCOUNTING     NEW YORK

    20 RESEARCH       DALLAS

    30 SALES          CHICAGO

    40 OPERATIONS     BOSTON

 

SQL> selecta.empno,a.ename,b.deptno,b.dname from emp a inner join dept b ona.deptno=b.deptno;

EMPNO ENAME      DEPTNO DNAME

----- ---------------- --------------

 7782 CLARK          10 ACCOUNTING

 8888 测试数据      10 ACCOUNTING

 7839 KING           10 ACCOUNTING

 7934 MILLER         10 ACCOUNTING

 7566 JONES          20 RESEARCH

 7369 SMITH          20 RESEARCH

 7788 SCOTT          20 RESEARCH

 7902 FORD           20 RESEARCH

 7876 ADAMS          20 RESEARCH

 7844 TURNER         30 SALES

 7499 ALLEN          30 SALES

 7900 JAMES          30 SALES

 7521 WARD           30 SALES

 7654 MARTIN         30 SALES

 7698 BLAKE          30 SALES

15 rows selected

 

 

 

3、外连接--outer join（左连接、右连接、全连接）

 

1）左连接（left join）

 

2）右连接（right join）

 

3）全连接（full join）

 

 

 

 

 

 

 

语法：

内连接--inner join（join） 也可叫做等值连接

 

1）select a.empno,a.ename,b.deptno from empa,dept b where a.deptno=b.deptno and a.deptno>20;---等值连接

 

EMPNO ENAME      DEPTNO

----- ----------------

 7499 ALLEN          30

 7521 WARD           30

 7654 MARTIN         30

 7698 BLAKE          30

 7844 TURNER         30

 7900 JAMES          30

 

 

2）select a.empno,a.ename,b.deptno from empa（inner）joindept b on a.deptno=b.deptno and a.deptno>20;---内连接

 

EMPNO ENAME      DEPTNO

----- ----------------

 7499 ALLEN          30

 7521 WARD           30

 7654 MARTIN         30

 7698 BLAKE          30

 7844 TURNER         30

 7900 JAMES          30

 

 

外连接--outer join

3）左连接--left join

selecta.empno,a.ename,b.deptno from emp a left join dept b on a.deptno=b.deptno anda.deptno=20;

EMPNO ENAME      DEPTNO

----- ----------------

 7369 SMITH          20

 7499 ALLEN     

 7521 WARD      

 7566 JONES          20

 7654 MARTIN    

 7698 BLAKE     

 7782 CLARK     

 7788 SCOTT          20

 7839 KING      

 7844 TURNER    

 7876 ADAMS          20

 7900 JAMES     

 7902 FORD           20

 7934 MILLER    

 8888 测试数据  

 

 

selecta.empno,a.ename,b.deptno from emp a left join dept b on a.deptno=b.deptno;

等价于

selecta.empno,a.ename,b.deptno from emp a , dept b where a.deptno=b.deptno(+);

EMPNO ENAME      DEPTNO

----- ----------------

 7369 SMITH          20

 7499 ALLEN          30

 7521 WARD           30

 7566 JONES          20

 7654 MARTIN         30

 7698 BLAKE          30

 7782 CLARK          10

 7788 SCOTT          20

 7839 KING           10

 7844 TURNER         30

 7876 ADAMS          20

 7900 JAMES          30

 7902 FORD           20

 7934 MILLER         10

 8888 测试数据      10

 

 

 

4）右连接--right join

 

selecta.empno,a.ename,b.deptno from emp a right join dept b on a.deptno=b.deptno;

等价于

selecta.empno,a.ename,b.deptno from emp a , dept b where a.deptno(+)=b.deptno;

 

EMPNO ENAME      DEPTNO

----- ----------------

 7369 SMITH          20

 7499 ALLEN          30

 7521 WARD           30

 7566 JONES          20

 7654 MARTIN         30

 7698 BLAKE          30

 7782 CLARK          10

 7788 SCOTT          20

 7839 KING           10

 7844 TURNER         30

 7876 ADAMS          20

 7900 JAMES          30

 7902 FORD           20

 7934 MILLER         10

 8888 测试数据      10

                     40

 

 

5）全连接--full join

 

selecta.empno,a.ename,b.deptno from emp a full join dept b on a.deptno=b.deptno;