使用SELECT FROM WHERE IN相对于在多个表上使用SELECT FROM的优势

Question

我在学校参加了一门数据库课程。老师给了我们一个简单的练习：考虑下面这个简单的模式：

Table Book:
    Column title (primary key)
    Column genre (one of: "romance", "polar", ...)

Table Author:
    Column title (foreign key on Book.title)
    Column name
    Primary key on (title, name)

在这些问题中，有以下一个：

编写一个查询语句，返回写过浪漫小说的作者。

我提出了以下答案：

select distinct name 
from Author where title in (select title from Book where genre = "romance")

然而老师说这是错误的，正确答案应该是:

select distinct name 
from Book, Author 
where Book.title = Author.title 
  and genre = "romance"

当我寻求解释时，我得到的只是一个"如果你更加关注课程，你就会知道为什么了"。太棒了。

那么，我的答案为什么不正确？这些查询之间到底有什么区别？它们在数据库引擎层面上具体做了什么？

- user703016

---

编辑注意：这不是作业，我需要的是完整答案，而不是提示。 - user703016

这是其中一种情况，有很多答案，但只有一个比其他的更正确。使用子查询通常比先进行连接再限制数据的方式要慢。如果你正在处理拥有成千上万行的表格，则子查询将不够有效率。为了证明它，请查看两个查询的执行计划。当你能阅读执行计划时，你会注意到差异。就我个人而言，我会因老师教授非ANSII标准连接而扣分。 - xQbert

实际上，第一个查询由两个查询组成。问题是：编写查询... - rosco

@rosco：我的意思是数据库会将其视为单个语句。因为从技术上讲，对于DBMS来说，它确实是一个单独的语句。 - user330315

除了因为非标准连接而批评老师外，我对那个模式也有问题；毕竟它违反了规范化的实践。社区的额外任务！考虑以下情况-书籍可能有多个作者，书籍可能属于多个流派。尝试通过仅更新一行来保留更新作者姓名和流派的能力（提示-查看“代理”键）。请修正该模式。 - Clockwork-Muse

Answer 1

那么，我的答案为什么是不正确的？

您的回答是正确的。

我猜测老师标记错误的原因是他/她试图练习使用连接。但如果这是故意的话，那应该是问题的一部分。

这些查询之间到底有什么区别？

从技术上讲，它们确实是不同的。一个具有简单查询优化器的DBMS将以与您老师答案中的连接不同的方式检索子选择。我不会感到惊讶，如果一个具有良好优化器的DBMS实际上可能会为两个查询提供相同的执行计划。

编辑

我创建了一些测试数据，包括50000本书、50000位作者和7种不同的类型（较小的数字并没有真正意义，因为优化器倾向于简单地获取整个表）。该语句将返回7144行。

PostgreSQL

执行计划几乎完全相同，只是“join”方法略有不同。

以下是子选择版本的计划：http://explain.depesz.com/s/eov
以下是连接版本的计划：http://explain.depesz.com/s/aTI

令人惊讶的是，连接版本有一个稍微更高的成本值。

Oracle

两个计划是100%相同的：

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |  6815 |   399K|       |   273   (2)| 00:00:04 |
|   1 |  HASH UNIQUE        |        |  6815 |   399K|   464K|   273   (2)| 00:00:04 |
|*  2 |   HASH JOIN         |        |  6815 |   399K|       |   172   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL| BOOK   |  6815 |   166K|       |    69   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| AUTHOR | 50000 |  1708K|       |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------------

使用autotrace查看统计信息时，也没有任何区别。我没有费心去创建跟踪文件进行分析，因为我不希望在那里看到任何区别。

如果在book.genre上添加索引，事情并不会真正改变。即使有10万行，Oracle仍然坚持进行完整表扫描。可能是因为表不是很宽，许多行适合于单个页面。

PostgreSQL对两个语句都使用索引，但计划之间仍然没有真正的区别。

- user330315

---

只有7个类型是远远不够的，如果它们被均匀分布的话，索引就没有用了。 - Quassnoi

Answer 2

这两个查询语句是有效的并且返回相同的结果。

你的老师使用了过时但仍然有效的连接语法，而你使用的构造在某些数据库（例如MySQL）中不太高效。

如果我是你的老师，我会将查询语句编写为：

SELECT  DISTINCT name
FROM    books b
JOIN    authors a
ON      a.title = b.title
WHERE   b.genre = 'romance'

如果该课程不特定于MySQL优化，则仍会接受您和您的老师的查询。

当老师谈到注意力时，这难道不是他/她的意思吗？

更新：

在数据库引擎层面上，两个查询将被优化以使用相同的执行计划，除非数据库引擎为MySQL。

在MySQL中，您的查询将被强制使用Authors作为领先表，而对于您老师的查询，优化器可以根据表统计信息选择哪个表作为领先表。

- Quassnoi

---

换句话说，显式连接更可取。对吗？ - keyser

@Keyser：从实际角度来看，它们是相同的，但是许多人认为显式连接更易读和更容易调试。 - Quassnoi

我很欣赏你的回答清晰明了！从现在开始我会使用你的语法。如果我可以问一个简单的问题：我使用的语法是从哪里来的？因为我没有在课程中学到它（见：注意力不足），它一定来自其他地方吧？ - user703016

@Cicada：你问我从哪里知道IN？老实说，我不知道！可能你在网上读到过它，或者在源代码中看到过，或者其他什么地方！ - Quassnoi