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

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"


当我要求解释时,我所得到的只是“如果您对课程有更多的关注,您会知道为什么”。辉煌。

那么,为什么我的答案不正确?这些查询之间到底有什么区别?在数据库引擎级别上,它们究竟是做什么的?

最佳答案

那么,为什么我的答案不正确?


您的回答是正确的。

我想老师为什么要标记为错误,他/她尝试练习使用带有该问题的联接。但这是否应该成为问题的一部分。


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


从技术上讲,它们的确不同。具有简单查询优化器的DBMS将以不同于教师答案中联接的方式检索子选择。

如果具有良好优化程序的DBMS实际上针对两个查询提出相同的执行计划,我不会感到惊讶。

编辑

我创建了一些测试数据,其中包含50000本书,50000作者和7种不同类型的测试数据(较小的数字实际上并没有什么意义,因为优化器倾向于随后简单地抓住整个表)。该语句将返回7144行。

PostgreSQL的

执行计划几乎相同,只是对“ join”方法进行了一些小的更改。

这是次选择版本的计划:http://explain.depesz.com/s/eov
这是加入版本的计划:http://explain.depesz.com/s/aTI

出人意料的是,加入版本的成本价值略高。

甲骨文

两种计划都是100%相同的:

-------------------------------------------------- ------------------------------------
| ID |操作|姓名|行|字节| TempSpc |费用(%CPU)|时间|
-------------------------------------------------- ------------------------------------
| 0 |选择声明| 6815 | 399K | | 273(2)| 00:00:04 |
| 1 |哈希唯一| | 6815 | 399K | 464K | 273(2)| 00:00:04 |
| * 2 |哈希联接| | 6815 | 399K | | 172(2)| 00:00:03 |
| * 3 |表访问已满|预订6815 | 166K | | 69(2)| 00:00:01 |
| 4 |表访问已满|作者| 50000 | 1708K | | 103(1)| 00:00:02 |
-------------------------------------------------- ------------------------------------


查看使用autotrace时的统计信息,也没有任何区别。我没有真正创建跟踪文件进行分析的麻烦,因为我不希望在那里看到任何区别。

如果添加了book.genre上的索引,事情并不会真正改变。 Oracle坚持全表扫描(即使有100000行)。可能是因为表格不是很宽,并且很多行都放在一个页面上。

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

关于sql - 与多个表上的SELECT FROM相比,SELECT FROM WHERE IN,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10652168/

10-11 17:34