oracle学习笔记(十一)：多表查询

---

1、员工表 emp 和部门表 dept 的笛卡尔积：select * from emp,dept;

笛卡尔积表 的列数 = 表1的列数 + 表2的列数（列数和）；

笛卡尔积表 的行数 = 表1的行数 x 表2的行数（行数积）；

笛卡尔积表中有些数据是不符合要求的（没有意义）；

 

2、内连接 查询：只能查询出符合条件的记录，不符合条件的记录无法查出；

内连接又分为 等值连接 和 非等值连接；

等值连接只能使用 = 号；

例：查询员工的 编号、姓名、部门名、部门编号：（部门表中 部门编号为 40 的数据无法查出）

SQL> select emp.empno,emp.ename,dept.dname,dept.deptno
  2  from emp,dept
  3  where emp.deptno = dept.deptno;

EMPNO ENAME   DNAME       DEPTNO
----- ------- ----------- ------
 7782 CLARK   ACCOUNTING      10
 7839 KING    ACCOUNTING      10
 7934 MILLER  ACCOUNTING      10
 7566 JONES   RESEARCH        20
 7902 FORD    RESEARCH        20
 7876 ADAMS   RESEARCH        20
 7369 SMITH   RESEARCH        20
 7788 SCOTT   RESEARCH        20
 7521 WARD    SALES           30
 7844 TURNER  SALES           30
 7499 ALLEN   SALES           30
 7900 JAMES   SALES           30
 7698 BLAKE   SALES           30
 7654 MARTIN  SALES           30

已选择14行。

非等值连接 不能使用 = 号，其他符号都可以，比如 >=、<=、<>、between and 等；

例：查询员工的 编号、姓名、月薪，和工资级别：（使用 表的别名）

SQL> select e.empno,e.ename,e.sal,s.grade
  2  from emp e,salgrade s
  3  where e.sal between s.losal and s.hisal;

EMPNO ENAME     SAL      GRADE
----- ------- ----- ----------
 7369 SMITH     800          1
 7900 JAMES     950          1
 7876 ADAMS    1100          1
 7521 WARD     1250          2
 7654 MARTIN   1250          2
 7934 MILLER   1300          2
 7844 TURNER   1500          3
 7499 ALLEN    1600          3
 7782 CLARK    2450          4
 7698 BLAKE    2850          4
 7566 JONES    2975          4
 7788 SCOTT    3000          4
 7902 FORD     3000          4
 7839 KING     5000          5

已选择14行。

 

3、外连接 查询：既能查询出符合条件的数据，也能根据一方强行将另一方的数据查出来；

外连接 又分为 左外连接 和 右外连接；

在 oracle 中，将 (+) 号出现在 = 号右边，叫做 左外连接；将 (+) 号出现在 = 号左边，叫做 右外连接；

(+) 号出现在数据少的一方；（emp 表中 deptno 只有3个，而 dept 表中 deptno 有4个）

左外或者右外，本质是一样的，只是 (+) 号的位置不一样，且 (+) 号只能出现一次；以上语法是 oracle 专用语法；

（sql 标准语法是 left outer join 和 right outer join）

例：使用 左外连接 按部门号 统计各部门员工人数，要求显示 部门号、部门名、人数；

SQL> select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"
  2  from dept,emp
  3  where dept.deptno = emp.deptno(+)
  4  group by dept.deptno,dept.dname;

    部门号 部门名                             人数
---------- ---------------------------- ----------
        10 ACCOUNTING                            3
        40 OPERATIONS                            0
        20 RESEARCH                              5
        30 SALES                                 6

例：使用 右外连接 按部门号 统计各部门员工人数，要求显示 部门号、部门名、人数；

SQL> select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"
  2  from dept,emp
  3  where emp.deptno(+) = dept.deptno
  4  group by dept.deptno,dept.dname;

    部门号 部门名                             人数
---------- ---------------------------- ----------
        10 ACCOUNTING                            3
        40 OPERATIONS                            0
        20 RESEARCH                              5
        30 SALES                                 6

从上面可以看出，40 号部门没有员工，但是也显示出来了；如果使用 内连接，就无法查询出 40 号部门，如下所示：

SQL> select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"
  2  from dept,emp
  3  where emp.deptno = dept.deptno
  4  group by dept.deptno,dept.dname;

    部门号 部门名                             人数
---------- ---------------------------- ----------
        10 ACCOUNTING                            3
        20 RESEARCH                              5
        30 SALES                                 6

 

4、自连接：自己 和 自己连接，因为只有一张表，所以需要 虚拟出两张表；

例：查询显示 " xxx 的上级是 xxx " 这种格式；

查询某个员工 及其上级，都可以在 emp 表中查到；

虚拟出两张表，一张员工表 users，一张上级表 boss；

SQL> select users.ename || ' 的上级是 ' ||boss.ename
  2  from emp users,emp boss
  3  where users.mgr = boss.empno;

USERS.ENAME||'的上级是'||BOSS.ENAME
--------------------------------------------------------------------
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行。

结果只有13条数据，因为查询条件是等值连接，属于内连接，只能查询到满足条件的数据；KING没有上级，所有查询不到；

如果想查询 KING 的数据可以通过 外连接 实现：

SQL> select users.ename || ' 的上级是 ' ||boss.ename
  2  from emp users,emp boss
  3  where users.mgr = boss.empno(+);

USERS.ENAME||'的上级是'||BOSS.ENAME
--------------------------------------------------------------------
FORD 的上级是 JONES
SCOTT 的上级是 JONES
JAMES 的上级是 BLAKE
TURNER 的上级是 BLAKE
MARTIN 的上级是 BLAKE
WARD 的上级是 BLAKE
ALLEN 的上级是 BLAKE
MILLER 的上级是 CLARK
ADAMS 的上级是 SCOTT
CLARK 的上级是 KING
BLAKE 的上级是 KING
JONES 的上级是 KING
SMITH 的上级是 FORD
KING 的上级是

已选择14行。