Передовые методы соединения: боковые соединения, полусоединения, антисоединения.

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

# Введение

Операторы INNER JOIN и LEFT JOIN подходят для большинства SQL-запросов. Для решения меньшего класса задач требуются другие типы соединений : подсчет результатов функций, возвращающих множество, построчно, фильтрация строк по наличию в другой таблице и возврат строк, не имеющих соответствия в другой таблице.

Три менее распространенных типа соединений (join) корректно обрабатывают эти запросы. LATERAL-соединения позволяют подзапросу в предложении FROM ссылаться на столбцы, расположенные ранее в том же предложении FROM. Semi-соединения возвращают строки, где существует совпадение в другой таблице, без дублирования этих строк. Anti-соединения возвращают строки, где совпадения нет.

Давайте рассмотрим, как применять эти модели на практике.

# Боковые соединения

Подзапрос с оператором LATERAL в предложении FROM может ссылаться на столбцы из предыдущих таблиц в том же предложении FROM. Без оператора LATERAL подзапрос в предложении FROM оценивается независимо и не может видеть эти столбцы.

Это особенно важно при вызове функции, возвращающей множество (функции, которая возвращает несколько строк на вход). Функции, возвращающие множество, можно вызывать в списке SELECT, но для их применения построчно к столбцу из внешней таблицы внутри предложения FROM требуется LATERAL.

Типичные случаи:

• Вызов функции unnest() для столбца массива позволяет получить по одной строке для каждого элемента массива.
• Вызов функции regexp_matches() с флагом 'g' для извлечения всех совпадений в каждой строке.
• Вычисление результата «топ-N» для каждой группы с помощью коррелированного подзапроса в FROM
• Разделение JSON-массивов по строкам

// Пример: Подсчет количества вхождений слов

В этом вопросе от Google нас просят подсчитать, сколько раз слова «bull» и «bear» встречаются в содержании столбца. Совпадения должны быть нечувствительны к регистру, а подстроки, такие как bullish или bearing, следует исключить.

Данные : таблица google_file_store содержит:

Код : функция regexp_matches() возвращает одну строку на каждое совпадение. Чтобы запустить её один раз для каждой строки таблицы google_file_store и подсчитать все совпадения по всей таблице, мы помещаем её в предложение FROM с оператором LATERAL. Якоря m и M — это границы слов в PostgreSQL , которые исключают слова «bullish» и «bearing».

SELECT 'bull' AS word, COUNT(*) AS nentry FROM google_file_store, LATERAL regexp_matches(LOWER(contents), 'm(bull)M', 'g') UNION ALL SELECT 'bear' AS word, COUNT(*) AS nentry FROM google_file_store, LATERAL regexp_matches(LOWER(contents), 'm(bear)M', 'g');

// Выход

# Полусоединения

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

Две реализации SQL:

• ГДЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ …)
• ГДЕ col В (SELECT col FROM …)

EXISTS — это более общая форма, поскольку она обрабатывает условия объединения столбцов и коррелированные подзапросы без переписывания запроса.

// Пример: Поиск ценных клиентов

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

Данные : Предварительный просмотр данных о клиентах онлайн-магазина и заказах онлайн-магазина:

Код : Подзапрос EXISTS проверяет для каждого клиента, существует ли заказ на сумму более 100 долларов. Использование SELECT 1 является общепринятым, поскольку EXISTS интересует только наличие возвращаемой строки, а не её содержимое.

SELECT c.customer_id, c.customer_name FROM online_store_customers c WHERE EXISTS ( SELECT 1 FROM online_store_orders o WHERE o.customer_id = c.customer_id AND o.amount > 100 );

Если бы мы использовали INNER JOIN, клиент 1 появился бы в результате дважды, поскольку два заказа совпадают. EXISTS возвращает клиента 1 один раз.

// Выход

# Анти-соединения

Анти-соединение возвращает строки из левой таблицы, для которых нет совпадений в правой таблице. Это обратная операция по отношению к полу-соединению.

Две реализации SQL:

• LEFT JOIN … WHERE right_table.col IS NULL
• ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ …)

Оба варианта дают одинаковый результат. В современных версиях PostgreSQL оператор NOT EXISTS часто обеспечивает лучший план запроса и позволяет читать данные более непосредственно. Шаблон LEFT JOIN + IS NULL более старый и полезен, когда вам также нужны столбцы с правой стороны для строк, не совпадающих по значению.

// Пример: Бесплатные пользователи без звонков в апреле

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

Данные : Предварительный просмотр rc_calls и rc_users:

Код : Фильтр по дате находится в предложении ON, а не WHERE. Именно это различие делает данное соединение анти-JOIN. Если бы фильтр по дате был в предложении WHERE, были бы удалены строки, где LEFT JOIN выдал значения NULL, и соединение было бы сведено к INNER JOIN. При использовании фильтра в предложении ON, бесплатные пользователи, не имеющие соответствующих условий для участия в программе April JOIN, всё равно создавали бы строку со значениями NULL справа, и проверка IS NULL сохраняла бы только эти строки.

SELECT DISTINCT u.user_id FROM rc_users u LEFT JOIN rc_calls c ON u.user_id = c.user_id AND c.call_date BETWEEN '2020-04-01' AND '2020-04-30' WHERE u.status = 'free' AND c.user_id IS NULL;

// Выход

# Заключение

Эти три оператора объединения решают проблемы, возникающие в случаях, когда операторы INNER JOIN и LEFT JOIN неудобны или некорректны:

• LATERAL — это способ вызова функций, возвращающих множество, построчно внутри функции FROM.
• Функция EXISTS позволяет получить «строки с совпадением» без дублирования, которое возникает при использовании INNER JOIN.
• Операторы NOT EXISTS или LEFT JOIN + IS NULL корректно отображают «строки, для которых нет совпадений».

Главное правило — запомнить короткий шаблон. Если INNER JOIN дублирует ненужные строки, используйте EXISTS. Если нужны строки, для которых нет совпадений, используйте NOT EXISTS или LEFT JOIN + IS NULL. Если подзапрос в FROM должен ссылаться на столбцы из внешней таблицы, добавьте LATERAL.

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

Нейт Розиди — специалист по анализу данных и продуктовой стратегии. Он также является адъюнкт-профессором, преподающим аналитику, и основателем StrataScratch, платформы, помогающей специалистам по анализу данных готовиться к собеседованиям с помощью реальных вопросов от ведущих компаний. Нейт пишет о последних тенденциях на рынке труда, дает советы по прохождению собеседований, делится проектами по анализу данных и освещает все аспекты SQL.

Источник: www.kdnuggets.com