多表连接

1.1 多表连接查询的概念

• 由于数据库中很多数据被分散到多个数据库表中。在查询数据时就经常出现要查的数据来自多个表中，此时就必须采用多表连接查询。多表连接查询是数据库查询中常见的查询方式。
• 多表连接查询分为内连接和外连接。

1.2 内连接的概念

• 内连接就是两张表处于同等地位，只显示两张表能够匹配上的记录。
• 内连接主要方式：
  • 等值连接，连接条件为两个或者多个表的字段相等，大多数多表连接查询是等值连接。
  • 非等值连接，连接条件不是两个或者多个表的字段相等，而是其他运算符(>,<,>=,<=,<>,between and等)。
  • 自连接,用别名将一个表看成两张表进行多表连接。

1.3 外连接的概念

• 外连接有主表和副表的区别，两张表中有一张表是主表，另一张表是副表，主要查询主表中的数据(主表的数据一定会显示)，匹配查询副表，当副表中的数据没有和主表中的数据匹配时，副表自动模拟出null与之匹配。
• 外连接主要方式：
  • 左外连接，左边的表是主表，主表的意思是左边的表数据不会为空，但是可能会重复，查出的数据可能比主表的行数还要多。
  • 右外连接，右边的表是主表。
  • 全外连接，两边都是主表(基本用不到)。

1.4 案例说明

• 接下来以oracle数据库中scott用户下emp(员工表)和dept(部门表)以及salgrade(薪水等级表)为例，解析一下多表连接查询对应的SQL语句。
• 其中emp（员工表）表结构和表数据如下：

SQL> desc emp; 
 Name                       Null?    Type
 ----------------------------------------- -------- --------------

 EMPNO                      NOT NULL NUMBER(4)     -- 员工编号
 ENAME                               VARCHAR2(10)  -- 员工姓名
 JOB                                 VARCHAR2(9)   -- 员工职位
 MGR                                 NUMBER(4)     -- 员工领导
 HIREDATE                            DATE          -- 入职日期
 SAL                                 NUMBER(7,2)   -- 员工薪水
 COMM                                NUMBER(7,2)   -- 员工提成
 DEPTNO                              NUMBER(2)     -- 部门编号

• 其中dept(部门表)表结构和表数据如下：

SQL> desc dept;
 Name                       Null?    Type
 ----------------------------------------- -------- --------------

 DEPTNO                     NOT NULL NUMBER(2)      -- 部门编号
 DNAME                               VARCHAR2(14)   -- 部门名称
 LOC                                 VARCHAR2(13)   -- 办公地点

• 其中salgrade(薪水等级表)表结构和表数据如下：

SQL> desc salgrade;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------

 GRADE                               NUMBER        -- 等级编号
 LOSAL                               NUMBER        -- 最低薪水
 HISAL                               NUMBER        -- 最高薪水

1.4.1 交叉查询

• 交叉查询没有任何实际意义，得到的是2张表的笛卡尔积，也就是让表1的每一项都组合表2的每一项：笛卡尔乘积现象(没有条件限制)，结果条数是2张表记录条数的乘积。

select e.ename,d.dname from emp e,dept d; 

• 使用第一张表的第一条记录和第二张表的每一条记录都匹配，然后第一张表的每一条记录也一样去找表2的每一条记录进行匹配。
• 如果不用别名，若两张表存在一模一样的字段时，会出现混淆。
      

1.4.2 内连接查询

（1）如何避免笛卡尔积现象

• 加条件进行过滤。避免了笛卡尔积现象，会减少记录的匹配次数吗？不会，次数还是56次。只不过显示的是有效记录。

（2）内连接查询

• 内连接之等值连接：最大特点是：条件是等量关系。
• 案例：查询每个员工的部门名称，要求显示员工名和部门名。

-- 语法太老，基本不用了
SQL92: select e.ename,d.dname 
         from emp e,dept d 
           where e.deptno=d.deptno;
-- 常用，inner可以省略，带着inner目的是可读性好一些。
SQL99: select e.ename,d.dname 
         from emp e inner join dept d 
           on e.deptno=d.deptno;

• 语法：… A join B on 连接条件 where 查询条件；

• 表连接的条件和数据进行过滤的where条件进行分离。语法结构更清晰。
  

• 内连接之非等值连接的最大特点是：连接条件中的关系是非等量关系。

• 案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select e.ename, e.sal, s.grade 
  from emp e join salgrade s 
    on e.sal between s.losal and s.hisal;

• 自连接：最大的特点是：一张表看做两张表。自己连接自己。
• 案例：找出每个员工的上级领导，要求显示员工名和对应的领导名，希望mgr显示的是领导名，而不是编号。
• 其实都是同一张表，员工的领导编号=领导的员工编号。(核心点,在于找这个等量关系)。

select a.ename as ‘员工名’, b.ename as ‘领导名’ 
  from emp a inner join emp b 
    on a.mgr=b.empno;

1.4.3 外连接查询

• 外连接最重要的特点是：主表的数据无条件的全部查询出来。
• 左连接有右连接的写法，右连接也会有对应的左连接的写法。
• 案列：找出每个员工的上级领导？(所有员工必须全部查询出来。)

-- 外连接(左)：outer可以省略。
select a.ename ‘员工’, b.ename ‘领导’ 
  from emp a left outer join emp b 
    on a.mgr=b.empno;
-- 外连接(右)：
select a.ename ‘员工’, b.ename ‘领导’ 
  from emp b right outer join emp a 
    on a.mgr=b.empno;

更多精彩欢迎关注微信公众号《格子衫007》！