3.8 多表查询

  在employees表中，雇员的职位是通过job_id记录的，而这个职位的具体信息存放在jobs表里。对用户而言，往往并不清楚每个job_id的含义，而是希望能直接看到每个雇员详细的职位信息，这时候就需要将两个或两个以上的表进行关联查询，以获得需要的结果。

3.8.1 等值连接

  当需要的数据需从多个表中查询得到时，需要采用多表连接查询，其语法形式如下：

  1. SELECT  table1.column,  table2.column
  3. FROM  table1,  table2
  5. WHERE  table1.column1  =  table2.column2

  其中SELECT子句中可选择多个表的多个字段，FROM子句中表明字段来自多个表，而WHERE子句为连接条件，table1.column1=table2.column2即具体的连接条件。

  在多表连接查询的SELECT子句中，字段名前面用表名可以使语义清楚，并且加快数据库访问，提高性能；如果相同的字段名出现在多个表中，字段名必须加表名，否则会引起冲突。为了将N 个表连接在一起，至少需要N-1个连接条件。

  首先介绍最简单也最常用的等值连接，等值连接也常被称为简单连接。

  为了确定一个雇员的职位信息，需要比较employees表中的job_id字段和jobs表中的 job_id 字段的值，当两个表中job_id 字段的值相等时，才可连接出雇员的具体职位信息，完成该案例的等值连接SQL语句如下：

  1. SELECT employees.first_name, jobs.job_title, employees.salary
  3. FROM employees, jobs
  5. WHERE employees.job_id = jobs.job_id

  执行该SQL语句，部分显示结果如图3.13所示。

3.8 多表查询 - 图1


图3.13 等值连接

3.8.2 表别名

  为了提高性能、避免字段名冲突，在多表连接查询的SELECT子句中，应该以“表名.字段名”的方式命名。不过按照此规则编写，如果是在所选字段比较多的情况下，SELECT子句往往冗长，可以使用表别名解决这个问题。

  和给字段起别名一样，表别名就是给表起了另外一个名字。不过两者的不同点在于，字段别名通常是为了显示的字段标题易于理解而起的，而表别名是为了让SQL语句简单、易读。

  修改上面的SQL语句，给employees表起别名为e，给jobs表起别名为j，修改完的SQL语句如下：

  1. SELECT e.first_name, j.job_title, e.salary
  3. FROM employees e, jobs j
  5. WHERE e.job_id = j.job_id

  两段SQL语句相比，使用了表别名的看起来更简单、易读。

  在使用表别名时，要注意表别名最多可以有30个字符，不过相信没有哪个程序开发人员会写出这么长的表别名；表别名的作用范围在整个SELECT语句中，离开这个SELECT语句即无效。

3.8.3 多表等值连接

  前面在讲等值连接时，将employees表和jobs表通过job_id进行了等值连接。如果还想在对employees表进行查询时显示雇员的部门名称（department_name），而不是部门编号（department_id），该怎么操作呢？

  解决方法就是使用多个连接条件，进行多表的等值连接，具体SQL语句如下：

  1. SELECT e.first_name, j.job_title,d.department_name, e.salary
  3. FROM employees e, jobs j,departments d
  5. WHERE e.job_id = j.job_id AND e.department_id = d.department_id

  在SQL语句的WHERE子句里，用AND将两个连接条件结合起来。执行该SQL语句，部分显示结果如图3.14所示。

3.8 多表查询 - 图2


图3.14 多表等值连接

  分析该多表连接，其核心表是employees表，分别通过job_id和jobs表关联连接（其中jobs表中的job_id字段是主键，employees表中的job_id字段是外键），通过department_id和departments表关联连接（其中departments表中的department_id字段是主键，employees表中的department_id字段是外键）。

  如果还需要在对employees表进行查询时显示雇员所在的城市，该如何实现呢？通过分析，employees表中没有和城市相关联的字段，但和employees表关联的departments表中有所在地编号（location_id），而通过location_id可关联到locations表，该表中有城市的信息。所以，可以继续在departments表上进行多表等值连接，其SQL语句如下：

  1. SELECT e.first_name, j.job_title,d.department_name, l.city, e.salary
  3. FROM employees e, jobs j,departments d,locations l 
  5. WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND d.location_id = l.location_id

  为了更好地介绍多表连接，图3.15显示了四个表之间的关系。

3.8 多表查询 - 图3


图3.15 多表连接示意图

