- 一、自联结
- 二、自然联结
- 三、外联结
- 请参阅
目录汇总:SQL 入门教程:面向萌新小白的零基础入门教程
迄今为止,我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结:自联结(self-join)、自然联结(natural join)和外联结 (outer join)。
一、自联结
如 使用表别名 所述,使用表别名的一个主要原因是能在一条 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 子句 中,以检索出为该公司工作的所有雇员(子查询 中讲授了子查询,更多信息请参阅该部分)。
现在来看使用联结的相同查询:
输入▼
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 处理联结远比处理子查询快得多。应该试一下两种方法,以确定哪一种的性能更好。
二、自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。标准的联结(联结 中介绍的内联结)返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。
怎样完成这项工作呢?答案是,系统不完成这项工作,由你自己完成它。自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符(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
。
分析▼
在这个例子中,通配符只对第一个表使用。所有其他列明确列出,所以没有重复的列被检索出来。
事实上,我们迄今为止建立的每个内联结都是自然联结,很可能永远都不会用到不是自然联结的内联结。
三、外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行。例如,可能需要使用联结完成以下工作:
- 对每个顾客下的订单进行计数,包括那些至今尚未下订单的顾客;
- 列出所有产品以及订购数量,包括没有人订购的产品;
- 计算平均销售规模,包括那些至今尚未下订单的顾客。
在上述例子中,联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
注意:语法差别
需要注意,用来创建外联结的语法在不同的 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 Customers.cust_id = Orders.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 Customers
FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
注意:
FULL OUTER JOIN
的支持MariaDB、MySQL 和 SQLite 不支持
FULL OUTER JOIN
语法。
请参阅
- 使用表别名
- 使用不同类型的联结
- 使用带聚集函数的联结
- 高级联结(JOIN)练习题
(完)