第七章连接 | AI大学堂

前面讲到的查询都是从单个数据表查询数据，如果需要查询的数据存储在多个表中，那么怎样用一条 SELECT 语句来从多个表中查询数据呢？

答案是使用连接。连接其实就是将多张表根据一定的条件组合成一张表，然后从组合后的表中查询想要的数据。创建连接非常简单，指定要连接的所有表以及关联它们的方式即可。

# 1.等值连接

例如我们要在一个 SELECT 语句中查询每个供应商以及供应商供应的商品的名称和价格。我们可以这么查询：

SELECT 
    vend_name,
    prod_name,
    prod_price
FROM
    vendors, products
WHERE
    vendors.vend_id = products.vend_id;

1
2
3
4
5
6
7
8

运行结果如下：

vend_id 必须使用完全限定列名，用一个句点分隔表名和列名，因为在表 vendors 和表 products 中都有 vend_id 列。如果引用一个没有用表名限制的具有歧义的列名，大多数 DBMS 会返回错误。

为了书写方便，可以给表起个别名。例如：

SELECT 
    vend_name,
    prod_name,
    prod_price
FROM
    vendors v, products p
WHERE
    v.vend_id = p.vend_id;

1
2
3
4
5
6
7
8

运行结果如下：

在连接两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，只包含那些匹配给定连接条件的行。如果没有 WHERE 子句，第一个表中的每一行将与第二表中的每一行匹配，而不管它们逻辑上是否能配在一起。实际上没有 WHERE 子句的连接返回的是两个表的笛卡尔积，查询出的行的数目是第一个表中的行数乘以第二个表中的行数。我们可以看下没有 WHERE 子句的结果是什么样的。

SELECT 
    vend_name,
    prod_name,
    prod_price
FROM
    vendors, products;

1
2
3
4
5
6

运行结果如下：

从结果中我们可以看出，笛卡尔积的结果不是我们想要的，因为这里用每个供应商匹配了每个商品，包括了供应商没有供应的商品。

笛卡尔乘积也可以通过 CROSS JOIN 来得到。

SELECT 
    vend_name,
    prod_name,
    prod_price
FROM
    vendors CROSS JOIN products;

1
2
3
4
5
6

运行结果如下：

刚才介绍的连接称为等值连接，也称为内连接，对于这种连接，我们还有稍微不同的写法，这种写法明确指定了连接的类型。另外一种写法：

SELECT 
    vend_name, 
    prod_name, 
    prod_price
FROM
    vendors v
        INNER JOIN
    products p ON v.vend_id = p.vend_id;

1
2
3
4
5
6
7
8

运行结果如下：

使用这种语法时，连接条件用 ON 子句指出，而不是 WHERE 子句指出，传递给 ON 的实际条件与传递给 WHERE 的相同。我们也可以把 INNER 省略掉，直接写成：

SELECT 
    vend_name, 
    prod_name, 
    prod_price
FROM
    vendors v
        JOIN
    products p ON v.vend_id = p.vend_id;

1
2
3
4
5
6
7
8

运行结果如下：

上述 SQL 同样可以跟上 WHERE 子句对数据进行过滤。例如：

SELECT 
    vend_name, 
    prod_name, 
    prod_price
FROM
    vendors v
        JOIN
    products p ON v.vend_id = p.vend_id
WHERE 
    prod_price > 5;

1
2
3
4
5
6
7
8
9
10

运行结果如下:

我们也可以在同一条 SELECT 里可以关联 2 个以上的表。例如：我们要查询客户 The Toy Store 购买了哪些商品。SQL 可以如下写：

SELECT 
    DISTINCT prod_name
FROM
    customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN orderitems oi ON oi.order_num = o.order_num
JOIN products p ON p.prod_id = oi.prod_id
WHERE c.cust_name = 'The Toy Store';

1
2
3
4
5
6
7
8

运行结果如下：

在 MySQL 里，应该注意不要连接不必要的表，因为连接的表越多，性能下降的越厉害。阿里巴巴 Java 开发手册里规定，超过三个表禁止join。但是也不能一概而论，当每个表都比较小时，超过 3 个表的连接也是可以的。

# 2.自连接和外连接

