《MySQL必知必会》读书笔记第十六章创建高级联结

使用表别名

别名除了用于列名和计算字段外，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 = 'TNT2';

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

使用不同类型的联结

除了内部联结外，还有自联结、自然联结和外部联结。

自联结

查询某物品的供应商生产的其他物品，子查询如下：

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');

使用联结的查询（多次使用相同的表）：

SELECT p2.prod_id, p2.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p1.prod_id = 'DTNTR';

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

自然联结排除多次出现的列（一般是被联结的列），使每个列只返回一次。

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

外部联结

一般联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的客户。

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

检索所有客户，包括那些没有订单的客户：

1
2
3

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

使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所
有行，应该使用RIGHT OUTER JOIN。

使用带聚集函数的联结

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

SELECT customers.cust_name,
       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_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers LEFT INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

使用联结和联结条件

关于联结及其使用的要点：

注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
保证使用正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡尔积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排查更为简单。