SQL 使用联结（二）

作者: 山药鱼儿 | 来源:发表于2022-03-20 12:14 被阅读0次

SQL 使用联结（二）
《SQL必知必会》笔记7-高级联结（as、self-join、o
SQL 使用联结（一）
need
SQL 学习笔记
高性能 SQL 计划 Day 3
图解SQL联结：内联结
图解SQL联结：右联结
图解SQL联结：交叉联结
图解SQL联结：左联结

连载的上一篇文章，我们学习了内联结的创建，包括使用 WHERE 过滤联结、使用更明确的语法 INNER JOIN ... ON 创建内联结。本节内容，我们将进步深入学习联结操作，包含创建和使用表的别名、自联结、外连接（左外连接和右外联结），下面开始我们今天的学习吧~

订单表、顾客表、订单明细表

创建和使用表别名

SQL 除了可以对列名和计算字段使用别名，还允许给表起名字。比如，下面涉及到 3 张表的查询语句中，我们就为每张表都起了一个别名。

SELECT
    C.cust_id,
    C.cust_name,
    C.cust_contact 
FROM
    Customers AS C,
    Orders AS O,
    OrderItems AS OI 
WHERE
    C.cust_id = O.cust_id 
    AND o.order_num = OI.order_num 
    AND OI.prod_id = 'RGAN01';

为表创建别名之后，在使用完全限定列名时就可以直接通过表的别名来引用了。上述 SQL 成功检索出了购买了商品 RGAN01 的顾客姓名和联系人。

注：Oracle 数据库不支持 AS 关键字，创建别名时直接在表名后添加别名即可，如 Customers C 即可为 Customers 表指定别名 C 。

为表创建别名的好处：

缩短 SQL 语句，书写简洁；
允许在一条 SELECT 语句中多次使用相同的表。

关于第二个好处，什么时候会在一条 SELECT 语句中多次使用一张表呢？听起来怪怪的 (*￣︶￣) 这就需要继续我们的联结话题了，自联结的时候就会在一条 SQL 中多次使用一张表！

创建自联结

我们举个案例来学习自联结。有如下的一张会员信息表：

cust_id 为会员 ID，具有唯一性；cust_name 此处代指会员所在的公司；cust_contact 为雇员姓名。现在，假设我们需要检索出和 Jim Jones 雇员属于同一家公司的所有会员信息。

如果使用子查询的话，我们可以很轻松地编写出如下的 SQL：

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

首先找到雇员 Jim Jones 所在的公司，然后找到该公司的所有会员信息。其中子查询使用的表名和外部查询使用的是同一张表 Customers 。

子查询可以转换为内联结的形式：

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

上述查询中联结的两张表其实是一张表，即 Customers 表：Customers 第一次出现使用了别名 c1，第二次出现使用了别名 c2 ，这样我们就可以在一条 SELECT 语句中多次使用这一张表了，并使用完全限定列名。

当联结的多张表实际上是同一个表的时候，就是自联结。上述 SQL 通过自联结联结两张 Customers 表，然后根据 c1.cust_name = c2.cust_name 和 c2.cust_contact = 'Jim Jones' 过滤联结。检索结果如下：

注：自联结的性能要优于子查询。

创建外联结

前面我们介绍的内联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。比如：

统计每个客户的订单数量，包含未下单的客户；
统计每类产品的订购数量，包含没有被订购的产品；

上述场景，联结包含了那些在相关表中没有 关联行 的记录，这种联结就是外联结了。

举个例子：使用内联结检索所有顾客及其订单：

SELECT
    C.cust_id,
    C.cust_name,
    O.order_num 
FROM
    Customers AS C
    INNER JOIN Orders AS O ON C.cust_id = O.cust_id
ORDER BY 
    C.cust_id;

检索运行结果：

内联结返回的记录中，每个顾客都至少有一个订单号与之对应。那如果我们需要返回全部的顾客，不管顾客有没有下单怎么办呢？

这就需要用到外联结了，外联结有两种基本的形式：左外联结 LEFT JOIN ... ON 和右外联结 RIGHT JOIN ... ON；通过调整 FROM 和 JOIN 子句中表的顺序，两种外联结可以相互转换。

下面，我们使用左外联结改写上述 SQL：

SELECT
    C.cust_id,
    C.cust_name,
    O.order_num 
FROM
    Customers AS C
    LEFT JOIN Orders AS O ON C.cust_id = O.cust_id 
ORDER BY
    C.cust_id;

运行结果：

与内联结关联两个表中的行不同的是，外联结还包括没有关联行的记录。上述 SQL 使用 LEFT JOIN ... ON 从 LEFT JOIN 子句左侧的表 Customers 中选择所有行，LEFT JOIN 右侧的表只选择关联行。因此，返回结果中就会出现没有关联订单的顾客记录。

注：SQLite 不支持 RIGHT JOIN ... ON ，小鱼这里就不演示右联结了。

在联结中使用聚合函数

聚合函数用来汇总数据，之前我们介绍聚合函数时，都是在一张表中汇总所有记录，或者分组聚合每组记录。其实，聚合函数也可以与联结一起使用！

我们来看下面的例子，统计所有下单顾客中，每位客户的订单总数：

SELECT
    Customers.cust_id,
    Customers.cust_name,
    COUNT( Orders.order_num ) AS order_num 
FROM
    Customers
    INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
GROUP BY
    Customers.cust_id 
ORDER BY
    order_num DESC;

上述 SQL 使用 INNER JOIN ... ON 将 Customers 表和 Orders 表相互关联，GROUP BY 按顾客 ID 分组数据库，COUNT( Orders.order_num ) 对每个客户的订单进行计数，作为 order_num 字段返回。

如果使用外联结，可以包含订单数量为 0 的顾客：

SELECT
    Customers.cust_id,
    Customers.cust_name,
    COUNT( Orders.order_num ) AS order_num 
FROM
    Customers
    LEFT JOIN Orders ON Customers.cust_id = Orders.cust_id 
GROUP BY
    Customers.cust_id 
ORDER BY
    order_num DESC;

检索运行结果：