刚才讲了等值连接或者叫做内连接，现在来看下自连接和外连接。假如我们要查询和供应商 Furball Inc. 在一个国家的其他供应商的名称。我们可以如下写 SQL，使用子查询：

SELECT 
    DISTINCT vend_name 
FROM 
    vendors
WHERE vend_country = (SELECT 
                          DISTINCT vend_country 
                      FROM
                          vendors
                      WHERE vend_name = 'Furball Inc.');

1
2
3
4
5
6
7
8
9

运行结果如下：

我们也可以使用自连接写成如下形式:

SELECT
    DISTINCT v1.vend_name
FROM
    vendors v1, vendors v2
WHERE v1.vend_country = v2.vend_country
AND v2.vend_name = 'Furball Inc.';

1
2
3
4
5
6

运行结果如下：

当既可以使用子查询又可以使用自连接来查询数据时，我们推荐使用自连接，因为 DBMS 处理自连接比子查询要快得多。

上述连接查询到的结果都是关联到的记录，也就是说根据关联条件，每个表都包含的记录，有时候我们需要查询没有关联到的记录。这时候便用到外连接。外连接有 LEFT OUTER JOIN（左外连接）又叫 LEFT JOIN（左连接）和 RIGHT OUTER JOIN（右外连接）又叫 RIGTH JOIN（右连接）。

例如：我们要查询没有订单的客户的 ID。

SELECT 
    DISTINCT c.cust_id
FROM
    customers c LEFT OUTER JOIN
    orders o ON c.cust_id = o.cust_id
WHERE o.cust_id IS NULL;

1
2
3
4
5
6

运行结果如下：

也可以把 OUTER 省略掉，直接使用 LEFT JOIN。

SELECT 
    DISTINCT c.cust_id
FROM
    customers c LEFT JOIN
    orders o ON c.cust_id = o.cust_id
WHERE o.cust_id IS NULL;

1
2
3
4
5
6

运行结果如下：

再来举个例子，我们要查询订单时间在 2012 年 1 月的用户。

SELECT 
    DISTINCT c.cust_id
FROM
    customers c LEFT JOIN
    orders o ON c.cust_id = o.cust_id
WHERE
    DATE_FORMAT(o.order_date, '%Y-%m') = '2012-01';

1
2
3
4
5
6
7

运行结果如下：

左外连接以左表为主，右表关联不上的字段为NULL。左表叫做驱动表，右表叫做被驱动表。刚才的查询同样可以使用 RIGHT OUTER JOIN 来实现：

SELECT 
    DISTINCT c.cust_id
FROM
    orders o RIGHT OUTER JOIN
    customers c ON c.cust_id = o.cust_id
WHERE o.cust_id IS NULL;

1
2
3
4
5
6

运行结果如下：

SELECT 
    DISTINCT c.cust_id
FROM
    orders o RIGHT JOIN
    customers c ON c.cust_id = o.cust_id
WHERE o.cust_id IS NULL;

1
2
3
4
5
6

运行结果如下：

SELECT 
    DISTINCT c.cust_id
FROM
    customers c RIGHT JOIN
    orders o ON c.cust_id = o.cust_id
WHERE
    DATE_FORMAT(o.order_date, '%Y-%m') = '2012-01';

1
2
3
4
5
6
7

运行结果如下：

右外连接以右表为主，左表关联不上的字段为NULL。右表叫做驱动表，左表叫做被驱动表。

前面讲到的汇总函数都是单个表的数据的汇总。汇总函数也可以和连接结合在一起。例如：我们可以查询所有顾客以及每个顾客所下的订单数：

SELECT 
    c.cust_name, COUNT(o.order_num)
FROM
    customers c
        JOIN
    orders o ON c.cust_id = o.cust_id
GROUP BY
    c.cust_name;

1
2
3
4
5
6
7
8

运行结果如下：

例如：我们还可以查询每个顾客订购的商品的总金额

SELECT 
    c.cust_name, 
    SUM(oi.quantity * oi.item_price)
FROM
    customers c
        JOIN
    orders o ON c.cust_id = o.cust_id
        JOIN
    orderitems oi ON o.order_num = oi.order_num
GROUP BY
    c.cust_name;

1
2
3
4
5
6
7
8
9
10
11

运行结果如下：

小结：

# 3.课后题

查询客户 Village Toys 购买了哪些商品？