开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 27 天，点击查看活动详情

创建高级联结

表别名

之前记录过，我们会给计算字段起别名，也可以给列名起别名。现在，我们还可以给表名起别名。

这样做的好处有：

缩短 SQL 语句
允许在单条 SELECT 语句中多次使用相同的表

我们先来回顾之前的例子：

SELECT
    prod_name, vend_name, prod_price, quantity
FROM
    orderitems, products, vendors
WHERE
    products.vend_id = vendors.vend_id
    AND orderitems.prod_id = products.prod_id
    AND order_num = 1001;

在上面例子中，我们可以给表起别名，这样就可以使用省写的别名而不是表全名。

如下所示：

SELECT
    prod_name, vend_name, prod_price, quantity
FROM
    orderitems as o, products as p, vendors as v
WHERE
    p.vend_id = v.vend_id
    AND o.prod_id = p.prod_id
    AND order_num = 1001;

需要注意的是，表别名只在查询执行中使用，其不会返回到客户机

使用不同类型的联结

在上一篇笔记中，记录的是内部联结（或等值联结）。下面，记录的是其他不同类型的联结，分别是：

自联结
自然联结
外部联结

自联结

现在看一个需求：

从 products 表中查询 prod_id 为 'abc' 的产品的供应商 ID
查询该供应商生产的其他产品信息

代码示例如下：

select
    prod_id, prod_name
from
    products
where
    vend_id = (
        select vend_id from products
        where prod_id = 'abc'
    );

可以看到，我们使用了子查询作为解决方案。该方法先是做了一个简单的检索，查询到 prod_id 为 'abc' 的产品的供应商 ID，即 vend_id。该 vend_id 用于外部查询的 where 子句中。这样就检索出该供应商 ID 生产的所有产品。

现在，我们有了另一种解决方案，即使用自联结。

先看代码：

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

记录：

注意，如果使用了子查询语句，用于从相同的表中检索数据，那么此时可以用自联结的方式来代替子查询。
从上面的代码中可以看到，在 from 子句中，引用了两次 products 表，并分别起了表别名（p1 和 p2）。起别名的作用是让 MySQL 知道引用的是哪个实例，这可以避免发生二义性问题。
在 where 子句中，通过匹配 p1 中的 vend_id 和 p2 中的 vend_id 来联结两个表，然后按照 p2 中的 prod_id 来过滤数据，最后返回检索的数据。

自然联结

我们知道，为了对表进行联结，被联结的列应该出现在不止一个表中。前面记录过的内部联结（等值联结）会返回所有数据（相同的列可能会多次出现）。

我们可以使用自然联结，来排除联结时相同的列多次出现的情况，使得每个列只返回一次。

来看下面代码：

select
    c.*,
    o.order_num,
    o.order_data,
    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 = 'xxx';

记录：

在上述代码中，我们可以看到：我们对表 c 使用了通配符 * ，而对所有其他表的列使用了明确的字段来完成检索，这就是自然联结。
自然联结的作用就是：没有重复的列被检索出来

事实上，我们建立的每个内部联结都是自然联结。

外部联结

什么是外部联结呢？我们说，如果在联结表的时候，需要将那些在相关表中没有关联行的行也包含进来，那这种类型的联结就称为外部联结。

比如在联结表时：

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

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

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

先来看一个内部联结的例子：

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;

在上述代码中：

使用了关键字 outer join 指定了联结的类型
注意，与内部联结关联两个表的行不同，外部联结还包括了没有关联行的行
为了指定包括所有行的表，我们需要指定关键字 left 和 right。其中 left 指的是 outer join 左边的表， right 指的是 outer join 右边的表

使用带聚集函数的联结

事实上，聚集函数可以与联结一起使用。

来看这个例子：

select
    customers.cust_id,
    customers.cust_name,
    count(orders.order_num) as ord_num
from
    customers inner join orders
on
    customers.cust_id = orders.cust_id
group by
    customers.cust_id;

记录：

上述代码用于检索所有客户及其订单数
函数调用 count(orders.order_num) 对每个客户的订单数进行统计，并作为 ord_num 返回。

使用联结和联结条件

小结如下：

在使用联结表时，要注意联结类型，一般使用内部联结（当然外部联结也是有效的）。
必须提供并使用正确的 联结条件，返回正确的数据
在一个联结中可以包含多个表（对于每种联结可以采用不同的联结类型），我们可以在测试前分别测试每个联结，确保其正确性

【MySQL必知必会】：创建高级联结