• 1、创建高级联结
  • 1.1 使用表别名
  • 1.2 自联结（self-join）
  • 1.3 自然联结（natural join）
  • 1.4 外联结（outer join）
• 2、使用带聚集函数的联结
  • 2.1 检索所有顾客及每个顾客所下的订单数
  • 2.2 聚集函数与左外部联结一起使用
• 3、使用联结和联结条件
• 4、总结

• 参考资料
  • 《MySQL必知必会（文字版）》

1、创建高级联结

1.1 使用表别名

• SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：
  • 缩短 SQL 语句；
  • 允许在一条 SELECT 语句中多次使用相同的表。

SELECT cust_name, cust_contact
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'; 

1.2 自联结（self-join）

• 假如要给与 Jim Jones 同一公司的所有顾客发送一封信件：先找出 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'; 

• 此查询中需要的两个表实际上是相同的表，因此 Customers 表在 FROM 子句中出现了两次。
• 虽然这是完全合法的，但对 Customers 的引用具有歧义性，因为 DBMS 不知道你引用的是哪个 Customers 表。
• 解决此问题，需要使用表别名。
  • Customers 第一次出现用了别名 C1，第二次出现用了别名 C2。

• 许多 DBMS 处理联结远比处理子查询快得多

1.3 自然联结（natural join）

• 自然联结排除多次出现，使每一列只返回一次。
• 自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符 （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'; 

• 在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以 没有重复的列被检索出来。

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

1.4 外联结（outer join）

• 许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。

• 例如，可能需要使用联结完成以下工作：
  • 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
  • 列出所有产品以及订购数量，包括没有人订购的产品；
  • 计算平均销售规模，包括那些至今尚未下订单的顾客。

• 在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

• 检索所有顾客及其订单 - 内联结：

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 表）中选择所有行。

• 全外联结（full outer join）：
  • 它检索两个表中的所有行并关联那些可以关联的行。
  • 与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。
• Access、MariaDB、MySQL、Open Office Base 和 SQLite 不支持 FULL OUTER JOIN 语法。

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
 ON Orders.cust_id = Customers.cust_id; 

2、使用带聚集函数的联结

2.1 检索所有顾客及每个顾客所下的订单数

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 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联。
• GROUP BY 子句按顾客分组数据，
• 因此，函数调用 COUNT(Orders.order_num)对每个顾客的订单计数，将它作为 num_ord 返回。

2.2 聚集函数与左外部联结一起使用

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; 

3、使用联结和联结条件

4、总结

• 自联结：需要使用表别名，获取同一个表中的数据，联结同一个表，用不同的表别名表示
• 自然联结：排除多次出现，使每一列只返回一次，可联结不同的表
• 外联结：有左外联结、右外联结、全外联结；可联结不同的表，返回所有数据（包括 NULL）