Теория: Подзапросы и соединения

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

Сравниваем соединения и некоррелированные подзапросы

Иногда мы можем заменить соединение на некоррелированный подзапрос. Одной из таких задач является фильтрация на основе признака, который хранится в связанной таблице. Рассмотрим этот случай на примере.

В базе данных хранится информация о книгах и об авторах:

authors

books

Нам необходимо получить список книг, зная имя автора.

Решение с помощью соединения:

SELECT bk.title
FROM books AS bk
INNER JOIN authors AS au
    ON
        bk.book_author_id = au.author_id
WHERE au.author_name = 'А.С. Пушкин';

https://www.db-fiddle.com/f/c1W2Y73DhvsoS1Nx8qkHVv/1

Логически это решение выполняется так:

1. СУБД строит связь между книгами и авторами.
2. Выполняется фильтрация по имени автора.
3. Из результата возвращается только столбец с названием книги.

Рассмотрим решение с помощью подзапроса:

SELECT bk.title
FROM books AS bk
WHERE bk.book_author_id = (
    SELECT au.author_id
    FROM authors AS au
    WHERE au.author_name = 'А.С. Пушкин'
);

https://www.db-fiddle.com/f/c1W2Y73DhvsoS1Nx8qkHVv/2

Логика этого решения такова:

1. Находим идентификатор нужного нам автора.
2. Отбираем книги с таким же идентификатором.

Логически решение через соединение содержит избыточные действия — СУБД связывает ненужные нам книги с ненужными авторами. Но на практике СУБД выполняет соединения очень эффективно, а оптимизатор может отбросить ненужные строки до связывания.

Так решение через подзапрос лучше, потому что оно экономичнее. Но и решение через соединение тоже достаточно хорошее. На практике разница между ними может быть минимальной.

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

Сравниваем соединения и коррелированные подзапросы

Мы используем соединения, чтобы связать строки из разных таблиц друг с другом. Такую же логику можно реализовать и через подзапросы.

Воспользуемся тем же примером и рассмотрим задачу, когда нам надо построить список книг вместе с их авторами.

Решение с помощью соединения:

SELECT
    bk.title,
    au.author_name
FROM books AS bk
INNER JOIN authors AS au ON
    bk.book_author_id = au.author_id;

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

Решение с помощью подзапроса:

SELECT
    bk.title,
    (
        SELECT au.author_name
        FROM authors AS au
        WHERE au.author_id = bk.book_author_id
    ) AS author_name
FROM books AS bk

https://www.db-fiddle.com/f/c1W2Y73DhvsoS1Nx8qkHVv/3

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

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

Выводы

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

• Коррелированные подзапросы работают хуже соединений на больших объемах данных. Все из-за того, что подзапрос выполняется для каждой строки основного запроса
• В остальных случаях и соединения, и подзапросы показывают себя хорошо