14 创建高级联结

14.1 使用表列名

• 别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：
  • 缩短SQL语句
  • 允许在单条select语句中多次使用相同的表。
• 请看下面的select语句。它与前一章的例子中所有的语句基本相同，但改成了使用别名：

select cust_name, cust_contact
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
    and oi.order_num = o.order_num
    and prod_id = 'TNT2';

• 可以看到,from子句中3个表全都具有别名。customers as c建立c作为customers的别名，等等。这使得能使用省写的c而不是全名customers。在此例子中，表 别名只用于where子句。但是，表别名不仅用于where子句，还可以用于select的列表、order by子句以及语句的其他部分。
• 应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

14.2 使用不同类型的联结

• 迄今为止，我们使用的只是称为内部联结或等值联结的简单联结。现在来看3种其他联结，它们分别是自联结、自然联结和外部联结。

14.2.1 自联结

• 如前所述，使用表别名的主要原因之一是能在单条select语句中不止一次引用相同的表。下面举一个例子。
• 假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生 产的其他物品。下面是解决此问题的一种方法：

select prod_id, prod_name
from products
where vend_id = (
            select vend_id
            from products
            where prod_id = 'DTNTR'
        );

+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

• 这是第一种解决方案，它使用了子查询。内部的select语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的where子句中，以便检索这个供应商生产的 所有物品。
• 现在来看使用联结的相同查询:

select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';

+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

• 此查询中需要的两个表实际上是相同的表，因此products表在from子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中哪个 实例。
• 为解决此问题，使用了表别名。products的第一次出现为p1，第二次出现为p2。现在可以将这些别名用作表名。例如，select语句使用P1前缀明确给出所需要列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的哪一列（即使它们事实上是同一列）。where（通过匹配P1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的vend_id过滤数据，返回需要的数据。
• 用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

14.2.2 自然联结

• 无论何时对表进行联结，应该至少有一个列出现不止一个表中（被联结的列）。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。
• 怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符(select *)，对所有其他表的列使用明确的子集来完成的。下面举一个例子：

select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
    and oi.order_num = o.order_num
    and prod_id = 'FB';

+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 | 10         |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 | 10         |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+

• 在这个例子中，通配符只对第一表使用。所有其他列明确列出，所以没有重复的列被检索出来。事实上，迄今我为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

14.2.3 外部联结

• 许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成一下工作：
  • 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户
  • 列出所有产品以及订购数量，包括没有人订购的产品
  • 计算平均销售规模，包括那些至今尚未下订单的客户。
• 在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。
• 下面的select语句给出了一个简单的内部联结。它检索所有客户及其订单：

select customers.cust_id, orders.order_num
from customers inner join orders
on customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

• 外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行：

select customers.cust_id, orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 | NULL      |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

• 类似于上一章中所看到的内部联结，这条select语句使用了关键字OUTER JOIN来指定联结的类型（而不是在where字句中指定）。但是，与内部联结关联两个表中的行不通的是，外部联结还包括没有关联的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表)。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表(customers表)中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，如下例所示：

select customers.cust_id, orders.order_num
from customers right outer join orders
on customers.cust_id = orders.cust_id;

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

• 没有*=操作符 MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中很流行的。
• 外部联结的类型 存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒from或where子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

14.3 使用带聚集函数的联结

• 聚集函数用来汇总数据。虽然至今聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。
• 为说明这一点，请看一个例子。如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

select customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

• 此select语句使用inner join将customers和orders表互相关联。group by子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。
• 聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

select customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

• 这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

14.4 使用联结和联结条件

• 在总结关于联结的这两章前，有必要汇总一下关于联结及其使用的某些要点。
  • 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
  • 保证使用的联结条件，否则将返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡儿积。
  • 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。