SQL Story摘录（二）————联接查询初探

例1-2、键值重复的信息
现在看一下压缩掉重复信息的PRODUCT表
ID PNAME PRICE NUMBER PDESCRIPTION
1Apple 123000NULL
2Banana 16.997600NULL
3Olive 25.224500NULL
4Coco Nut 40.992000NULL
4Orange 15.995500NULL
5Pineapple 302500NULL
6Olive 25.223000NULL

这里还有几个有问题的地方。表中Coco Nut和Orange的ID都是4，ID号为3和6的两种商品的品名（PNAME）都是Olive。而我们的原意显然是想要让每一种商品对应一个ID号，而且表中的ID号和PNAME都应该是唯一的。这个表中只有7行，我们可以直接观察，用肉眼发现问题，表中数据量很大时呢？
现在我们回顾一下例1中查询重复数据的语句。我们用
……
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
对数据集进行了分组，并用
HAVING COUNT(*) > 1
过滤出了重复的数据，依此类推，单独对ID列进行分组和过滤，能否找出ID重复的数据呢？试一试：
SELECT ID
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
返回结果：
ID
-----------
4
这样倒是出现了我们所要的ID号，可这种报表实在没什么实际意义，现在我们查一下这个ID到底是谁：
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
这条语句执行出错，很显然，ID号之后的四列既不在GROUP BY中，也是统计函数，它们不应该出现在这里。而这样的语句：
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
HAVING COUNT(*) > 1
返回的是一个空结果集：
ID PNAME PRICE NUMBER PDESCRIPTION
----------- -------------------- --------------------- ----------- ------------------------------------

 

（所影响的行数为 0 行）
很多朋友用子查询
SELECT ID, PNAME, PRICE, NUMBER, PDESCRIPTION
FROM PRODUCT
WHERE ID IN (
SELECT ID
FROM PRODUCT
GROUP BY ID
HAVING COUNT(*) > 1
)
来解决，我还见过一个用二级游标的例子（！？），那么没有更好的办法了吗？
我更喜欢以下这行语句：
SELECT L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
GROUP BY L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
返回结果如下：
ID PNAMEPRICE NUMBER PDESCRIPTION
4Coco Nut40.992000NULL
4Orange 15.995500NULL

使用联接查询，速度会比子查询快很多，因为不用每次用IN操作在子语句中的结果集中搜索数据。尤其当表中数据很多，返回的结果集也很大时，其差异是相当惊人的。如果在多处理器，多硬盘的服务器上运行，联接查询还可以充分利用并行运算来提高效率。1999年夏天，IBM公司的工程师们在兰州大学出席全国数据库技术会议时，向我们讲解了运用并行运算技术优化联接查询所带来的性能飚升。相比之下，子查询在这方面有点吃亏。有些强大的数据库引擎会在适当的时候将子查询转化为联接查询，或反之。但把真理掌握在我们自己手中，不是更好吗？
当然，子查询并不是一定比联接慢，有机会我也会演示一些子查询快于联接查询的例子，甚至有些子查询语句，用联接是很难实现的。理论来讲，联接查询会生成一个迪卡尔积，这个集合的大小是组成它的各个子集的乘积。这会带来空间上的巨大开销（实际我们所见的数据库系统没有一个真这么干的）。而子查询的情况比较复杂。由生成的结果集来分，有标量子查询和向量子查询，（标量子查询指返回一个简单数据的查询，这种子查询语句在MS SQL Server中可以直接做为外部查询语句的一列）；由子查询与外部查询的关系来分，有相关子查询和非相关子查询（相关子查询的结果集取决于外部查询当前的数据行，非相关子查询反之）。通常相关子查询比较让人头痛，它需要反复执行子查询语句，若外部查询操作的数据集（不是返回的数据集）行数为n，子查询操作的数据集行数为m，那它的复杂度最大将是m的n次方！加上子查询数据集展开带来的巨大空间开销，会极大影响速度。上例中的子查询比较幸运，是一个无关的向量子查询，但即使如此，也要在运算中保存一个子结果集并对其反复操作，而且难以并行运算，结果是它的速度不会比联接查询快。这也就是MySQL在很长时间里一直不支持子查询的原因。在通常情况下，大数据集的操作，联接查询的性能总是优于子查询，所以我们应当充分掌握这一方法。
以例2中最终的联接查询为例，我们分析一下编写这种联接查询的思路。前面提到，在理论上，联接数据集时，会生成一个迪卡尔积。如果有一个表T的内容如下：
Word
----
a
b
那么执行“SELECT L.Word, R.Word FROM T AS L JOIN T AS R ON L.Word = R.Word”时，会先生成
L.WordR.Word
a a
a b
ba
bb

