16.使用MySQL之创建高级联结

1. 使用表别名

第10章中介绍了如何使用别名引用被检索的表列。给列起别名的语法如下：

select concat(rtrim(vend_name), ' (', rtrim(vend_country), ')') as vend_title
from vendors
order by vend_name;

别名除了用于列名和计算字段外，SQL还允许给表名起别名。

这样做有两个主要理由：

1. 缩短SQL语句；

2. 允许在单条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子句以及语句的其他部分。

应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

2. 使用不同类型的联结

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

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');
  

  输出如下：

  

  这是第一种解决方案，它使用了子查询。内部的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';

输出如下：

此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。

为解决此问题，使用了表别名。products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名。

例如，SELECT语句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据。

补充：

用自联结而不用子查询：

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

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';

输出如下：

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。

补充：

修改一下在自联结那一节举的例子：

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

输出如下：

看完这个输出。应该能对自然联结有个更深的理解。

2.3 外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：

• 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

• 列出所有产品以及订购数量，包括没有人订购的产品；

• 计算平均销售规模，包括那些至今尚未下订单的客户。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单：

select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

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

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

输出如下：

插一句题外话

其实写成如下的语句也可以：

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

省略了outer。

回归正题

类似于上一章中所看到的内部联结，这条SELECT语句使用了关键字OUTER JOIN(其中OUTER可省略)来指定联结的类型（而不是在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 orders.cust_id = customers.cust_id;

输出如下：

插一句题外话

其实写成如下的语句也可以：

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

可以省略outer。

补充：

• 没有*=操作符：

  MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中是很流行的。

  插入一个题外话：

  介绍下*=和=*：

  在某些数据库管理系统中，如SQL Server，*=和=*被用于表示外连接，具体用法如下：

  • *=：

    用于表示左外连接（Left Outer Join）。在查询中，如果某一表的字段在结果集中不匹配右表的记录，则该记录依然会出现在结果中，右表的字段会返回NULL。

    SELECT *
    FROM table1, table2
    WHERE table1.id *= table2.id;
    

  • =*：

    用于表示右外连接（Right Outer Join）。在这种情况下，右表的记录会全部返回，即使左表没有匹配的记录，左表的字段会返回NULL。

    SELECT *
    FROM table1, table2
    WHERE table1.id =* table2.id;
    

  • 需要注意的是，这些简化的连接操作符在SQL Server中已经被弃用，推荐使用标准的JOIN语法（如LEFT JOIN和RIGHT JOIN），因为它们在可读性和兼容性上更优。其他数据库系统（如MySQL、PostgreSQL、Oracle等）也不支持这些操作符，因此在跨数据库开发时，使用标准的JOIN语法是最佳实践。

• 外部联结的类型：

  存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

• 不支持FULL OUTER JOIN：

  MySQL本身不直接支持FULL OUTER JOIN。不过，可以通过组合LEFT JOIN和RIGHT JOIN来实现相同的效果。示例如下：

  SELECT *
  FROM table1
  LEFT JOIN table2 ON table1.id = table2.id
  
  UNION
  
  SELECT *
  FROM table1
  RIGHT JOIN table2 ON table1.id = table2.id;
  

  这样可以得到两个表的所有记录，无论是匹配还是不匹配。UNION会去除重复的记录，因此最终结果会包含所有左表和右表的记录。

3. 使用带聚集函数的联结

正如第12章所述，聚集函数用来汇总数据。虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。

请看一个例子。如果要检索所有客户及每个客户所下的订单数，下面使用了``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;

输出如下：

此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;

输出如下：

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

4. 使用联结和联结条件

在总结关于联结的这两章前，有必要汇总一下关于联结及其使用的某些要点。

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

• 保证使用正确的联结条件，否则将返回不正确的数据。

• 应该总是提供联结条件，否则会得出笛卡儿积。

• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

  比如：

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

  输出如下：

  

  其实写成这样也可以：

  select c.cust_id, prod_id, o.order_num
  from customers c inner join orders o
  on c.cust_id = o.cust_id
  inner join orderitems oi
  on o.order_num = oi.order_num