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

При внутреннем соединении двух таблиц соединение возвращает строку из одной таблицы столько раз, сколько есть совпадений в другой таблице. Но для некоторых запросов важна только информация о наличии совпадения, а не о количестве совпадений. Предположим, что есть таблицы с названиями class и roster, которые содержат список курсов по учебному плану и списки студентов (студенты, записавшиеся на каждый курс), соответственно. Чтобы отобразить курсы, на которые действительно записаны студенты, можно использовать следующее соединение:

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

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

Предполагая, что class_num является первичным ключом в таблице class, подавление дубликатов возможно с помощью SELECT DISTINCT, но неэффективно сначала сгенерировать все соответствующие строки, а затем удалить дубликаты.

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

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

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

Следующее утверждение, содержащее предикат подзапроса EXISTS, эквивалентно предыдущему утверждению, содержащему предикат подзапроса IN:

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

Любое утверждение с предикатом подзапроса EXISTS преобразуется теми же преобразованиями семисоединения, что и утверждение с эквивалентным предикатом подзапроса IN.

Следующие подзапросы преобразуются в антисоединения:

Короче говоря, любое отрицание подзапроса вида IN (SELECT ... FROM ...) или EXISTS (SELECT ... FROM ...) преобразуется в антисоединение.

Антисоединение — это операция, возвращающая только строки, для которых нет совпадений. Рассмотрим показанный здесь запрос:

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

Этот запрос переписывается внутри как антисоединение SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num, которое возвращает один экземпляр каждой строки в class, который не совпадает ни с одной строкой в roster. Это означает, что для каждой строки в class, как только будет найдено совпадение в roster, строка в class может быть отброшена.

Преобразования антисоединения в большинстве случаев не могут быть применены, если сравниваемые выражения могут быть NULL. Исключением из этого правила является то, что (... NOT IN (SELECT ...)) IS NOT FALSE и его эквивалент (... IN (SELECT ...)) IS NOT TRUE могут быть преобразованы в антисоединения.

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

В MySQL подзапрос должен удовлетворять этим критериям, чтобы он обрабатывался как семисоединение (или как антисоединение, если NOT изменяет подзапрос):

Если подзапрос соответствует приведенным выше критериям, MySQL преобразует его в семисоединение (или в антисоединение, если применимо) и делает основанный на стоимости выбор из этих стратегий:

Каждая из этих стратегий может быть включена или отключена с помощью следующих флагов системной переменной optimizer_switch:

Эти флаги включены по умолчанию. См. раздел 10.9.2 «Переключаемые оптимизации».

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

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

Оптимизатор сначала рассматривает представление и преобразует подзапрос IN в полусоединение, затем проверяет, можно ли объединить представление с внешним запросом. Поскольку модификатор STRAIGHT_JOIN во внешнем запросе предотвращает полусоединение, оптимизатор отказывается от объединения, вызывая оценку производной таблицы с использованием материализованной таблицы.

Вывод EXPLAIN указывает на использование стратегий полусоединения следующим образом:

Преобразование полусоединения также может быть применено к оператору UPDATE или DELETE с одной таблицей, который использует предикат подзапроса [NOT] IN или [NOT] EXISTS, при условии, что оператор не использует ORDER BY или LIMIT, и что преобразования полусоединения разрешены подсказкой оптимизатора или настройкой optimizer_switch.