Мы продолжаем работу с соединениями и переходим к следующему запросу, который продолжает логику предыдущего. Мы используем две таблицы:
ProductSubcategory (PSC) и
Product (P). После соединения применяем фильтр и отбираем только те продукты, у которых категория равна 1. Так как мы используем
INNER JOIN, то получим только те продукты, у которых указана категория. Поскольку мы в итоге фильтруем данные так, чтобы категория равнялась единице, и это значение не может быть NULL, то и LEFT JOIN, и INNER JOIN вернут одно и то же.
Давайте проверим. Выполняем запрос, получаем 97 строк. Убираем фильтр на категорию и видим 295 строк по всем категориям. Но всего товаров у нас 504. Чтобы убедиться в этом, заменяем INNER JOIN на LEFT JOIN для подкатегории и категории. Это разрешает включать в список продукты, у которых не указана подкатегория и категория. Мы получаем 504 записи. Однако, если мы снова применим фильтр
WHERE Category = 1, то опять получим те же 97 строк. С учетом фильтра INNER JOIN и LEFT JOIN дают идентичный результат.
Посмотрим на план исполнения запроса. Оптимизатор выполняет: соединение, использование кластеризованного индекса, сортировку и снова соединение с индексом. Сравним числовые показатели планов для LEFT JOIN и INNER JOIN — они абсолютно одинаковы. Оптимизатор правильно понимает, что оба типа соединения в данном контексте эквивалентны, и убирает лишние действия. Запрос оптимизирован так, что выполняются только необходимые операции.
Влияние фильтрации на тип соединения
Рассмотрим более сложный запрос. Извлекаем: название модели (
ProductModel), название категории, название подкатегории, идентификатор и название продукта. Используем соединения между таблицами и применяем фильтр:
ProductModelID = 23 и
ProductName LIKE '%black%'. Это означает, что мы отбираем продукты 23-й модели, в названии которых где-то встречается слово «black». Результат сортируем по
ProductID. Запрос возвращает 5 продуктов.
Если теперь для таблиц
ProductSubcategory и
ProductCategory использовать LEFT JOIN, результат не изменится, так как фильтр по конкретному
ProductModelID исключает все записи, где эти значения могли бы быть NULL. Планы исполнения с разными JOIN также идентичны. Даже удаление из списка полей
ModelID не влияет на план.
Использование агрегации с соединениями
Переходим к более сложному запросу. Извлекаем:
OrderID,
ProductID,
ProductName и количество заказанных штук (
OrderQuantity). Также вычисляем
LineTotal (произведение цены на количество), округленное до двух знаков. Данные берутся из таблиц
OrderDetail (OD),
Product,
OrderHeader, а затем соединяются с
ProductSubcategory и
ProductCategory. Фильтруем по категории с ID равным 3 и по году заказа 2013. Получаем большой объем данных (более 10 000 строк), так как в заказах много позиций.
Теперь на основе этого запроса сделаем группировку. Мы хотим посчитать сумму штук и общую сумму (
SUM(LineTotal)) для каждого заказа. Убираем из списка полей дату, ID и название продукта. Оставляем
OrderID и применяем агрегатные функции
SUM к
OrderQuantity и
LineTotal. Группируем по
OrderID. Теперь для каждого заказа видна одна строка с суммой штук и общей стоимостью. Мы можем отсортировать по этим суммам и увидеть самые крупные заказы.
Сложный запрос с группировкой и фильтрацией
Рассмотрим запрос, который выводит: название продукта, дату заказа, ID подкатегории, название категории, сумму штук в заказе, а также округленные суммы: стоимость доставки, налоги, промежуточный и общий итоги. Соединяем таблицы
Product,
OrderDetail,
OrderHeader,
ProductSubcategory и
ProductCategory. Фильтруем по подкатегориям с ID 1, 2 или 3, а также по дате заказа за 2013 год. Группируем по всем полям без агрегатной функции. Сортируем по общему итогу.
Внешнее соединение и поиск отсутствующих записей
Переключаемся на базу данных
DW (Data Warehouse). Новый запрос использует опцию
TOP 5, чтобы вернуть только пять строк. Из таблиц
Customer,
FactInternetSales,
Product и
DimGeography извлекаем данные о клиентах и продажах за декабрь 2013 года. С помощью функции
ISNULL заменяем
NULL-значения в поле
MiddleName на пустую строку. Считаем сумму продаж для каждого сочетания параметров, группируем и сортируем по сумме продаж. Опция TOP 5 оставляет только пять записей с наибольшей выручкой.
Ключевое различие между INNER JOIN и LEFT JOIN демонстрируется на примере поиска причин продаж. Запрос соединяет таблицу
DimSalesReason со
FactInternetSalesReason с помощью
LEFT JOIN. Мы хотим найти все причины продаж, по которым не было ни одной транзакции. Для этого в условии
WHERE указываем, что поле
SalesOrderNumber из таблицы фактов должно быть NULL. Если бы использовался INNER JOIN, этот запрос не вернул бы ни одной строки. С LEFT JOIN мы получаем четыре причины, которые ни разу не использовались.
Поиск неактивных адресов и клиентов
Используем LEFT JOIN для поиска почтовых индексов (PostalCode) из таблицы
DimGeography, к которым не привязан ни один покупатель, совершивший покупку. Первый LEFT JOIN соединяет адрес с покупателями, а второй — покупателей с фактами интернет-продаж. Фильтр
WHERE CustomerKey IS NULL оставляет только те адреса, у которых нет клиентов с покупками. Результат — 317 записей в разных странах. Если заменить все соединения на INNER JOIN, запрос вернет пустой результат, так как теперь мы запрашиваем несуществующие NULL-значения в строго внутреннем соединении.
Оператор INTERSECT
Оператор
INTERSECT находит пересечение (общие строки) двух запросов. Создадим два похожих запроса с LEFT JOIN. Первый находит почтовые индексы, по которым не было интернет-продаж. Второй — почтовые индексы, по которым не было розничных продаж (Reseller Sales). Применяем INTERSECT между ними и получаем 41 почтовый индекс. Это адреса, где не было сделано ни одной покупки ни через интернет, ни в розничных магазинах.
Анализ эффективности рекламных акций
LEFT JOIN помогает в анализе эффективности. Запрос извлекает все рекламные акции (EnglishPromotionName) и соединяет их с фактами розничных продаж. LEFT JOIN гарантирует, что мы увидим все акции, даже если по ним не было продаж. Фильтр исключает из списка техническую акцию «No Discount» (отсутствие скидки), которая добавляется для обычных продаж. В результате мы находим четыре рекламные акции, по которым не было ни одной продажи, что позволяет судить об их неэффективности.
Краткие итоги
Анализ материала раскрывает практическую философию работы с соединениями в SQL, смещая фокус с формального заучивания синтаксиса на понимание поведения системы. Ключевая идея заключается в том, что выбор между INNER, LEFT или RIGHT JOIN диктуется не просто желанием «соединить таблицы», а конкретной бизнес-задачей, стоящей за аналитикой данных. Главное разграничение проходит по линии: нужно ли нам найти только полные совпадения или же необходимо выявить «разрывы», отсутствие связей.
На примере поиска рекламных акций без продаж или почтовых индексов без клиентов становится ясно, что LEFT JOIN — это инструмент аудита целостности и полноты данных. Он позволяет задавать вопросы о том, чего нет: какие продукты не покупали, какие причины скидок не использовали. Такая постановка вопроса критически важна для бизнес-анализа, позволяя оценивать эффективность маркетинга или выявлять «мертвые зоны» в логистике. Оператор INTERSECT логически продолжает эту мысль, позволяя находить пересечения таких «негативных» сценариев, усиливая аналитическую строгость.
Отдельно стоит отметить демонстрацию работы оптимизатора запросов. Наблюдение о том, что при определенных фильтрах планы выполнения для INNER и LEFT JOIN идентичны, развенчивает миф о том, что внешние соединения всегда медленнее. Это знание освобождает разработчика от микрооптимизации «на глаз» и заставляет сосредоточиться на семантической правильности запроса. Если по логике задачи NULL-значения в правой таблице все равно отсекаются условием WHERE, оптимизатор сам сведет один тип соединения к другому.
Кульминацией становится синтез соединений с агрегацией. Переход от детального отчета к группировке по заказам показывает путь от сырых данных к управленческой сводке, где многотысячные таблицы сворачиваются в показатели эффективности. Такой подход не просто учит писать запросы, он формирует образ мышления, при котором язык SQL служит не для извлечения строк, а для получения ответов на вопросы, стоящие перед бизнесом.
1. Внешние и внутренние соединения могут давать идентичный результат, если последующая фильтрация исключает все NULL-значения из правой таблицы.
2. LEFT JOIN является основным инструментом для поиска записей, у которых отсутствуют соответствия в связанных таблицах (анализ «разрывов»).
3. При поиске отсутствующих записей необходимо размещать фильтр (WHERE RightTable.Key IS NULL) после условия соединения.
4. Замена LEFT JOIN на INNER JOIN в запросе на поиск несуществующих связей приведет к пустому результату.
5. Оптимизатор SQL Server способен распознавать эквивалентные по результату запросы и строить для них одинаковые планы исполнения.
6. Оператор INTERSECT позволяет находить строки, общие для результатов двух запросов, что удобно для анализа пересекающихся множеств.
7. Агрегатные функции (SUM) в сочетании с GROUP BY преобразуют детальные записи в сводные показатели по группам.
8. Функция ISNULL заменяет NULL-значения на указанное значение, делая вывод данных более читаемым.
9. Использование LEFT JOIN необходимо для анализа полноты справочников, например, поиска причин скидок или рекламных акций без продаж.
10. Сложность запроса не всегда пропорциональна времени его выполнения благодаря работе оптимизатора.
11. Для получения объективной картины по эффективности акций следует исключать технические записи (например, «No Discount»).
12. Тип соединения определяется семантикой задачи («все записи с одной стороны и подходящие с другой» или «только полные совпадения»).
1. Почему при добавлении фильтра WHERE Category = 1 результаты INNER JOIN и LEFT JOIN могут быть абсолютно идентичны?
2. В чем заключается основная задача LEFT JOIN, которую нельзя решить с помощью INNER JOIN?
3. Как составить запрос с LEFT JOIN, чтобы найти все продукты, которые никогда не были заказаны?
4. Объясните, почему запрос на поиск почтовых индексов без клиентов возвращает пустой результат при замене LEFT JOIN на INNER JOIN.
5. Для чего используется оператор INTERSECT и какую аналитическую задачу он решает?
6. Какую роль играет функция ISNULL(MiddleName, '') в формировании результирующего набора данных?
7. Опишите процесс, при котором оптимизатор запросов может сделать план выполнения для LEFT JOIN таким же, как для INNER JOIN.
8. Какие поля необходимо включить в секцию GROUP BY, если в SELECT присутствуют и агрегатные функции, и обычные поля?
9. Почему для поиска рекламных акций, по которым не было продаж, используется LEFT JOIN, а не INNER JOIN?
10. Как отфильтровать записи, чтобы увидеть только те причины продаж, которые ни разу не использовались?
11. В чем разница между фильтрацией по полю правой таблицы в условии WHERE и в условии ON при LEFT JOIN?
12. Каким образом можно объединить данные из нескольких таблиц фактов и справочников для получения суммированных продаж по конкретной группе товаров за период?