然后再执行“ON L.Word = R.Word”，将其过滤为
L.WordR.Word
a a
bb

在这里，我们就利用中间这个迪卡尔积做文章。如果PRODUCT表的ID列中数据的确是唯一的，那对它做自联接后，就应该像刚才看到的T表Word列一样，结果集中的ID也仍然保持唯一。现在我们执行这个语句试试：
SELECT L.ID, R.ID， L.PNAME, R.PNAME
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
结果如下：
IDIDPNAMEPNAME
11AppleApple
22BananaBanana
33OliveOlive
44OrangeOrange
44Coco nutOrange
44OrangeCoco nut
44Coco nutCoco nut
55PineapplePineapple
66OliveOlive

注意到了吗？原本重复两次的ID号4，现在，重复了4次。这是由于Coco Nut和Orange两行ID号重复，迪卡尔积为其平方，无法为联接条件过滤。所以，我们对这个结果集按其中一个子集的ID字段和另一个子集的其它字段进行分组后，ID为4的数据被分为两组，每组两行，而正常数据每组仅为一行。就可以找出ID重复的数据，甚至我们还可以知道它重复了几次！请看下面的SQL语句：
SELECT L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION, COUNT(*) ROW_COUNT
FROM PRODUCT L
JOIN PRODUCT R
ON L.ID = R.ID
GROUP BY L.ID, R.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
返回结果：
IDPNAMEPRICENUMBERPDESCRIPTIONROW_COUNT
4Coco nut40.992000NULL2
4Orange15.995500NULL2

（所影响的行数为 2 行）
这种结构上的可扩展性也同样是子查询所不及的，同时它也会带来一些有趣的附效应，有好有坏，这就要到后面的章节讨论了。
同样，用
SELECT R.ID, L.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION, COUNT(*) ROW_COUNT
FROM PRODUCT L
JOIN PRODUCT R
ON L.PNAME = R.PNAME
GROUP BY R.ID, L.PNAME, R.PRICE, R.NUMBER, R.PDESCRIPTION
HAVING COUNT(*) > 1
语句，就可以找出PNAME列重复的数据及其重复次数：
ID PNAME PRICE NUMBER PDESCRIPTION ROW_COUNT
3Olive 25.224500NULL 2
6Olive 25.223000NULL 2

（所影响的行数为 2 行）
经过上面的两个例子，我们可以看到，更深入地了解关系型数据库的运行机制，熟练运用简单查询和联接查询，可以有效地提高程序的性能及可维护性，降低代码复杂度。何乐而不为呢？
InterBase中没有Money数据类型，所以在InterBase中创建PRODUCT表时，记得把PRICE字段定义为其它的类型，这里我用的是NUMERIC（8，4）。另外，InterBase中还有一个问题，执行以下语句删除重复数据时：
DELETE FROM PRODUCT
WHERE ID IN (SELECT ID
FROM PRODUCT
GROUP BY ID, PNAME, PRICE, NUMBER, PDESCRIPTION
HAVING COUNT(*) > 1)
理论来讲，它应当把两行“Apple”全都删掉，MS SQL Server2000就是这样做的。可它只删掉了其中一行！执行后的表中数据如下：
IDPNAMEPRICENUMBERPDESCRIPTION
1Apple12.00003000NULL
2Banana16.99007600NULL

显然，它在删除了一行数据后，又重新查询了数据表并重新决定下一行要删除的数据。对于关系型数据库来说，这不是一个好事，既不严谨，也不优美。不过具体到这个语句，倒是一个好事，我们只用一条删除命令就完成了本应分几次操作的数据合并。InterBase中，还有类似的一些地方，并没有像MS SQL Server那样，实现真正的集合操作。在以后的例子中我会随时提到。在实际工作中大家也要注意。
我使用的InterBase 6.0.1是一个可以免费获得的开放源码的数据库，而MS SQL Server是微软的掌上明珠，MS SQL Server7的设计者曾获得1998年的图灵奖。我不得不承认这个轻巧快捷的InterBase是一个让人赞叹的好东西，它实现了诸如级联更新这样一些MS SQL Server直至2000版才加入的强大功能，当然它也有其不尽如人意之处。不过考虑其性价比，我们真的不能要求更多了。另外建议新手借此机会了解一下临时表的使用。这里不多占篇幅了。

原文转自：http://www.ltesting.net