CHAPTER 5 Multi-table Queries

多表查询

---

5.1 什么是连接

5.1.1 笛卡尔积

当查询只连接了两个表而未指定连接列（键），那么数据库服务器将产生笛卡尔积，即将两个表的所有置换。

SELECT e.fname, e.lname, d.name
FROM employee e JOIN department d;

5.1.2 内连接

要修改上一个查询就要求我们描述清楚两个表要如何关联的：

SELECT e.fname, e.lname, d.name
FROM employee e JOIN department d
ON e.dept_id = d.dept_id;

若存在不匹配的e.dept_id或d.dept_id，则数据库会将其排除，这样的连接方式也就被称为内连接
但是对于那些未匹配的雇员行或者部门信息，若想包含，需要使用外连接。
 
在上述例子中，from语句并未指定所使用的连接类型，默认为内连接，实际上，最好在from子式中显式的指定连接类型。如：

SELECT e.fname, e.lname, d.name
FROM employee e INNER JOIN department d
ON e.dept_id = d.dept_id;#由于列名相同，可以用USING(dept_id)代替。

5.1.3 ANSI连接语法

基本所有主流的数据库管理系统都采用了SQL92的连接语法。（这就包含了一些就的连接语法）

• 连接条件和过滤条件被分隔到两个字句，（on子句和where子句），是查询语句更易于理解；
• 每两个表之间的连接条件都在他们自己的on子句中列出，这样不容易错误地忽略了某些连接条件。
• 使用SQL92连接语法的查询语句可以在各种数据管理系统通用。

SELECT a.account_id,a.cust_id,a.open_date,a.product_cd
FROM account a, branch b,employee e
WHERE a.open_emp_id = e.emp_id
    AND e.start_date<'2007-01-01'
    AND e.assigned_branch_id = b.branch_id
    AND (e.title = 'Teller' OR e.title ='Head Teller')
    AND b.name='Woburn Branch';

该查询一方面不太容易识别where中哪些条件是连接条件，哪些是过滤条件；另一方面是对于使用了何种连接类型也并非显而易见。（内连接还是其他）
SQL92的处理应该如下：

SELECT a.account_id,a.cust_id,a.open_date,a.product_cd
FROM account a INNER JOIN employee e
    ON a.open_emp_id = e.emp_id
    INNER JOIN branch b
    ON e.assigned_branch_id = b.branch_id
WHERE e.start_date<'2007-01-01'
    AND (e.title = 'Teller' OR e.title ='Head Teller')
    AND b.name='Woburn Branch';

这样就显然有连接条件都处在on语句中，过滤条件都处在where语句中。

5.2 连接3个或更多的表

参见上面，注意其中from中进行连接的表出现的次序可以任意调换，这是因为SQL是一个非过程化的语言。all you need to do is describe the object you need clearly, how to achieve it perfectly is just the databases server’s business.

5.2.1 将子查询结果作为查询表

SELECT a.account_id,a.cust_id,a.open_date,a.product_cd
FROM account a INNER JOIN 
    (SELECT emp_id,assigned_branch_id
    FROM employee
    WHERE start_date<'2007-01-01'
    AND (title = 'Teller' OR title ='Head Teller')) e
ON a.open_emp_id = e.emp_id
INNER JOIN
    (SELECT branch_id
    FROM branch
    WHERE b.name='Woburn Branch') b
ON e.assigned_branch_id = b.branch_id

5.2.2 连续两次使用同一张表

若某表同时包含其他两表的外键，就需要在from子句中两次引用该表。并最好两次给该表不同的别名。示例如下：

SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch
FROM account a INNER JOIN branch b_a
    ON a.open_branch_id = b_a.branch_id
    INNER JOIN employee e
    ON a.open_emp_id = e.emp_id
    INNER JOIN branch b_e
    ON e.assigned_branch_id = b_e.branch_id
WHERE a.product_cd = 'CHK';

5.3 自连接

若一个表包含了一个纸箱自身的外键，即只想本表主键的列，如雇员表中上级领导id指向了本表中某些职位较高的employee_id，这时候就需要我们使用自连接。

SELECT e.fname, e.lname
    e_mgr.fname mgr_lname, e_mgr.lname mgr_lname
FROM employee e INNER JOIN employee e_mgr
    ON e.superior_emp_id = e_mgr.emp_id;

5.4 相等连接和不等连接

大多数查询使用的是相等连接，但有时也可以通过限定值的范围实现对表的连接，亦称为不等连接。有时还需要使用不等自连接。
如从雇员中安排出纳员们两两竞争：

SELECT e1.fname, e1.lname,'VS' vs,e2.fname, e2.lname
FROM employee e1 INNER JOIN employee e2
    ON e1.emp_id < e2.emp_id
WHERE e1.title = 'Teller' AND e2.title = 'Teller';

5.5 连接条件和过滤条件

最好在合适的位置放置查询条件。