Присоединение против подзапроса: что выбрать?

Субзапросы являются логически правильным способом решения задач вида "Получить факты из A, в зависимости от фактов из B". В таких случаях, логичнее разместить B в субзапросе, чем выполнять объединение (join). Это также безопаснее, поскольку вам не нужно переживать о получении дублированных данных из A из-за множества совпадений с B.

Тем не менее, на практике ответ обычно сводится к производительности. Некоторые оптимизаторы плохо работают с объединениями по сравнению с субзапросами, а некоторые — наоборот, и это зависит от конкретного оптимизатора, версии СУБД и спецификации самого запроса.

Исторически сложилось так, что явные объединения обычно выигрывают, поэтому существует мнение, что объединения лучше, но оптимизаторы постоянно улучшаются. Поэтому я предпочитаю сначала писать запросы логически последовательным образом, а затем изменять их структуру, если производительность требует этого.

В большинстве случаев JOIN быстрее, чем подзапросы, и очень редко подзапросы оказываются быстрее.

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

Плюс подзапросов в том, что они более читаемы, чем JOIN: именно поэтому большинство новых пользователей SQL предпочитает их; это простой способ. Однако когда дело доходит до производительности, JOIN в большинстве случаев оказывается лучше, хотя они тоже несложны для чтения.

В 2010 году я бы согласился с автором этого вопроса и проголосовал за JOIN, но с большим опытом (особенно в MySQL) я могу утверждать: да, подзапросы могут быть лучше. Я прочитал множество ответов здесь; некоторые утверждали, что подзапросы быстрее, но не предоставили хорошего объяснения. Надеюсь, я смогу это сделать с этим (очень) запоздалым ответом:

Прежде всего, важно отметить: Существуют разные формы подзапросов.

Второе важное утверждение: Размер имеет значение.

Если вы используете подзапросы, вы должны осознавать, как сервер баз данных выполняет подзапрос. Особенно важно знать, оценивается ли подзапрос один раз или для каждой строки! С другой стороны, современные серверы баз данных способны на многое оптимизировать. В некоторых случаях подзапрос помогает оптимизировать запрос, но новая версия сервера баз данных может сделать эту оптимизацию избыточной.

Подзапросы в выборке (Select-Fields)

SELECT moo, (SELECT roger FROM wilco WHERE moo = me) AS bar FROM foo

Имейте в виду, что подзапрос выполняется для каждой результирующей строки из foo.

Лучше избегать этого, если это возможно; это может значительно замедлить ваш запрос на больших наборах данных. Тем не менее, если подзапрос не имеет ссылки на foo, сервер базы данных может оптимизировать его как статический контент и выполнить только один раз.

Подзапросы в условии Where

SELECT moo FROM foo WHERE bar = (SELECT roger FROM wilco WHERE moo = me)

Если повезет, сервер базы данных оптимизирует это внутрь JOIN. Если нет, ваш запрос станет очень, очень медленным на больших наборах данных, так как он будет выполнять подзапрос для каждой строки в foo, а не только для результатов, как в случае с выборкой.

Подзапросы в условии Join

SELECT moo, bar 
  FROM foo 
    LEFT JOIN (
      SELECT MIN(bar), me FROM wilco GROUP BY me
    ) ON moo = me

Это интересно. Мы комбинируем JOIN с подзапросом. И здесь мы получаем настоящую силу подзапросов. Представьте набор данных с миллионами строк в wilco, но всего лишь несколько уникальных me. Вместо объединения с огромной таблицей, у нас теперь есть меньшая временная таблица для объединения. Это может привести к значительно более быстрым запросам в зависимости от размера базы данных. Тот же эффект можно получить с помощью CREATE TEMPORARY TABLE ... и INSERT INTO ... SELECT ..., что может обеспечить лучшую читаемость на очень сложных запросах (но может заблокировать наборы данных на уровне изоляции повторяемого чтения).

Вложенные подзапросы

SELECT VARIANCE(moo)
  FROM (
    SELECT moo, CONCAT(roger, wilco) AS bar
      FROM foo
      HAVING bar LIKE 'SpaceQ%'
  ) AS temp_foo
  GROUP BY moo

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

Заключение

Подзапросы не являются заменой для JOIN и не следует использовать их в таком формате (хотя это возможно). На мой скромный взгляд, правильное использование подзапроса — это возможность быстрого замещения CREATE TEMPORARY TABLE .... Хороший подзапрос уменьшает набор данных таким образом, который невозможно достичь в выражении ON при использовании JOIN. Если подзапрос имеет одно из ключевых слов GROUP BY или DISTINCT и, желательно, не находится в выборочных полях или в условии where, это может значительно улучшить производительность.

Используйте EXPLAIN, чтобы увидеть, как ваша база данных выполняет запрос на ваших данных. В этом ответе есть большая доля неопределенности...

PostgreSQL может переписывать подзапрос в соединение (JOIN) или соединение в подзапрос, если считает, что один из вариантов быстрее другого. Всё зависит от данных, индексов, корреляции, объема данных, самого запроса и т.д.

Прежде всего, чтобы сравнить две выборки, нужно разделить запросы с подзапросами на:

1. Класс подзапросов, которые всегда имеют эквивалентный запрос, написанный с использованием соединений.
2. Класс подзапросов, которые нельзя переписать с помощью соединений.

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

В наши дни даже MySQL делает это.

Тем не менее, иногда это не так, но это не означает, что соединения всегда выигрывают — у меня были случаи, когда использование подзапросов в MySQL улучшало производительность. (Например, если что-то мешает планировщику MySQL правильно оценить затраты, и если планировщик не видит вариант с соединением и вариант с подзапросом как одинаковые, то подзапросы могут превзойти соединения, заставляя использовать определённый путь).

Вывод в том, что вам следует протестировать ваши запросы как с использованием соединений, так и с подзапросами, если вы хотите быть уверенными, какой из вариантов будет работать лучше.

Для второго класса сравнение не имеет смысла, так как эти запросы нельзя переписать с помощью соединений, и в таких случаях подзапросы являются естественным способом выполнения необходимых задач, и не стоит их недооценивать.