SELECT FROM WHERE IN по сравнению с SELECT FROM на нескольких таблицах

Я посещаю курс базы данных в своей школе. Учитель дал нам простое упражнение: рассмотрим следующую, простую схему:

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"

Когда я попросил объяснений, все, что у меня было, было "если бы вы больше внимания уделяли курсу, который вы бы знали, почему". Brilliant.

Итак, почему мой ответ неверен? В чем разница между этими запросами? Что именно они делают, на уровне БД?

Ответы

Ответ 1

Итак, почему мой ответ неверен?

Вы отвечаете правильно.

Мое предположение, почему учитель отметил это как ошибочное, что он пытался практиковать использование объединений с этим вопросом. Но это должно было быть частью вопроса, если бы это было предназначено.

В чем разница между этими запросами

Технически они действительно разные. СУБД с простым оптимизатором запросов будет извлекать подзапрос другим способом, чем соединение с ответом вашего учителя.

Я бы не удивился, если бы СУБД с хорошим оптимизатором действительно мог придумать один и тот же план выполнения для обоих запросов.

Изменить

Я создал несколько тестовых данных с 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. Oracle придерживается полного сканирования таблицы (даже с 100000 строк). Вероятно, потому, что таблицы не очень широкие, и много строк подходят на одной странице.

PostgreSQL использует индекс для обоих операторов, но до сих пор нет реальной разницы между планами.

Ответ 2

Оба запроса действительны и возвращают то же самое.

Ваш учитель использует довольно устаревший (хотя и действительный) синтаксис соединения, и вы используете конструкцию, которая менее эффективна в некоторых базах данных (MySQL, например).

Если бы я был вашим учителем, я бы написал запрос следующим образом:

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

но по-прежнему принимайте как ваши, так и ваши запросы преподавателя, если курс не был специфичен для оптимизации MySQL.

Разве это не то, что имел в виду учитель, когда он говорил о том, чтобы обратить внимание?

Update:

На уровне ядра БД оба запроса будут оптимизированы для использования одного и того же плана, за исключением случаев, когда движок БД MySQL.

В MySQL ваш запрос будет вынужден использовать Authors в качестве ведущей таблицы, тогда как для вашего запроса учителя оптимизатор может выбрать, какая таблица должна вести ведущую в зависимости от статистики таблицы.