_13_ 创建高级联结
本文DEMO
https://gitee.com/changsanjiang/SQLDemo/tree/master
下面我们介绍如何使用表别名, 如何对联结的表使用聚集函数.
使用表别名
SQL除了可以对列名和计算字段使用别名, 还允许给表名起别名. 这样做有两个主要理由:
- 缩短SQL语句.
- 允许在一条SELECT语句中多次使用相同的表.
请看下面的使用别名的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';
可以看到, FROM子句中的三个表全都有别名. Customers AS C使用C作为Customers的别名, 如此等等.
使用不同的联结
迄今为止, 我们使用的只是内联结(或等值联结). 现在来看三种其他联结:
- 自联结(self-join)
- 自然联结(natural join)
- 外联结(outer join)
自联结
自联结利用表别名在SQL语句对表进行重命名, 像处理两个表一样把表结合到自身.
假如要给与Jim Jones一个公司的顾客发送一封信件. 这个查询要求首先找出Jim Jones所在的公司, 然后找出该公司的顾客. 下面是解决此问题的一种方法:
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
输出:
image.png
我们来看使用联结的相同查询:
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 表.
解决此问题, 需要使用别名. 分别是 c1, c2. SELECT 语句使用 c1 前缀明确给出所需列的全名. 如果不这样, DBMS 将返回错误, 因为cust_id, cust_name, cust_contact 的列各有两个. DBMS 不知道想要的是哪一列(即使他们其实是同一列). WHERE 首先联结两个表, 然后按第二个表中的 cust_contact 过滤数据, 返回所需数据.
提示: 用自联结而不用子查询
自联结通常作为外部语句, 用来替代从相同表中检索数据的使用子查询语句. 虽然最终结果是相同的, 但许多 DBMS 处理联结远比处理子查询快得多. 应该试一下两种方法, 以确定哪一种性能更好.
自然联结
无论何时对表进行联结, 应该至少有一列不止出现在一个表中(被联结的列). 标准的联结(前一篇介绍的内联结)返回所有数据, 相同的列甚至出现多次. 自然联结排除多次出现, 使每一列返回一次.
怎样完成这项工作呢? 答案是, 由你来完成它. 自然联结要求你只能选择那些唯一的列, 一般通过对一个表使用通配符(SEELCT *), 而对其他表的列使用明确的子集来完成. 下面举一个例子:
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';
输出:
image.png
分析:
在这个例子中, 通配符只对第一个表使用. 所有其他列明确列出, 所以没有重复的列被检索出来.
事实上, 我们迄今为止建立的每个内联结都是自然联结, 很可能永远都不会用到不是自然联结的内联结.
外联结
许多联结将一个表中的行与另一个表中的行相关联, 但有时候需要包含没有关联行的那些行. 例如, 可能需要使用联结完成以下工作:
- 对每个顾客下的订单进行计数, 包括那些至今尚未下订单的顾客;
- 列出所有产品以及订购数量, 包括没有人订购的产品;
- 计数平均销售规模, 包括那些至今尚未下订单的顾客.
在上述例子中, 联结包含了那些在相关表中没有关联行的行. 这种联结称为外联结.
下面的SELECT语句给出了一个简单的内联结. 它检索所有顾客及其订单:
输入:
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;
image.png
分析;
与内联结关联两个表中的行不同的是, 外联结会返回一个表里的全部记录, 即使对应的记录在第二个表里为NULL. 在使用 OUTER JOIN 语法时, 必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表(RIGHT 指出的是 OUTER JOIN 右边的表, 而 LEFT 指出的是 OUTER JOIN 左边的表). 上面的例子使用 LEFT OUTER JOIN 从 FROM子句左边的 Customers 表中选择所有行. 为了从右边的表中选择所有行, 需要使用 RIGHT OUTER JOIN. 如下所示:
-- SQLite 不支持 RIGHT OUTER JOIN
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;
使用带聚集函数的联结
前面我们使用聚集函数来汇总数据. 至今为止我们使用聚集函数时都是在一个表中汇总数据, 但这些函数也可以与联结一起使用.
我们来看个例子, 要检索所有顾客及每个顾客所下的订单数, 下面的代码使用COUNT()函数完成此工作:
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;
输出:
image.png
分析:
这里的SELECT语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联. GROUP BY 子句按顾客分组, 因此函数调用 COUNT(Orders.order_num)对每个顾客的订单计数, 将它作为 num_ord 返回.
聚集函数也可以方便地与其他联结一起使用. 请看下面的例子:
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;
输出:
image.png
我们汇总一下联结及其使用要点:
- 注意所使用的联结类型. 一般我们使用内联结, 但使用外联结也有效.
- 关于确切的联结语法, 应该查看具体的文档, 看相应的DBMS支持何种语法.
- 保证使用正确的联结条件(不管采用哪种语法), 否则会返回不正确的数据.
- 应该总是提供联结条件, 否则会得出笛卡儿积.
- 在一个联结中可以包含多个表, 甚至可以对每个联结采用不同的联结类型. 虽然这样做是合法的, 一般也很少用, 但应该在一起测试他们前分别测试没个联结. 这会使规则排除更为简单.
