本文介绍自联结(self-join)、自然联结(natural join)和外联结 (outer join),包括它们的含义和使用方法。介绍如何使用表别名,如何对被联结的表使用聚集函数。
一、使用表别名
SQL 如何创建计算字段 介绍了如何使用别名引用被检索的表列。给列起别名的语法如下:
|
|
SQL 除了可以对列名和计算字段使用别名,还允许给表名起别名。这样做有两个主要理由:
- 缩短 SQL 语句;
- 允许在一条
SELECT
语句中多次使用相同的表。
请看下面的 SELECT
语句。它与 如何使用 SQL INNER JOIN 联结两个或多个表 例子中所用的语句基本相同,但改成了使用别名:
|
|
可以看到,FROM
子句中的三个表全都有别名。Customers AS C
使用 C
作为 Customers
的别名,如此等等。
这样,就可以使用省略的 C
而不用全名 Customers
。
在这个例子中,表别名只用于 WHERE
子句。其实它不仅能用于 WHERE
子句,还可以用于 SELECT
的列表、ORDER BY
子句以及其他语句部分。
需要注意,表别名只在查询执行中使用。与列别名不一样,表别名不返回到客户端。
二、使用不同类型的联结
迄今为止,我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结:自联结(self-join)、自然联结(natural join)和外联结 (outer join)。
2.1 自联结
如前所述,使用表别名的一个主要原因是能在一条 SELECT
语句中不止一次引用相同的表。下面举一个例子。
假如要给与 Jim Jones
同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones
工作的公司,然后找出在该公司工作的顾客。
下面是解决此问题的一种方法:
|
|
输出:
|
|
这是第一种解决方案,使用了子查询。内部的 SELECT
语句做了一个简单检索,返回 Jim Jones
工作公司的 cust_name
。
该名字用于外部查询的 WHERE
子句中,以检索出为该公司工作的所有雇员(SQL 如何使用子查询 中介绍了子查询,更多信息请参阅此文章)。
现在来看使用联结的相同查询:
|
|
输出:
|
|
此查询中需要的两个表实际上是相同的表,因此 Customers
表在 FROM
子句中出现了两次。
虽然这是完全合法的,但对 Customers
的引用具有歧义性,因为 DBMS 不知道你引用的是哪个 Customers
表。
解决此问题,需要使用表别名。Customers
第一次出现用了别名 c1
,第二次出现用了别名 c2
。现在可以将这些别名用作表名。
例如,SELECT
语句使用 c1
前缀明确给出所需列的全名。
如果不这样,DBMS 将返回错误,因为名为 cust_id
、cust_name
、cust_contact
的列各有两个。DBMS 不知道想要的是哪一列(即使它们其实是同一列)。
WHERE
首先联结两个表,然后按第二个表中的 cust_contact
过滤数据,返回所需的数据。
2.2 自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。
标准的联结(如何使用 SQL INNER JOIN 联结两个或多个表 中介绍的内联结)返回所有数据,相同的列甚至多次出现。
自然联结排除多次出现,使每一列只返回一次。
怎样完成这项工作呢?答案是,系统不完成这项工作,由你自己完成它。
自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT *
),而对其他表的列使用明确的子集来完成。下面举一个例子:
|
|
在这个例子中,通配符只对第一个表使用。所有其他列明确列出,所以没有重复的列被检索出来。
事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
2.3 外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行。例如,可能需要使用联结完成以下工作:
- 对每个顾客下的订单进行计数,包括那些至今尚未下订单的顾客;
- 列出所有产品以及订购数量,包括没有人订购的产品;
- 计算平均销售规模,包括那些至今尚未下订单的顾客。
在上述例子中,联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
下面的 SELECT
语句给出了一个简单的内联结。它检索所有顾客及其订单:
|
|
外联结语法类似。要检索包括没有订单顾客在内的所有顾客,可如下进行:
|
|
输出:
|
|
类似 如何使用 SQL INNER JOIN 联结两个或多个表 提到的内联结,这条 SELECT
语句使用了关键字 OUTER JOIN
来指定联结类型(而不是在 WHERE
子句中指定)。
但是,与内联结关联两个表中的行不同的是,外联结还包括没有关联行的行。
在使用 OUTER JOIN
语法时,必须使用 RIGHT
或 LEFT
关键字指定包括其所有行的表(RIGHT
指出的是 OUTER JOIN
右边的表,而 LEFT
指出的是 OUTER JOIN
左边的表)。
上面的例子使用 LEFT OUTER JOIN
从 FROM
子句左边的表(Customers
表)中选择所有行。
为了从右边的表中选择所有行,需要使用 RIGHT OUTER JOIN
,如下例所示:
|
|
还存在另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。
与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。全外联结的语法如下:
|
|
三、使用带聚集函数的联结
如 如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 汇总数据 所述,聚集函数用来汇总数据。
虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据,但这些函数也可以与联结一起使用。
我们来看个例子,要检索所有顾客及每个顾客所下的订单数,下面的代码使用 COUNT()
函数完成此工作:
|
|
输出:
|
|
这条 SELECT
语句使用 INNER JOIN
将 Customers
和 Orders
表互相关联。
GROUP BY
子句按顾客分组数据,因此,函数调用 COUNT(Orders.order_num)
对每个顾客的订单计数,将它作为 num_ord
返回。
聚集函数也可以方便地与其他联结一起使用。请看下面的例子:
|
|
输出:
|
|
这个例子使用左外部联结来包含所有顾客,甚至包含那些没有任何订单的顾客。结果中也包含了顾客 1000000002
,他有 0
个订单,这和使用 INNER JOIN
时不同。
四、使用联结和联结条件
在总结讨论联结的这两篇前,有必要汇总一下联结及其使用的要点。
- 注意所使用的联结类型。一般我们使用内联结,但使用外联结也有效。
- 关于确切的联结语法,应该查看具体的文档,看相应的 DBMS 支持何种语法(大多数 DBMS 使用这两篇中描述的某种语法)。
- 保证使用正确的联结条件(不管采用哪种语法),否则会返回不正确的数据。
- 应该总是提供联结条件,否则会得出笛卡儿积。
- 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。
五、小结
本文是 如何使用 SQL INNER JOIN 联结两个或多个表 的延续,首先介绍了如何以及为什么使用别名,然后讨论不同的联结类型以及每类联结所使用的语法。
我们还介绍了如何与联结一起使用聚集函数,以及在使用联结时应该注意的问题。
(完)