Oracle SQL之--多表查询基础用法

zhoujun.g

多表查询，顾名思义就是从多个表中查询你所要的数据。这里以scott用户下的emp表与dept表进行试验，两个表的结构如下：  SQL> select * from dept; DEPTNO DNAME LOC　　---------- -------------- -------------
　　10 ACCOUNTING NEW YORK
　　20 RESEARCH DALLAS
　　30 SALES CHICAGO
　　40 OPERATIONS BOSTON
　　SQL> select * from emp;
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7369 SMITH      CLERK           7902 17-DEC-80      800                    20
　　7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
　　7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
　　7566 JONES      MANAGER         7839 02-APR-81       2975                    20
　　7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
　　7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
　　7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
　　7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
　　7839 KING       PRESIDENT            17-NOV-81      5000                    10
　　7844 TURNER     SALESMAN        7698 08-SEP-81       500          0         30
　　7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7900 JAMES      CLERK           7698 03-DEC-81        950                    30
　　7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
　　7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
　　14 rows selected.
　　使用连接可在多个表中查询数据：
　　基本语句如下：
　　SELECT table1.column, table2.column from  table1, table2 where  table1.column1 = table2.column2
　　连接的类型分成很多种：Equijoins（等值连接） Non-equijoin（非等值连接） Outer-join（外连接） Self-join（自连接） Cross joins（交叉连接） Natural joins（自然连接）
　　1、Equijoins
　　Equijoins（等值连接）--利用emp表中的deptno与dept表中的deptno进行等值连接。
　　SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept where
　　2  emp.deptno=dept.deptno;
　　EMPNO ENAME          DEPTNO     DEPTNO DNAME
　　---------- ---------- ---------- ---------- --------------
　　7369 SMITH              20         20 RESEARCH
　　7499 ALLEN              30         30 SALES
　　7521 WARD               30         30 SALES
　　7566 JONES              20         20 RESEARCH
　　7654 MARTIN             30         30 SALES
　　7698 BLAKE              30         30 SALES
　　7782 CLARK              10         10 ACCOUNTING
　　7788 SCOTT              20         20 RESEARCH
　　7839 KING               10         10 ACCOUNTING
　　7844 TURNER             30         30 SALES
　　7876 ADAMS              20         20 RESEARCH
　　EMPNO ENAME          DEPTNO     DEPTNO DNAME
　　---------- ---------- ---------- ---------- --------------
　　7900 JAMES              30         30 SALES
　　7902 FORD               20         20 RESEARCH
　　7934 MILLER             10         10 ACCOUNTING
　　14 rows selected.
　　2、Non-equijoin
　　Non-equijoin（非等值连接）--新建一个名为job_grades的表，并往其中添加数据：
　　SQL> select * from job_grades
　　2  ;
　　GRA   LOWEST_SAL HIGHEST_SAL
　　----- ---------- -----------
　　A            500        1499
　　B           1500        2499
　　C           2500        3500
　　SQL> select emp.ename,emp.sal,job_grades.gra from emp,job_grades where emp.sal between
　　2  job_grades.lowest_sal and job_grades.highest_sal;
　　ENAME             SAL GRA
　　---------- ---------- -----
　　SMITH             800        A
　　WARD              1250       A
　　MARTIN            1250       A
　　ADAMS             1100       A
　　JAMES             950        A
　　MILLER            1300       A
　　ALLEN             1600       B
　　CLARK             2450       B
　　TURNER            1500       B
　　JONES             2975       C
　　BLAKE             2850       C
　　ENAME             SAL GRA
　　---------- ---------- -----
　　SCOTT             3000      C
　　FORD              3000      C
　　13 rows selected.
　　3、Self-join
　　Self-join（自连接）---是sql语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
　　SQL> select worker.ename || ' works for ' || manager.ename from emp worker,emp manager
　　2  where worker.mgr = manager.empno;
　　WORKER.ENAME||'WORKSFOR'||MANAG
　　-------------------------------
　　SMITH works for FORD
　　ALLEN works for BLAKE
　　WARD works for BLAKE
　　JONES works for KING
　　MARTIN works for BLAKE
　　BLAKE works for KING
　　CLARK works for KING
　　SCOTT works for JONES
　　TURNER works for BLAKE
　　ADAMS works for SCOTT
　　JAMES works for BLAKE
　　WORKER.ENAME||'WORKSFOR'||MANAG
　　-------------------------------
　　FORD works for JONES
　　MILLER works for CLARK
　　13 rows selected.
　　4、Natural joins
　　Natural joins（自然连接）---是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。
　　SQL> select deptno,ename,dname,loc from emp natural join dept;
　　DEPTNO ENAME      DNAME          LOC
　　---------- ---------- -------------- -------------
　　20 SMITH      RESEARCH       DALLAS
　　30 ALLEN      SALES          CHICAGO
　　30 WARD       SALES          CHICAGO
　　20 JONES      RESEARCH       DALLAS
　　30 MARTIN     SALES          CHICAGO
　　30 BLAKE      SALES          CHICAGO
　　10 CLARK      ACCOUNTING     NEW YORK
　　20 SCOTT      RESEARCH       DALLAS
　　10 KING       ACCOUNTING     NEW YORK
　　30 TURNER     SALES          CHICAGO
　　20 ADAMS      RESEARCH       DALLAS
　　DEPTNO ENAME      DNAME          LOC
　　---------- ---------- -------------- -------------
　　30 JAMES      SALES          CHICAGO
　　20 FORD       RESEARCH       DALLAS
　　10 MILLER     ACCOUNTING     NEW YORK
　　14 rows selected.
　　4.1  Using字句
　　在natural join字句创建等值连接时，可以使用using字句指定等值连接中需要用到的列。使用using可以在有多个列满足条件时进行选择。
　　SQL> select e.empno,e.ename,d.loc from emp e join dept d using(deptno);
　　EMPNO ENAME      LOC
　　---------- ---------- -------------
　　7369 SMITH      DALLAS
　　7499 ALLEN      CHICAGO
　　7521 WARD       CHICAGO
　　7566 JONES      DALLAS
　　7654 MARTIN     CHICAGO
　　7698 BLAKE      CHICAGO
　　7782 CLARK      NEW YORK
　　7788 SCOTT      DALLAS
　　7839 KING       NEW YORK
　　7844 TURNER     CHICAGO
　　7876 ADAMS      DALLAS
　　EMPNO ENAME      LOC
　　---------- ---------- -------------
　　7900 JAMES      CHICAGO
　　7902 FORD       DALLAS
　　7934 MILLER     NEW YORK
　　14 rows selected.
　　4.2 ON字句
　　自然连接中是以具有相同名字的列作为连接条件的。我们可以使用ON字句指定额外的连接条件，这个连接条件时与其他条件分开的。
　　SQL> select emp.empno,emp.ename,emp.deptno,dept.deptno,dept.loc from emp join dept
　　2  on (emp.deptno = dept.deptno);
　　EMPNO ENAME          DEPTNO     DEPTNO LOC
　　---------- ---------- ---------- ---------- -------------
　　7369 SMITH              20         20 DALLAS
　　7499 ALLEN              30         30 CHICAGO
　　7521 WARD               30         30 CHICAGO
　　7566 JONES              20         20 DALLAS
　　7654 MARTIN             30         30 CHICAGO
　　7698 BLAKE              30         30 CHICAGO
　　7782 CLARK              10         10 NEW YORK
　　7788 SCOTT              20         20 DALLAS
　　7839 KING               10         10 NEW YORK
　　7844 TURNER             30         30 CHICAGO
　　7876 ADAMS              20         20 DALLAS
　　EMPNO ENAME          DEPTNO     DEPTNO LOC
　　---------- ---------- ---------- ---------- -------------
　　7900 JAMES              30         30 CHICAGO
　　7902 FORD               20         20 DALLAS
　　7934 MILLER             10         10 NEW YORK
　　14 rows selected.
　　5、Outer-join
　　outer-join（外连接）---使用外连接可以查询不满足连接条件的数据。外连接的符号是（+）。外连接还可以分为左外连接、右外连接。所谓左外连接，就是返回左表中不满足条件的行。右外连接，则是返回右表中不满足条件的行。
　　上面的dept表中有一个40号的deptno，但是emp表中却没有这个dept的数据。故可以用来做外连接的实验：
　　5.1 右外连接：
　　下面两条SQL语句的效果是一样的：
　　SQL> select e.ename,e.deptno,d.dname from emp e right outer join dept d on (e.deptno = d.deptno);
　　SQL> select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno(+) = d.deptno;
　　ENAME          DEPTNO DNAME
　　---------- ---------- --------------
　　SMITH              20 RESEARCH
　　ALLEN              30 SALES
　　WARD               30 SALES
　　JONES              20 RESEARCH
　　MARTIN             30 SALES
　　BLAKE              30 SALES
　　CLARK              10 ACCOUNTING
　　SCOTT              20 RESEARCH
　　KING               10 ACCOUNTING
　　TURNER             30 SALES
　　ADAMS              20 RESEARCH
　　ENAME          DEPTNO DNAME
　　---------- ---------- --------------
　　JAMES              30 SALES
　　FORD               20 RESEARCH
　　MILLER             10 ACCOUNTING
　　OPERATIONS
　　5.2 左外连接
　　左外连接的sql语句与右外连接的sql语句相似：
　　SQL> select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno(+);
　　SQL> select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno = d.deptno);
　　6、Cross joins（交叉连接）
　　使用corss join字句是连接表产生叉集。叉集合笛卡尔集是相同的
　　SQL> select ename,dname from emp cross join dept;
　　ENAME      DNAME
　　---------- --------------
　　SMITH      ACCOUNTING
　　ALLEN      ACCOUNTING
　　WARD       ACCOUNTING
　　JONES      ACCOUNTING
　　MARTIN     ACCOUNTING
　　BLAKE      ACCOUNTING
　　CLARK      ACCOUNTING
　　SCOTT      ACCOUNTING
　　KING       ACCOUNTING
　　TURNER     ACCOUNTING
　　ADAMS      ACCOUNTING
　　ENAME      DNAME
　　---------- --------------
　　JAMES      ACCOUNTING
　　FORD       ACCOUNTING
　　MILLER     ACCOUNTING
　　SMITH      RESEARCH
　　ALLEN      RESEARCH
　　WARD       RESEARCH
　　JONES      RESEARCH
　　MARTIN     RESEARCH
　　BLAKE      RESEARCH
　　CLARK      RESEARCH
　　SCOTT      RESEARCH
　　ENAME      DNAME
　　---------- --------------
　　KING       RESEARCH
　　TURNER     RESEARCH
　　ADAMS      RESEARCH
　　JAMES      RESEARCH
　　FORD       RESEARCH
　　MILLER     RESEARCH
　　SMITH      SALES
　　ALLEN      SALES
　　WARD       SALES
　　JONES      SALES
　　MARTIN     SALES
　　ENAME      DNAME
　　---------- --------------
　　BLAKE      SALES
　　CLARK      SALES
　　SCOTT      SALES
　　KING       SALES
　　TURNER     SALES
　　ADAMS      SALES
　　JAMES      SALES
　　FORD       SALES
　　MILLER     SALES
　　SMITH      OPERATIONS
　　ALLEN      OPERATIONS
　　ENAME      DNAME
　　---------- --------------
　　WARD       OPERATIONS
　　JONES      OPERATIONS
　　MARTIN     OPERATIONS
　　BLAKE      OPERATIONS
　　CLARK      OPERATIONS
　　SCOTT      OPERATIONS
　　KING       OPERATIONS
　　TURNER     OPERATIONS
　　ADAMS      OPERATIONS
　　JAMES      OPERATIONS
　　FORD       OPERATIONS
　　ENAME      DNAME
　　---------- --------------
　　MILLER     OPERATIONS
　　56 rows selected.
　　以上是个人学习之后做的笔记，刚刚入门，还有很多欠缺的地方。但是还是一点态度吧，大家一起学习进步！