3.8.4 自然连接、USING子句和ON子句

  如果两个表中，有相同的字段名且字段的数据类型相同，则可以使用Oracle的自然连接实现两表之间的等值连接，自然连接是一种特殊的等值连接。

  在第3.8.2节中，实现employees表和jobs表的等值连接的SQL语句如下：

  1. SELECT e.first_name, j.job_title, e.salary
  3. FROM employees e, jobs j
  5. WHERE e.job_id = j.job_id

  这两个表都用相同的字段名job_id，且这两个字段的数据数型都是VARCHAR2类型，满足自然连接的条件。可以将上面的SQL语句调整如下，用自然连接（NATURAL JOIN）的方式完成等值连接。

  1. SELECT e.first_name, j.job_title, e.salary
  3. FROM employees e 
  5. NATURAL JOIN jobs j

  在使用自然连接的时候需要注意，如果两个表中的字段名相同，但数据类型不同，用自然连接会返回一个错误。

  上面采用NATURAL JOIN关键字实现了自然连接，也可以不使用自然连接，而使用USING子句明确指定连接字段，其SQL语句如下：

  1. SELECT e.first_name, j.job_title, e.salary
  3. FROM employees e JOIN jobs j
  5. USING(job_id)

  另外有一点需要注意的是，不管是自然连接还是使用USING子句，在使用连接字段时，都不能在前面加上表的前缀，因为此时这个字段已经是连接字段，不再属于某个单独的表。

  除了可以用USING子句指定连接字段外，还可以使用ON子句完成类似的功能，上面使用USING子句的SQL语句，等价的使用ON子句的SQL语句如下：

  1. SELECT e.first_name, j.job_title, e.salary
  3. FROM employees e JOIN jobs j
  5. ON (e.job_id = j.job_id)

  ON子句比USING子句更为灵活，因为USING子句里只有一个字段名，也就是只有两个表中都有相同的字段名时，才能使用USING子句指定连接字段。而ON子句和等值连接一样，用于连接的两个字段，其字段名可以不同。

  上面介绍了自然连接、USING子句和ON子句，接下来继续给employees表和departments表建立自然连接，其SQL语句如下：

  1. SELECT e.first_name, d.department_name, e.salary
  3. FROM employees e
  5. NATURAL JOIN departments d

  执行该SQL语句，返回32条记录，如图3.16所示。该自然连接等价的等值连接的SQL语句如下：

  1. SELECT e.first_name, d.department_name, e.salary   
  3. FROM employees e,departments d
  5. WHERE e.department_id = d.department_id

  执行等值连接SQL语句，却返回了106条记录，如图3.17所示。这说明这两段SQL语句不等价，但问题出在哪里呢？

3.8 多表查询 - 图4


图3.16 自然连接

3.8 多表查询 - 图5


图3.17 等值连接

  通过查看这两个表的表结构后发现，两表中不仅有一个department_id字段名和类型相同，还有一个manager_id字段名和类型相同。所以在自然连接时，等价于对这两个字段同时进行了等值连接，所以该自然连接等价的等值连接，其SQL语句如下：

  1. SELECT e.first_name, d.department_name, e.salary   
  3. FROM employees e,departments d
  5. WHERE e.department_id = d.department_id AND e.manager_id = d.manager_id

  执行该SQL语句，返回和自然连接一样的32条记录。

  之前的自然连接都是在两个表之间的，接下来的SQL语句实现了employees表、departments表和locations表之间的自然连接，也称为三向连接：

  1. SELECT e.first_name, d.department_name, l.city, e.salary    
  3. FROM employees e
  5. NATURAL JOIN departments d
  7. NATURAL JOIN locations l

  其等价等值连接的SQL语句如下：

  1. SELECT e.first_name, d.department_name, l.city, e.salary    
  3. FROM employees e,departments d,locations l
  5. WHERE e.department_id = d.department_id AND e.manager_id = d.manager_id AND d.location_id = l.location_id

3.8.5 自关联

  我们还可以通过自关联，用一张表实现多表查询，SQL语句如下：

  1. --查询员工姓名和相应的领导姓名
  3. SELECT e.first_name 员工的姓名,b.first_name 老板姓名 FROM employees e, employees b
  5. WHERE e.manager_id=b.employee_id

  上述SQL语句中，将同一张employees表通过别名看成了两张不同的表：将别名为e的表看成员工表，将别名为b的表看成领导表，再在员工表和领导表之间进行多表查询。