SQL-联结表

联结是SQL中一个最重要、最强大的特性，有效地使用联结需要对关系数据库设计有基本的了解。

什么是联结表？

为什么要使用联结表？

如何编写使用联结表的SELECT语句？

如何使用表别名？

如何对被联结的表使用聚集函数？

---

1. 联结表

联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

---

使用SELECT语句编写使用联结表

-- 创建联结
SELECT vend_name, prod_name, prod_price 
FROM vendors, products 
WHERE vendors.vend_id = products.vend_id;

如果没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。

-- 不使用WHERE语句:按照笛卡尔积匹配
SELECT vend_name, prod_name, prod_price
FROM vendors, products;

笛卡儿积（cartesian product）
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

要保证所有的联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导致DBMS返回不正确的数据。

有时，返回笛卡尔积的联结，也称为叉联结（cross join）。

内联结

前面使用的联结也称作等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。
下面的SELECT语句返回和前面例子完全相同的数据：

-- 内联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

联结多个表

-- 联结多个表
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 = 20007;

性能考虑： DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

联结中的表的最大数目：虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS都有限制。请参阅具体的DBMS文档以了解其限制。

-- 返回订购产品RGAN01的顾客列表：
-- 使用子查询
-- SELECT cust_name, cust_contact
-- FROM customers
-- WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'RGAN01'));

-- 使用联结查询
SELECT cust_name, cust_contact 
FROM customers, orders, orderitems 
WHERE orders.order_num = orderitems.order_num 
AND orderitems.prod_id = 'RGAN01' 
AND customers.cust_id = orders.cust_id;

---

2. 使用表别名

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：

1. 缩短SQL语句；
2. 允许在一条SELECT语句中多次使用相同的表；

-- 使用表的别名 
SELECT cust_name, cust_contact 
FROM customers AS c, orders AS o, orderitems AS oi
WHERE o.order_num = oi.order_num 
AND oi.prod_id = 'RGAN01' 
AND c.cust_id = o.cust_id;

表的别名不仅可以用于WHERE子句，还可以用于SELECT的列表，ORDER BY子句以及其他语句部分。表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C）。

---

3. 不同类型的联结

前面，我们只使用内联结或等值联结的简单联结，现在来看其他三种联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

① 自联结

使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表。

-- 首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。
-- 方法1：使用子查询
SELECT cust_id, cust_name, cust_contact
FROM customers
WHERE cust_name = (SELECT cust_name
                   FROM customers
                   WHERE cust_contact = 'Jim Jones');

-- 方法2: 使用联结
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'

注：Oracle用户应该记住去掉AS。

② 自然联结

自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符（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';

事实上，我们迄今为止建立的每一个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

③ 外联结

联结包含那些在相关表中没有关联行的行，这种联结成为外联结。

-- 内联结:检索所有顾客及其订单：
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;

类似上一课提到的内联结，这条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;

有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：

-- 全外联结
SELECT customers.cust_id, orders.order_num
FROM orders FULL OUTER JOIN customers
ON customers.cust_id = orders.cust_id;

注： Access、MariaDB、MySQL、Open Office Base或SQLite不支持FULL OUTER JOIN语法。

4. 带聚集函数的联结

举个例子：

-- 检索所有顾客及每个顾客所下的订单数
SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

-- 聚集函数+外联结：检索所有顾客及每个顾客所下的订单数
SELECT customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单。