多表查询方式详解程序员

 笛卡尔(Descartes)乘积又叫直积。

         假设有两个集合X和Y，其笛卡尔积表示为X*Y，其积表示集合X中的每一个

         元素和集合Y中每一个元素的所有可能的有序对

         X*Y={(x,y)|x∈X∧y∈Y}

         例如：X={a,b}，Y={c,d,e}，则X*Y={(a,c),(a,d),(a,e),(b,c), (b,d),(b,e)}

多表查询

          当需要从多张表中获取数据(即多表联合查询)时，在不加任何限定条件的

          情况下就会产生笛卡尔积，下面使用scott的用户下的emp表和dept表

      进行联合查询：

SQL select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d ; 

EMPNO ENAME JOB DEPTNO DNAME 

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

 7369 SMITH CLERK 10 ACCOUNTING 

 7499 ALLEN SALESMAN 10 ACCOUNTING 

 7521 WARD SALESMAN 10 ACCOUNTING 

 7566 JONES MANAGER 10 ACCOUNTING 

 7654 MARTIN SALESMAN 10 ACCOUNTING 

 7698 BLAKE MANAGER 10 ACCOUNTING 

 7782 CLARK MANAGER 10 ACCOUNTING 

 7788 SCOTT ANALYST 10 ACCOUNTING 

 7839 KING PRESIDENT 10 ACCOUNTING 

 7844 TURNER SALESMAN 10 ACCOUNTING 

 7876 ADAMS CLERK 10 ACCOUNTING 

 7900 JAMES CLERK 10 ACCOUNTING 

 7902 FORD ANALYST 10 ACCOUNTING 

 7934 MILLER CLERK 10 ACCOUNTING 

 7369 SMITH CLERK 20 RESEARCH 

 7499 ALLEN SALESMAN 20 RESEARCH 

 7521 WARD SALESMAN 20 RESEARCH 

 7566 JONES MANAGER 20 RESEARCH 

 7654 MARTIN SALESMAN 20 RESEARCH 

 7698 BLAKE MANAGER 20 RESEARCH 

EMPNO ENAME JOB DEPTNO DNAME 

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

 7782 CLARK MANAGER 20 RESEARCH 

 7788 SCOTT ANALYST 20 RESEARCH 

 7839 KING PRESIDENT 20 RESEARCH 

 7844 TURNER SALESMAN 20 RESEARCH 

 7876 ADAMS CLERK 20 RESEARCH 

 7900 JAMES CLERK 20 RESEARCH 

 7902 FORD ANALYST 20 RESEARCH 

 7934 MILLER CLERK 20 RESEARCH 

 7369 SMITH CLERK 30 SALES 

 7499 ALLEN SALESMAN 30 SALES 

 7521 WARD SALESMAN 30 SALES 

 7566 JONES MANAGER 30 SALES 

 7654 MARTIN SALESMAN 30 SALES 

 7698 BLAKE MANAGER 30 SALES 

 7782 CLARK MANAGER 30 SALES 

 7788 SCOTT ANALYST 30 SALES 

 7839 KING PRESIDENT 30 SALES 

 7844 TURNER SALESMAN 30 SALES 

 7876 ADAMS CLERK 30 SALES 

 7900 JAMES CLERK 30 SALES 

 7902 FORD ANALYST 30 SALES 

EMPNO ENAME JOB DEPTNO DNAME 

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

 7934 MILLER CLERK 30 SALES 

 7369 SMITH CLERK 40 OPERATIONS 

 7499 ALLEN SALESMAN 40 OPERATIONS 

 7521 WARD SALESMAN 40 OPERATIONS 

 7566 JONES MANAGER 40 OPERATIONS 

 7654 MARTIN SALESMAN 40 OPERATIONS 

 7698 BLAKE MANAGER 40 OPERATIONS 

 7782 CLARK MANAGER 40 OPERATIONS 

 7788 SCOTT ANALYST 40 OPERATIONS 

 7839 KING PRESIDENT 40 OPERATIONS 

 7844 TURNER SALESMAN 40 OPERATIONS 

 7876 ADAMS CLERK 40 OPERATIONS 

 7900 JAMES CLERK 40 OPERATIONS 

 7902 FORD ANALYST 40 OPERATIONS 

 7934 MILLER CLERK 40 OPERATIONS 

56 rows selected 

上面这个查询产生笛卡儿积。

 从查询结果中可以发现有许多重复项，这就是产生了笛卡尔积，可以通过在

   WHERE子句中增加合适的连接条件来消除笛卡尔积，以下几种方式消除笛卡尔积。

      1、Equijoin：等值连接

    2、Non-equijoin：不等值连接

      3、Outer join：外连接

    4、Self join：自连接

1.等值连接

SQL select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno; 

EMPNO ENAME JOB DEPTNO DNAME 

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

 7782 CLARK MANAGER 10 ACCOUNTING 

 7839 KING PRESIDENT 10 ACCOUNTING 

 7934 MILLER CLERK 10 ACCOUNTING 

 7566 JONES MANAGER 20 RESEARCH 

 7902 FORD ANALYST 20 RESEARCH 

 7876 ADAMS CLERK 20 RESEARCH 

 7369 SMITH CLERK 20 RESEARCH 

 7788 SCOTT ANALYST 20 RESEARCH 

 7521 WARD SALESMAN 30 SALES 

 7844 TURNER SALESMAN 30 SALES 

 7499 ALLEN SALESMAN 30 SALES 

 7900 JAMES CLERK 30 SALES 

 7698 BLAKE MANAGER 30 SALES 

 7654 MARTIN SALESMAN 30 SALES 

