【学习07】MySQL联结查询与组合查询

一、联结查询

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

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

1.1 创建联结

输入：

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.`vend_id` = products.`vend_id`
ORDER BY vend_name,prod_name;

输出：

分析： SELECT语句与前面所有语句一样指定要检索的列。这里，最大的差别是所指定的两个列（prod_name和prod_price）在一个表中，而另一个列（vend_name）在另一个表中。

  现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

可以看到要匹配的两个列以 vendors.vend_id 和 products. vend_id指定。这里需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪一个（它们有两个，每个表中一个）

where 子句的重要性

  在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。（笛卡尔积）

1.2 内部联结

  目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据.

输入：

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.`vend_id` = products.`vend_id`
ORDER BY vend_name,prod_name;

输出：

分析： 此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是FROM子句的组成部分，以INNER
JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同

使用哪种语法 ANSI SQL规范首选INNER JOIN语法。此外，
尽管使用WHERE子句定义联结的确比较简单，但是使用明确的
联结语法能够确保不会忘记联结条件，有时候这样做也能影响
性能

回顾一下上一讲中的例子。该例子如下所示，其SELECT语句返回订购产品TNT2的客户列表

输入：

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 = 'TNT2'));

子查询并不总是执行复杂SELECT操作的最有效的方法，下面是使用联结的相同查询：

输入：

SELECT cust_name, cust_contact
FROM customers,orders,orderitems
WHERE customers.`cust_id` = orders.`cust_id`
	AND orderitems.`order_num` = orders.`order_num`
	AND prod_id = 'TNT2';

输出：

分析： 我们没有在嵌套子查询中使用它们，而是使用了两个联结。这
里有3个WHERE子句条件。前两个关联联结中的表，后一个过滤产品TNT2的数据

1.3 自联结

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法。

输入：

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

输出：

分析： 这是第一种解决方案，它使用了子查询。内部的SELECT语句做
了一个简单的检索，返回生产ID为DTNTR的物品供应商vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品

现在来看使用联结的相同查询：
输入：

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

1.4 外部联结

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

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

下面的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 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 JOIN orders
ON customers.`cust_id` = orders.`cust_id`;

外部联结的类型 存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

1.5 使用带聚集函数的联结

如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

输入：

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

聚集函数也可以方便地与其他联结一起使用。请看下面的例子

输入：

SELECT customers.`cust_name`,customers.`cust_id`,COUNT(orders.`order_num`) AS num_ord
FROM customers LEFT JOIN orders
ON customers.`cust_id` = orders.`cust_id`
GROUP BY customers.`cust_id`;

输出：

分析： 这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单

二、组合查询

  多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）

有两种基本情况，其中需要使用组合查询:

• 在单个查询中从不同的表返回类似结构的数据；
• 对单个表执行多个查询，按单个查询返回数据

组合查询和多个WHERE条件 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

2.1 创建组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成单个结果集

举一个例子，假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。当然，可以利用WHERE子句来完成此工作，不过这次我们将使用UNION

输入：

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION 
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002);

输出：

分析： 这条语句由前面的两条SELECT语句组成，语句中用UNION关键字分隔。UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集

输入：

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
      OR vend_id IN(1001,1002);

在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单

2.2 UNION规则

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。因为供应商1002生产的一种物品的价格也低于5，所以两条SELECT语句都返回该行。在使用UNION时，重复的行被自动取消。

这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

UNION与WHERE UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

2.3 对组合查询结果排序

SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句

下面的例子排序前面UNION返回的结果

输入：

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION 
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002)
ORDER BY vend_id,prod_price;

输出：

分析： 这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。