《SQL必知必会》第十三课 创建高级联结表 使用不同类型的联结 使用带聚集函数的联结 使用联结时应注意的问题

第十三课 创建高级联结表

#使用表别名的原因
#不同的联结类型以及每类联结所使用的语法
#如何与联结一起使用聚集函数
#使用联结时的注意问题

一、使用表别名

[1]前面（第七课）介绍使用别名引用被检索的表列
[2]SQL除了可以对列名和计算字段使用别名，还允许给表名起别名

给表名起别名的原因：

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

Vendors表

【1】SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
#对列取别名

Customers表

Orders表

OrderItems表

【2】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';
#FROM子句中的三个表都有别名
#customers AS C使用C作为customers表的别名
#表别名不仅能用于WHERE子句，也可用于SELECT列表，ORDER BY子句以及其它语句部分

注意1：
Oracle中没有AS：
Oracle不支持AS关键字，为在Oracle中使用别名，可以不用AS，简单地指定列名即可，如customers C

注意2：
表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

二、不同的联结类型：

[1]迄今为止，我们使用的只是内联结或等值联结的简单联结
[2]三种其他联结：

• 自联结（self-join）
• 自然联结（natural join）
• 外联结（outer join）

1.自联结

#给与Jim Jones同一公司的所有顾客发送一封信件
#此查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客
方法一： 使用子查询
【3】SELECT cust_id, cust_name, cust_contact FROM customers WHERE cust_name = (SELECT cust_name FROM customers WHERE cust_contact = 'Jim Jones');

#第一种方法使用子查询
#内部的SELECT语句做简单的检索，返回Jim Jones工作公司的cust_name
#cust_name该名字用于外部查询的WHERE子句中，以检索出为该公司工作的所有雇员

方法二： 使用联结的相同查询
【4】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语句中出现两次，为避免引用歧义而使用别名
#SELECT语句使用c1前缀明确给出所需列的全名，否则DBMS将返回错误（DBMS不知道想要的是哪一列）
#WHERE语句首先联结两个表
#然后按第二个表中的cust_contact过滤数据，返回所需的数据。

注意3： 使用自联结而不用子查询
[1]自联结通常作为外部语句，用来替代从相同表中检索数据使用的子查询语句
[2]许多DBMS处理联结远比子查询快得多

2.自然联结

#标准的联结（内联结）返回所有数据，相同的列甚至多次出现
#自然联结排除多次出现，使每一列只返回一次
#自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成

【5】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 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';

#此例中通配符只对第一个表使用，所有其他列明确列出
#没有重复的列被检索出
#迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结

3.外联结

#许多联结将一个表中的行与另一个表中的行相关联，但有时需要包含没有关联行的那些行
#如：对每个顾客下的订单进行计数，包括那些至今尚未下单的顾客
#联结包含了那些在相关表中没有关联行的行，这种联结称为外联结

【6】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 INNER JOIN orders
ON customers.cust_id = orders.cust_id;

#简单的内联结，外联结语法类似
#检索所有顾客及其订单

【7】SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER 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来指定联结的类型-外联结
#与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行
#使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表
#RIGHT指出的是OUTER JOIN右边的表，LEFT指出的是OUTER JOIN左边的表
#上例使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行
#为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN，如下例

【8】SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

#使用RIGHT OUTER JOIN从FROM子句右边的表（Customers表）中选择所有行

注意4： SQLite外联结
SQLite支持LEFT OUTER JOIN但不支持RIGHT OUTER JOIN

注意5： 外联结的类型
[1]两种基本的外联结形式：左外联结和右外联结
[2]调整FROM或WHERE子句中表的顺序，左外联结和右外联结可以相互转换
[3]存在另一种联结：全外联结(full outer join)

全外联结：

#检索两个表中所有的行并关联那些可以关联的行
#与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行
【9】SELECT customers.cust_id, orders.order_num FROM orders FULL OUTER JOIN customers ON orders.cust_id = customers.cust_id;

SELECT customers.cust_id, orders.order_num
FROM orders FULL OUTER JOIN customers
ON orders.cust_id = customers.cust_id;

注意6： FULL OUTER JOIN的支持
Access、MySQL、Open Office Base和SQLite不支持FULL OUTER JOIN语法

三、使用带聚集函数的联结：

#聚集函数用来汇总数据
#聚集函数也可以与联结一起使用

【10】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 INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

#检索所有顾客及每个顾客所下的订单数，使用COUNT()函数
#SELECT语句使用INNER JOIN将Customers和orders表互相关联
#GROUP BY子句按顾客分组数据
#函数调用COUNT(orders.order_num)对每个顾客的订单计数，将它作为num_ord返回

【11】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;

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;

#聚集函数与其他联结一起使用
#使用左外联结来包含所有顾客，甚至包含那些没有任何订单的顾客
#结果中包含顾客100000000002，他有0个订单

四、使用联结和联结条件：

联结及其使用要点：

• 注意所使用的联结类型，一般使用内联结，但使用外联结也有效
• 联结的具体语法，应查阅相应DBMS的具体文档
• 保证使用正确的联结
• 总是提供联结条件，否则会得到笛卡尔积
• 一个联结中可包含多个表，甚至对每个联结采用不同的联结类型。在一起测试它们前分别测试每个联结，可使得排除故障更容易

需要完整代码的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译