第13课 SQL入门之创建高级联结

本课讲解另外一些联结（包括它们的含义和使用方法），介绍如何使用表别名，如何对被联结的表使用聚集函数。

13.1 使用表别名

第7课介绍了如何使用别名引用被检索的表列。给列起别名的语法如下：
输入▼

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' 
       AS vend_title 
FROM Vendors 
 BY vend_name; 

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

分析▼
可以看到，FROM子句中的三个表全都有别名。Customers AS C使用C作为Customers的别名，如此等等。这样，就可以使用省略的C而不用全名Customers。在这个例子中，表别名只用于WHERE子句。其实它不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及其他语句部分。

警告：Oracle中没有AS
Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C）。
需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

13.2 使用不同类型的联结

迄今为止，我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

13.2.1 自联结

如前所述，使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表。下面举一个例子。
假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。下面是解决此问题的一种方法：
输入▼

SELECT cust_id, cust_name, cust_contact 
FROM Customers 
WHERE cust_name = (SELECT cust_name
				   FROM Customers 
				   WHERE cust_contact = 'Jim Jones'
					); 

输出▼

cust_id	cust_name	cust_contact
1000000003	Fun4All	Jim Jones
1000000004	Fun4All	Denise L. Stephens

分析▼
这是第一种解决方案，使用了子查询。内部的SELECT语句做了一个简单检索，返回Jim Jones工作公司的cust_name。该名字用于外部查询的WHERE子句中，以检索出为该公司工作的所有雇员（第11课中讲授了子查询，更多信息请参阅该课）。
现在来看使用联结的相同查询：
输入▼

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2 
WHERE c1.cust_name = c2.cust_name  
AND c2.cust_contact = 'Jim Jones';  

输出▼

cust_id	cust_name	cust_contact
1000000003	Fun4All	Jim Jones
1000000004	Fun4All	Denise L. Stephens

提示：Oracle中没有AS
Oracle用户应该记住去掉AS。

分析▼
此查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次。虽然这是完全合法的，但对Customers的引用具有歧义性，因为DBMS不知道你引用的是哪个Customers表。
解决此问题，需要使用表别名。Customers第一次出现用了别名C1，第二次出现用了别名C2。现在可以将这些别名用作表名。例如，SELECT语句使用C1前缀明确给出所需列的全名。如果不这样，DBMS将返回错误，因为名为cust_id、cust_name、cust_contact的列各有两个。DBMS不知道想要的是哪一列（即使它们其实是同一列）。WHERE首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据。

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

13.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 = 'RGAN01'; 

提示：Oracle中没有AS
Oracle用户应该记住去掉AS。

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

13.2.3 外联结

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

• 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。
  在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

警告：语法差别
需要注意，用来创建外联结的语法在不同的SQL实现中可能稍有不同。下面段落中描述的各种语法形式覆盖了大多数实现，在继续学习之前请参阅你使用的DBMS文档，以确定其语法。

下面的SELECT语句给出了一个简单的内联结。它检索所有顾客及其订单：
输入▼

SELECT Customers.cust_id, Orders.order_num 
FROM Customers 
INNER JOIN Orders  ON Customers.cust_id = Orders.cust_id; 

外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：
输入▼

SELECT Customers.cust_id, Orders.order_num 
FROM Customers 
LEFT OUTER JOIN Orders  ON Customers.cust_id = Orders.cust_id;  

输出▼

cust_id	order_num
1000000001	20005
1000000001	20009
1000000002	NULL
1000000003	20006
1000000004	20007
1000000005	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 Orders.cust_id = Customers.cust_id; 

警告：SQLite外联结
SQLite支持LEFT OUTER JOIN，但不支持RIGHT OUTER JOIN。幸好，如果你确实需要在SQLite中使用RIGHT OUTER JOIN，有一种更简单的办法，这将在下面的提示中介绍。

提示：外联结的类型
要记住，总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

还存在另一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：
输入▼

SELECT Customers.cust_id, Orders.order_num 
FROM Orders 
FULL OUTER JOIN Customers ON Orders.cust_id = Customers.cust_id; 

警告：FULL OUTER JOIN的支持
Access、MariaDB、MySQL、Open Office Base或SQLite不支持FULL OUTER JOIN语法。

13.3 使用带聚集函数的联结

如第9课所述，聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。
我们来看个例子，要检索所有顾客及每个顾客所下的订单数，下面的代码使用COUNT()函数完成此工作：
输入▼

SELECT 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_id	um_ord
1000000001	2
1000000003	1
1000000004	1
1000000005	1

分析▼
这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。GROUP BY子句按顾客分组数据，因此，函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回。
聚集函数也可以方便地与其他联结一起使用。请看下面的例子：
输入▼

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

提示：Oracle中没有AS
再次提醒Oracle用户，请记住删除AS。
输出▼

cust_id	num_ord
1000000001	2
1000000002	0
1000000003	1
1000000004	1
1000000005	1

分析▼
这个例子使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单。

13.4 使用联结和联结条件

在总结讨论联结的这两课前，有必要汇总一下联结及其使用的要点。

• 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
• 关于确切的联结语法，应该查看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法）。
• 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。

---

上一篇：第12课 SQL入门之联结表