零基础入门 SQL 系列之（六）表连接

等值连接（内连接）

前面讲到的查询都是从单个数据表查询数据，如果需要查询的数据存储在多个表中，可以使用连接，连接其实就是将多张表根据一定的条件组合成一张表，然后从组合后的表中查询想要的数据。创建连接非常简单，指定要连接的所有表以及关联它们的方式即可。例如我们要在一个 SELECT 语句中查询每个供应商以及供应商供应的商品的名称和价格。我们可以这么查询：

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

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;

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

SELECT vend_name,prod_name,prod_price
FROM vendors, products;

从结果中我们可以看出，笛卡尔积的结果不是我们想要的，因为这里用每个供应商匹配了每个商品，包括了供应商没有供应的商品。刚才介绍的连接称为等值连接，也称为内连接，对于这种连接，我们还有稍微不同的写法，这种写法明确指定了连接的类型。

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

使用这种语法时，连接条件用 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;

上述 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;

我们也可以在同一条 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';

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

自连接和外连接

自连接

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

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

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

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

当既可以使用子查询又可以使用自连接来查询数据时，我们推荐使用自连接，因为 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;

也可以把 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;

再来举个例子，我们要查询订单时间在 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';

左外连接以左表为主，右表关联不上的字段为 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;

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;

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

右外连接以右表为主，左表关联不上的字段为 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;

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

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;

备注：示例中用到的表和数据可以通过链接（https://github.com/songw/sql）下载。

本系列目录：
零基础入门 SQL 系列之（一）查询数据
零基础入门 SQL 系列之（二）排序
零基础入门 SQL 系列之（三）过滤数据
零基础入门 SQL 系列之（四）内置函数
零基础入门 SQL 系列之（五）数据汇总
零基础入门 SQL 系列之（六）表连接
零基础入门 SQL 系列之（七）组合
零基础入门 SQL 系列之（八）插入、更新、删除
零基础入门 SQL 系列之（九）创建和操作表
零基础入门 SQL 系列之（十）视图