14 rows selected

2.不等值连接

SQL select e.empno, e.ename, e.sal, s.grade 

 2 from emp e, SALGRADE s 

 3 where e.sal = s.losal and e.sal = s.hisal 

 4 ; 

EMPNO ENAME SAL GRADE 

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

 7369 SMITH 800.00 1 

 7900 JAMES 1045.00 1 

 7876 ADAMS 1100.00 1 

 7521 WARD 1250.00 2 

 7654 MARTIN 1250.00 2 

 7934 MILLER 1300.00 2 

 7844 TURNER 1500.00 3 

 7499 ALLEN 1600.00 3 

 7782 CLARK 2450.00 4 

 7698 BLAKE 2850.00 4 

 7566 JONES 2975.00 4 

 7788 SCOTT 3000.00 4 

 7902 FORD 3000.00 4 

 7839 KING 5000.00 5 

14 rows selected 

 

3.外连接

外连接分为三种类型。

1）左外连接

方式一：使用left outer join

SQL select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno 

 2 from emp e left outer join dept d 

 3 on e.deptno=d.deptno 

 4 ; 

EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO 

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

 7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK 10 

 7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK 10 

 7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK 10 

 7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS 20 

 7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS 20 

 7369 SMITH CLERK 800.00 20 RESEARCH DALLAS 20 

 7900 JAMES CLERK 1045.00 30 SALES CHICAGO 30 

 7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO 30 

 7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO 30 

 7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO 30 

 7521 WARD SALESMAN 1250.00 30 SALES CHICAGO 30 

 7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO 30 

14 rows selected

方式二：
用外连接符(+)进行连接

SQL select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno 

 2 from emp e,dept d 

 3 where e.deptno=d.deptno(+) 

 4 ; 

EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO 

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

 7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK 10 

 7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK 10 

 7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK 10 

 7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS 20 

 7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS 20 

 7369 SMITH CLERK 800.00 20 RESEARCH DALLAS 20 

 7900 JAMES CLERK 1045.00 30 SALES CHICAGO 30 

 7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO 30 

 7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO 30 

 7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO 30 

 7521 WARD SALESMAN 1250.00 30 SALES CHICAGO 30 

 7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO 30 

14 rows selected

2）右外连接

右外连接同理

3）全外连接

连接A和B两张表，返回A表和B表中所有的数据进 行的连接结果，可以在FROM语句中使用全外连接语法 FULL [OUTER] JOIN对两张表进行连接

SQL select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno 

 2 from emp e 

 3 full outer join 

 4 dept d 

 5 on e.deptno=d.deptno; 

EMPNO ENAME JOB SAL DEPTNO DNAME LOC DEPTNO 

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

 7369 SMITH CLERK 800.00 20 RESEARCH DALLAS 20 

 7499 ALLEN SALESMAN 1600.00 30 SALES CHICAGO 30 

 7521 WARD SALESMAN 1250.00 30 SALES CHICAGO 30 

 7566 JONES MANAGER 2975.00 20 RESEARCH DALLAS 20 

 7654 MARTIN SALESMAN 1250.00 30 SALES CHICAGO 30 

 7698 BLAKE MANAGER 2850.00 30 SALES CHICAGO 30 

 7782 CLARK MANAGER 2450.00 10 ACCOUNTING NEW YORK 10 

 7788 SCOTT ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7839 KING PRESIDENT 5000.00 10 ACCOUNTING NEW YORK 10 

 7844 TURNER SALESMAN 1500.00 30 SALES CHICAGO 30 

 7876 ADAMS CLERK 1100.00 20 RESEARCH DALLAS 20 

 7900 JAMES CLERK 1045.00 30 SALES CHICAGO 30 

 7902 FORD ANALYST 3000.00 20 RESEARCH DALLAS 20 

 7934 MILLER CLERK 1300.00 10 ACCOUNTING NEW YORK 10 

 OPERATIONS BOSTON 40 

15 rows selected

4.自连接

表A和自己进行连接操作，此时要为表起个别名，通过为表起别名可将同一张表视为多张表

SQL select e1.ename || 的老板是:  || e2.ename 

 2 from emp e1,emp e2 

 3 where e1.mgr=e2.empno; 

E1.ENAME||的老板是:||E2.ENAM 

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

FORD的老板是: JONES 

SCOTT的老板是: JONES 

TURNER的老板是: BLAKE 

ALLEN的老板是: BLAKE 

WARD的老板是: BLAKE 

JAMES的老板是: BLAKE 

MARTIN的老板是: BLAKE 

MILLER的老板是: CLARK 

ADAMS的老板是: SCOTT 

BLAKE的老板是: KING 

JONES的老板是: KING 

CLARK的老板是: KING 

SMITH的老板是: FORD 

13 rows selected

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/7252.html