Sql t join: Joins (SQL Server) — SQL Server

JOIN | Документация ClickHouse

1. Справка по SQL
2. Выражения
3. SELECT

Join создаёт новую таблицу путем объединения столбцов из одной или нескольких таблиц с использованием общих для каждой из них значений. Это обычная операция в базах данных с поддержкой SQL, которая соответствует join из реляционной алгебры. Частный случай соединения одной таблицы часто называют «self-join».

Синтаксис:

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

Выражения из секции ON и столбцы из секции USING называется «ключами соединения». Если не указано иное, при присоединение создаётся Декартово произведение из строк с совпадающими значениями ключей соединения, что может привести к получению результатов с гораздо большим количеством строк, чем исходные таблицы.

Поддерживаемые типы соединения

Все типы из стандартого SQL JOIN поддерживаются:

• INNER JOIN, возвращаются только совпадающие строки.
• LEFT OUTER JOIN, не совпадающие строки из левой таблицы возвращаются в дополнение к совпадающим строкам.
• RIGHT OUTER JOIN, не совпадающие строки из правой таблицы возвращаются в дополнение к совпадающим строкам.
• FULL OUTER JOIN, не совпадающие строки из обеих таблиц возвращаются в дополнение к совпадающим строкам.
• CROSS JOIN, производит декартово произведение таблиц целиком, ключи соединения не указываются.

Без указания типа JOIN подразумевается INNER. Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является ли указание нескольких таблиц, разделённых запятыми, в секции FROM.

Дополнительные типы соединений, доступные в ClickHouse:

• LEFT SEMI JOIN и RIGHT SEMI JOIN, белый список по ключам соединения, не производит декартово произведение.
• LEFT ANTI JOIN и RIGHT ANTI JOIN, черный список по ключам соединения, не производит декартово произведение.
• LEFT ANY JOIN, RIGHT ANY JOIN и INNER ANY JOIN, Частично (для противоположных сторон LEFT и RIGHT) или полностью (для INNER и FULL) отключает декартово произведение для стандартых видов JOIN.
• ASOF JOIN и LEFT ASOF JOIN, Для соединения последовательностей по нечеткому совпадению. Использование ASOF JOIN описано ниже.

Настройки

Примечание

Значение строгости по умолчанию может быть переопределено с помощью настройки join_default_strictness.

Поведение сервера ClickHouse для операций ANY JOIN зависит от параметра any_join_distinct_right_table_keys.

Использование ASOF JOIN

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

Для работы алгоритма необходим специальный столбец в таблицах. Этот столбец:

• Должен содержать упорядоченную последовательность.
• Может быть одного из следующих типов: Int, UInt, Float*, Date, DateTime, Decimal*.
• Не может быть единственным столбцом в секции JOIN.

Синтаксис ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

Можно использовать произвольное количество условий равенства и одно условие на ближайшее совпадение. Например, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

Условия, поддержанные для проверки на ближайшее совпадение: >, >=, <, <=.

Синтаксис ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, . .. equi_columnN, asof_column)

Для слияния по равенству ASOF JOIN использует equi_columnX, а для слияния по ближайшему совпадению использует asof_column с условием table_1.asof_column >= table_2.asof_column. Столбец asof_column должен быть последним в секции USING.

Например, рассмотрим следующие таблицы:

     table_1                           table_2
  event   | ev_time | user_id       event   | ev_time | user_id
----------|---------|----------   ----------|---------|----------
              ...                               ...
event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
              ...                 event_2_2 |  12:30  |   42
event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
              ...                               ...

ASOF JOIN принимает метку времени пользовательского события из table_1 и находит такое событие в table_2 метка времени которого наиболее близка к метке времени события из table_1 в соответствии с условием на ближайшее совпадение. При этом столбец user_id используется для объединения по равенству, а столбец ev_time для объединения по ближайшему совпадению. В нашем примере event_1_1 может быть объединено с event_2_1, event_1_2 может быть объединено с event_2_3, а event_2_2 не объединяется.

Примечание

ASOF JOIN не поддержан для движка таблиц Join.

Чтобы задать значение строгости по умолчанию, используйте сессионный параметр join_default_strictness.

Распределённый join

Есть два пути для выполнения соединения с участием распределённых таблиц:

• При использовании обычного JOIN , запрос отправляется на удалённые серверы. На каждом из них выполняются подзапросы для формирования «правой» таблицы, и с этой таблицей выполняется соединение. То есть, «правая» таблица формируется на каждом сервере отдельно.
• При использовании GLOBAL ... JOIN, сначала сервер-инициатор запроса запускает подзапрос для вычисления правой таблицы. Эта временная таблица передаётся на каждый удалённый сервер, и на них выполняются запросы с использованием переданных временных данных.

Будьте аккуратны при использовании GLOBAL. За дополнительной информацией обращайтесь в раздел Распределенные подзапросы.

Рекомендации по использованию

Обработка пустых ячеек и NULL

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

Если ключами JOIN выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение NULL, не соединяются.

Синтаксис

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

В секции USING указывается один или несколько столбцов для соединения, что обозначает условие на равенство этих столбцов. Список столбцов задаётся без скобок. Более сложные условия соединения не поддерживаются.

Ограничения cинтаксиса

Для множественных секций JOIN в одном запросе SELECT:

• Получение всех столбцов через * возможно только при объединении таблиц, но не подзапросов.
• Секция PREWHERE недоступна.

Для секций ON, WHERE и GROUP BY:

• Нельзя использовать произвольные выражения в секциях ON, WHERE, и GROUP BY, однако можно определить выражение в секции SELECT и затем использовать его через алиас в других секциях.

Производительность

При запуске JOIN, отсутствует оптимизация порядка выполнения по отношению к другим стадиям запроса. Соединение (поиск в «правой» таблице) выполняется до фильтрации в WHERE и до агрегации. Чтобы явно задать порядок вычислений, рекомендуется выполнять JOIN подзапроса с подзапросом.

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

В некоторых случаях это более эффективно использовать IN вместо JOIN.

Если JOIN необходим для соединения с таблицами измерений (dimension tables — сравнительно небольшие таблицы, которые содержат свойства измерений — например, имена для рекламных кампаний), то использование JOIN может быть не очень удобным из-за громоздкости синтаксиса, а также из-за того, что правая таблица читается заново при каждом запросе. Специально для таких случаев существует функциональность «Внешние словари», которую следует использовать вместо JOIN. Дополнительные сведения смотрите в разделе «Внешние словари».

Ограничения по памяти

По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет <right_table> и создает для него хэш-таблицу в оперативной памяти. После некоторого порога потребления памяти ClickHouse переходит к алгоритму merge join.

• max_rows_in_join — ограничивает количество строк в хэш-таблице.
• max_bytes_in_join — ограничивает размер хэш-таблицы.

По достижении любого из этих ограничений, ClickHouse действует в соответствии с настройкой join_overflow_mode.

Примеры

Пример:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

Оператор SQL INNER JOIN: синтаксис, примеры

Оператор SQL INNER JOIN формирует таблицу из записей двух или нескольких таблиц. Каждая строка из первой (левой) таблицы, сопоставляется с каждой строкой из второй (правой) таблицы, после чего происходит проверка условия. Если условие истинно, то строки попадают в результирующую таблицу. В результирующей таблице строки формируются конкатенацией строк первой и второй таблиц.

Оператор SQL INNER JOIN имеет следующий синтаксис:

SELECT
    column_names [,... n]
FROM
    Table_1 INNER JOIN Table_2
ON condition

Условие для сравнения задается в операторе ON.

Примеры оператора SQL INNER JOIN. Имеются две таблицы:

Authors — содержит в себе информацию об авторах книг:

Books — содержит в себе информацию о названии книг:

В таблице Books поле BookID являются внешним ключом и ссылаются на таблицу Authors.

Пример 1. Используя оператор SQL INNER JOIN вывести на экран, какими авторами были написаны какие из книг:

SELECT *
FROM Authors INNER JOIN Books
ON Authors.AuthorID = Books.BookID

В данном запросе оператора SQL INNER JOIN условие сравнения — это равенство полей AuthorID и BookID. В результирующую таблицу не попадет книга под названием Programming in Scala, так как значение ее BookID не найдет равенства ни с одной строкой AuthorID.

Результирующая таблица будет выглядеть следующим образом:

SQL SERVER MULTIPLE JOIN: избегайте повторяющихся значений

У меня есть 3 таблицы в моей БД:-

Покупатель:

 CId | CName | CLocation | CJoinDate

Платеж:

TxnId | TxStatus | TxComment | TxAmount | CId | TxDate

Назначение:

AppId | AppCode | CId | ADate | AComment

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

например:-

Если я попробую этот запрос, то общая сумма, рассчитанная правильно:

SELECT c.CName, sum(p.TxAmount) 
FROM Customer c LEFT JOIN Payment p ON c.CId = p.CId 
WHERE p.TxStatus = 1 
GROUP BY c.CName;

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

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

SELECT c.CName as Name, sum(p.TxAmount) as Payment, count(distinct a.ADate) as Total_Visit 
FROM Customer c LEFT JOIN Payment p ON c.CId = p.CId LEFT JOIN Appointment a ON c.CId = a.CId
WHERE p.TxStatus = 1 
GROUP BY c.CName;

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

Как я могу исправить описанный выше сценарий с помощью приведенного выше запроса?

ТКС

EDIT: на самом деле есть еще 2-3 таблиц, которые я должен join похож на таблицу встреч вместе с предложением GROUP BY для каждого месяца.

EDIT1: исправлено несколькими CTE. Спасибо за ваши ценные указания, это было действительно полезно.

sql-server

join

Поделиться

Источник

Villie    

04 августа 2016 в 10:37

3 ответа

• Получение максимальных значений от SQL Server join

  Я некоторое время боролся с запросом SQL Server и понял, что мне нужно получить помощь от кого-то, кто знает SQL Server (намного) лучше, чем я. Окружающая среда: SQL Server 2008 SELECT t1.SUPPL_ORDERNO, t2.OUR_ORDER, t3.CUST_INVOICE FROM t1 LEFT OUTER JOIN t2 ON t1. SUPPL_ORDERNO = t2.ORDER_REM5…

• SQL Server : JOIN запрос

  Я новичок в SQL Server, и мне нужна помощь с SQL Server и JOIN методом. Мой код таков: SELECT TOP 1000 p.value AS userposition, p2.value AS usercell, t.id FROM [es] t JOIN [user] u ON t.user_uid = u.uid JOIN [user] su ON u.superior_uid = su.uid JOIN [user_params] up ON t.user_uid = up.user_uid…

1

Используйте простое выражение CTE, если вы уверены, что ваша сумма вычисляется правильно по первому запросу

WITH cte AS 
(
SELECT c.CName, c.CID, sum(p.TxAmount) AS sumAmount
FROM Customer c LEFT JOIN Payment p ON c.CId = p.CId 
WHERE p.TxStatus = 1 
GROUP BY c.CName, c.CID 
)
SELECT cte.CName, cte.sumAmount, count(distinct a.ADate) as Total_Visit
FROM cte LEFT JOIN Appointment a ON c.CId = a.CId
GROUP BY c.CName, cte.sumAmount

Поделиться

PacoDePaco    

04 августа 2016 в 10:44

0

Попробуйте использовать подзапрос:

SELECT 
c. CName as Name
, sum(p.TxAmount) as Payment
, Total_Visit = (SELECT count(distinct a.ADate) FROM Appointment a ON c.CId = a.CId)
FROM Customer c 
LEFT JOIN Payment p ON c.CId = p.CId 
WHERE p.TxStatus = 1 
GROUP BY c.CName;

Поделиться

Jarle Bjørnbeth    

04 августа 2016 в 10:43

0

Вы можете вычислить total_visit для CId, а затем join подзапроса

SELECT 
  c.CName as Name
, sum(p.TxAmount) as Payment
, Total_Visit 
FROM Customer c 
LEFT JOIN Payment p ON c.CId = p.CId 
LEFT JOIN (SELECT a.CId, count(distinct a.ADate) Total_visit FROM Appointment a group by a.CId) as a on c.CId = a.CId 
WHERE p.TxStatus = 1 
GROUP BY c.CName;

Поделиться

vercelli    

04 августа 2016 в 10:47

• Идентификация повторяющихся значений в словаре и печать в таблице

  У меня есть словарь (d), где каждый ключ может иметь несколько значений (добавленных в виде списка). Например, словарь имеет следующие две пары ключ-значение, где одна имеет повторяющиеся значения, а другая-нет: Конкретных угроз , [‘5 конкретных угроз Майкрософт Windows печати очереди от младшего…

• Create VIEW (подсчет повторяющихся значений в столбце)

  У меня есть небольшой проект с базой данных SQL, в которой есть таблица с каким-то столбцом. Вопрос : Как создать View в SQL Server, который подсчитывает, сколько повторяющихся значений у меня есть в столбце, и показывает это число в следующем столбце. Здесь ниже вы можете увидеть результат,…

Похожие вопросы:

Натуральный join в SQL Server

Есть ли какая-либо поддержка natural join s в последних выпусках Microsoft SQL Server? Или есть хорошая альтернатива для того, чтобы заставить SQL Server выработать предикаты, которые были бы в…

Избегайте Повторяющихся Вставок — SQL Server

У меня есть таблица с 5 столбцами. Я добавил уникальный индекс для 2 столбцов в этой таблице. Однако всякий раз, когда появлялись дубликаты, запрос останавливался с ошибкой. Итак, в MySQL году я…

Условие присоединения в SQL Server 2008

Я хочу динамически join 2 таблицы в SQL Server, что означает в соответствии с количеством значений таблицы, как LEFT join или RIGHT join.. Возможно ли это в SQL Server ? Если да пожалуйста объясните…

Получение максимальных значений от SQL Server join

Я некоторое время боролся с запросом SQL Server и понял, что мне нужно получить помощь от кого-то, кто знает SQL Server (намного) лучше, чем я. Окружающая среда: SQL Server 2008 SELECT…

SQL Server : JOIN запрос

Я новичок в SQL Server, и мне нужна помощь с SQL Server и JOIN методом. Мой код таков: SELECT TOP 1000 p.value AS userposition, p2.value AS usercell, t.id FROM [es] t JOIN [user] u ON t.user_uid =…

Идентификация повторяющихся значений в словаре и печать в таблице

У меня есть словарь (d), где каждый ключ может иметь несколько значений (добавленных в виде списка). Например, словарь имеет следующие две пары ключ-значение, где одна имеет повторяющиеся значения,…

Create VIEW (подсчет повторяющихся значений в столбце)

У меня есть небольшой проект с базой данных SQL, в которой есть таблица с каким-то столбцом. Вопрос : Как создать View в SQL Server, который подсчитывает, сколько повторяющихся значений у меня есть…

Выберите 3 и более повторяющихся строк в SQL Server

Я изучаю SQL Server и запутался, выбирая 3 и более повторяющихся строки в SQL Server. Мне нужно отфильтровать свои данные минимум 3 и более повторяющихся строк с помощью SQL Server, и я не знаю, как…

Сумма без кутинга повторяющихся значений SQL

У меня есть таблица suivis , содержащая столбцы id_action AND id_individu, эта таблица может содержать несколько повторяющихся значений (не всю строку, а только столбец). пример : У меня также есть…

Как join две таблицы на разных значениях столбца?

SELECT table1.* ,address ,job FROM table1 JOIN table2 ON table2.name = table1.name Приведенный выше запрос возвращает результат и для повторяющихся значений name . Как я могу преобразовать запрос,…

SQL Server: присоединяется к

В этом руководстве по SQL Server объясняется, как использовать JOINS , как INNER, так и OUTER JOINS, в SQL Server (Transact-SQL) с синтаксисом, наглядными иллюстрациями и примерами.

Описание

SQL Server (Transact-SQL) СОЕДИНЕНИЯ используются для извлечения данных из нескольких таблиц. SQL Server JOIN выполняется всякий раз, когда две или более таблиц объединяются в операторе SQL.

Существует 4 различных типа соединений SQL Server:

• SQL Server ВНУТРЕННЕЕ СОЕДИНЕНИЕ (или иногда называемое простым соединением)
• SQL Server LEFT OUTER JOIN (или иногда его называют LEFT JOIN)
• SQL Server RIGHT OUTER JOIN (или иногда называется RIGHT JOIN)
• ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ SQL Server (или иногда его называют ПОЛНОЕ СОЕДИНЕНИЕ)

Итак, давайте обсудим синтаксис SQL Server JOIN, посмотрим на визуальные иллюстрации SQL Server JOINS и рассмотрим примеры SQL Server JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже написали инструкцию, в которой используется SQL Server INNER JOIN. Это наиболее распространенный тип соединения. SQL Server INNER JOINS возвращает все строки из нескольких таблиц, в которых выполнено условие соединения.

Синтаксис

Синтаксис INNER JOIN в SQL Server (Transact-SQL):

 ВЫБРАТЬ столбцы
ИЗ table1
INNER JOIN table2
НА table1.column = table2.column; 

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server INNER JOIN возвращает заштрихованную область:

.

SQL Server INNER JOIN вернет записи, в которых пересекаются table1 и table2 .

Пример

Вот пример ВНУТРЕННЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБЕРИТЕ поставщиков.supplier_id, поставщиков.supplier_name, orders.order_date
ОТ поставщиков
INNER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

В этом примере SQL Server INNER JOIN будут возвращены все строки из таблиц поставщиков и заказов, в которых есть совпадающее значение supplier_id как в таблице поставщиков, так и в таблице заказов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ВНУТРЕННИЕ СОЕДИНЕНИЯ:

У нас есть таблица поставщиков с двумя полями (supplier_id и supplier_name).Он содержит следующие данные:

У нас есть еще одна таблица под названием orders с тремя полями (order_id, supplier_id и order_date).Он содержит следующие данные:

Если мы запустим инструкцию SQL Server SELECT (которая содержит INNER JOIN) ниже:

 ВЫБЕРИТЕ поставщиков.идентификатор_ поставщика, имя_поставщика.имя_поставщика, заказ_дата_поставщика
ОТ поставщиков
INNER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Наш набор результатов будет выглядеть так:

Строки для Microsoft и NVIDIA из таблицы поставщиков будут опущены, так как 10002 и 10003 поставщика_id не существуют в обеих таблицах.Строка для 500127 (идентификатор_заказа) из таблицы заказов будет опущена, поскольку идентификатор поставщика 10004 не существует в таблице поставщиков.

Старый синтаксис

В заключение стоит упомянуть, что приведенный выше пример SQL Server INNER JOIN можно переписать с использованием старого неявного синтаксиса следующим образом (но мы по-прежнему рекомендуем использовать синтаксис ключевого слова INNER JOIN):

 ВЫБЕРИТЕ поставщиков.supplier_id, поставщиков.supplier_name, orders.order_date
ОТ поставщиков, заказы
ГДЕ поставщики.поставщик_id = orders.supplier_id; 

ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

Другой тип соединения называется LEFT OUTER JOIN SQL Server. Этот тип соединения возвращает все строки из ЛЕВОЙ таблицы, указанной в условии ON, а — только тех строк из другой таблицы, в которых объединенные поля равны (условие соединения выполнено).

Синтаксис

Синтаксис LEFT OUTER JOIN в SQL Server (Transact-SQL):

 ВЫБРАТЬ столбцы
ИЗ table1
LEFT [OUTER] JOIN table2
НА table1.столбец = table2.column; 

В некоторых базах данных ключевые слова LEFT OUTER JOIN заменяются на LEFT JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server LEFT OUTER JOIN возвращает заштрихованную область:

SQL Server LEFT OUTER JOIN вернет все записи из table1 и только те записи из table2 , которые пересекаются с table1 .

Пример

Вот пример ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБЕРИТЕ поставщиков.идентификатор_ поставщика, имя_поставщика.имя_поставщика, заказ_дата_поставщика
ОТ поставщиков
LEFT OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

В этом примере LEFT OUTER JOIN будут возвращены все строки из таблицы поставщиков и только те строки из таблицы заказов, в которых объединенные поля равны.

Если значение supplier_id в таблице поставщиков не существует в таблице заказов, все поля в таблице заказов будут отображаться как > в наборе результатов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают LEFT OUTER JOINS:

У нас есть таблица поставщиков с двумя полями (supplier_id и supplier_name).Он содержит следующие данные:

У нас есть вторая таблица с названием orders с тремя полями (order_id, supplier_id и order_date).Он содержит следующие данные:

Если мы запустим оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

 ВЫБЕРИТЕ поставщиков. Идентификатор_поставщика, поставщиков.имя_поставщика, orders.order_date
ОТ поставщиков
LEFT OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Наш набор результатов будет выглядеть так:

Строки для Microsoft и NVIDIA будут включены, потому что использовалось LEFT OUTER JOIN.Однако вы заметите, что поле order_date для этих записей содержит значение .

ПРАВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

Другой тип соединения называется RIGHT OUTER JOIN SQL Server. Этот тип соединения возвращает все строки из ПРАВОЙ таблицы, указанной в условии ON, и только тех строк из другой таблицы, в которых объединенные поля равны (условие соединения выполнено).

Синтаксис

Синтаксис ПРАВОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБРАТЬ столбцы
ИЗ table1
RIGHT [OUTER] JOIN table2
НА table1.столбец = table2.column; 

В некоторых базах данных ключевые слова RIGHT OUTER JOIN заменяются на RIGHT JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server RIGHT OUTER JOIN возвращает заштрихованную область:

.

SQL Server RIGHT OUTER JOIN вернет все записи из table2 и только те записи из table1 , которые пересекаются с table2 .

Пример

Вот пример ПРАВОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБРАТЬ заказы.order_id, orders.order_date, suppliers.supplier_name
ОТ поставщиков
RIGHT OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Этот пример ПРАВОГО ВНЕШНЕГО СОЕДИНЕНИЯ вернет все строки из таблицы заказов и только те строки из таблицы поставщиков, в которых объединенные поля равны.

Если значение supplier_id в таблице заказов не существует в таблице поставщиков, все поля в таблице поставщиков будут отображаться как > в наборе результатов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ПРАВОЕ ВНЕШНИЕ СОЕДИНЕНИЯ:

У нас есть таблица поставщиков с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

У нас есть вторая таблица с названием orders с тремя полями (order_id, supplier_id и order_date).Он содержит следующие данные:

Если мы запустим оператор SELECT (который содержит ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ) ниже:

 ВЫБРАТЬ заказы.order_id, orders.order_date, suppliers.supplier_name
ОТ поставщиков
RIGHT OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Наш набор результатов будет выглядеть так:

Строка для 500127 (order_id) будет включена, потому что использовалось ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ.Однако вы заметите, что поле supplier_name для этой записи содержит значение .

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Другой тип соединения называется ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ SQL Server. Этот тип соединения возвращает все строки из ЛЕВОЙ таблицы и ПРАВОЙ таблицы с нулями в местах, где условие соединения не выполняется.

Синтаксис

Синтаксис ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБРАТЬ столбцы
ИЗ table1
FULL [OUTER] JOIN table2
НА table1.столбец = table2.column; 

В некоторых базах данных ключевые слова FULL OUTER JOIN заменяются на FULL JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме FULL OUTER JOIN SQL Server возвращает заштрихованную область:

SQL Server FULL OUTER JOIN вернет все записи из table1 и table2 .

Пример

Вот пример ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБЕРИТЕ поставщиков.идентификатор_ поставщика, имя_поставщика.имя_поставщика, заказ_дата_поставщика
ОТ поставщиков
FULL OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Этот пример ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ вернет все строки из таблицы поставщиков и все строки из таблицы заказов, и всякий раз, когда условие соединения не выполняется, <пустые значения> будут расширены на эти поля в наборе результатов.

Если значение supplier_id в таблице поставщиков не существует в таблице заказов, все поля в таблице заказов будут отображаться как > в наборе результатов.Если значение supplier_id в таблице заказов не существует в таблице поставщиков, все поля в таблице поставщиков будут отображаться как в наборе результатов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ПОЛНЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ:

У нас есть таблица поставщиков с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

У нас есть вторая таблица с названием orders с тремя полями (order_id, supplier_id и order_date).Он содержит следующие данные:

Если мы запустим оператор SELECT (который содержит ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ) ниже:

 ВЫБЕРИТЕ поставщиков.идентификатор_ поставщика, имя_поставщика.имя_поставщика, заказ_дата_поставщика
ОТ поставщиков
FULL OUTER JOIN заказы
ON vendors.supplier_id = orders.supplier_id; 

Наш набор результатов будет выглядеть так:

Строки для Microsoft и NVIDIA будут включены, потому что использовалось ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ.Однако вы заметите, что поле order_date для этих записей содержит значение .

Строка для supplier_id 10004 также будет включена, потому что использовалось ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Однако вы заметите, что поля supplier_id и supplier_name для этих записей содержат значение .

Как справиться с антисоединениями и применить их к бизнес-задачам

Любой, кто пытался изучить SQL, знает, как сложно освоить соединения. Вы пробираетесь через SELECT, операторы и операторы сравнения, ORDER BY и бух! вы попадаете в соединения.Но преодолев этот концептуальный «лежачий полицейский», вы на пути к овладению SQL.

Даже после изучения принципов внутреннего, внешнего, левого и правого объединений у вас может возникнуть назойливый вопрос: как мне найти значения из одной таблицы, которые НЕ присутствуют в другой таблице?

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

• Клиенты, не разместившие заказ
• Клиенты, которые не заходили на ваш сайт
• Продавцы, не заключившие сделку

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

Как выполнить анти-соединение

В отличие от большинства соединений SQL, антисоединение не имеет собственного синтаксиса. Чтобы найти все значения из Table_1 , которых нет в Table_2 , вам необходимо использовать комбинацию LEFT JOIN и WHERE .

1. Выберите каждый столбец из Table_1 . Назначьте Table_1 псевдоним: t1 .

   ВЫБРАТЬ *
   ИЗ Table_1 t1

2. LEFT JOIN Table_2 to Table_1 и назначьте Table_2 псевдоним: t2 .Используя LEFT JOIN , ваш запрос вернет все значения из Table_1 (независимо от того, присутствуют ли они также в Table_2 ).

   ВЫБРАТЬ *
   ИЗ таблицы 1 т1
   LEFT JOIN Table 2 t2 ON t1.id = t2.id

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

   ВЫБРАТЬ *
   ИЗ таблицы 1 т1
   LEFT JOIN Table 2 t2 ON t1.id = t2.id
   ГДЕ t2.id НУЛЬ

Весь запрос вернет только значения, которые находятся в Table_1 , но не в Table_2 .

Примеры анти-соединения

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

Какие клиенты не размещали заказ в августе?

Первая таблица ( demo.accounts ) содержит записи всех учетных записей клиентов. Во второй таблице ( demo.orders ) есть запись каждого заказа.

1. Выберите нужные столбцы в таблице учетных записей: id (псевдоним customer_id ) и имя (псевдоним customer_name ).

   ВЫБЕРИТЕ a.id в качестве идентификатора клиента id,
   имя клиента имя
   ИЗ демо.счета

2. LEFT JOIN таблица заказов к таблице счетов на account_id , для заказов, которые произошли в августе 2016 года.

   ВЫБЕРИТЕ a.id в качестве идентификатора клиента id,
   имя клиента имя
   FROM demo.accounts a
   LEFT JOIN demo.orders o
   НА a.id = o.account id
   И возникло МЕЖДУ ‘2016-08-01’ И ‘2016-08-31 23:59:59’

3. Исключите учетные записи, которые разместили заказ в августе, добавив WHERE o.id - NULL предложение .

  ВЫБЕРИТЕ a.id как customer_id,
       a.name как имя_клиента
  FROM demo.accounts a
  LEFT JOIN demo.orders o
    НА a.id = o.account_id
   И o.occurred_at МЕЖДУ '2016-08-01' И '2016-08-31 23:59:59'
 ГДЕ o.id равно NULL
  

Какие клиенты не посещали ваш сайт в этом году?

Первая таблица ( demo.accounts ) содержит записи всех учетных записей клиентов. Вторая таблица ( demo.web_events ) содержит записи о каждом посещении сети.

1. Выберите нужные столбцы в таблице учетных записей: id (псевдоним customer_id ) и имя (псевдоним customer_name ).

   ВЫБЕРИТЕ a.id в качестве идентификатора клиента id,
   имя клиента имя
   FROM demo.accounts a

2. LEFT JOIN таблица веб-событий с таблицей учетных записей на account_id , для посещений, которые произошли в 2016 году:

     ВЫБЕРИТЕ a.id как customer_id,
          а.имя как customer_name
          FROM demo.accounts a
     

   LEFT JOIN demo.web события мы
   НА we.account id = a.id
   И we.occurred_at МЕЖДУ ‘2016-01-01’ И ‘2016-12-31 23:59:59’

3. Исключите учетные записи, которые посещали веб-сайт в 2016 году, добавив предложение WHERE we.id is NULL .

     ВЫБЕРИТЕ a.id как customer_id,
          a.name как имя_клиента
          FROM demo.accounts a
     

   LEFT JOIN demo.web события мы
   НА мы.аккаунт id = a.id
   И we.occurred_at МЕЖДУ ‘2016-01-01’ И ‘2016-12-31 23:59:59’
   ГДЕ we.id равно NULL

Какие торговые представители не заключили сделку с 5 по 15 сентября?

Для этого запроса вы будете использовать три таблицы: demo.accounts , demo.orders и demo.sales_reps .

1. Хотя у вас уже есть основная таблица, содержащая записи всех торговых представителей ( demo.sales_reps ), вам понадобится вторая таблица, содержащая записи всех торговых представителей, которые закрыли сделку в течение определенного периода времени, чтобы выполнить вашу анти присоединиться.Поскольку этой таблицы еще не существует, вам нужно начать с создания подзапроса.

   SELECT o.id AS, заказ id,
   sr.id as продажи представитель id
   ОТ demo.orders o
   ПРИСОЕДИНЯЙТЕСЬ demo.accounts a
   НА o.account id = a.id
   ПРИСОЕДИНЯЙТЕСЬ demo.sales представителя sr
   ON sr.id = a.sales rep id
   ГДЕ произошло МЕЖДУ ‘2016-09-05’ И ‘2016-09-15 23:59:59’

Теперь у вас есть две вещи, которые вам нужны: таблица, содержащая записи всех торговых представителей ( demo.sales_reps ) и подзапрос, извлекающий запись о торговом представителе, связанном со всеми заказами, которые произошли за указанный период времени. Вы сможете присоединить этот подзапрос к таблице так же, как если бы вы присоединили таблицу к таблице.

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

   SELECT sr.id AS Продажи rep id,
   sr.name AS sales rep name,
   sr.region id AS продажа rep region id
   ИЗ демо.sales_reps sr

3. LEFT JOIN подзапрос (с псевдонимом a ) на sales_rep_id .

     ВЫБЕРИТЕ sr.id AS sales_rep_id,
          sr.name AS sales_rep_name,
          sr.region_id AS sales_rep_region_id
     ОТ demo.sales_reps sr
     

   ЛЕВОЕ СОЕДИНЕНИЕ (
   ВЫБЕРИТЕ o.id КАК идентификатор заказа ,
   sr.id as продажи представитель id
   ОТ demo.orders o
   ПРИСОЕДИНЯЙТЕСЬ demo.accounts a
   НА o.account id = a.id
   ПРИСОЕДИНЯЙТЕСЬ demo.sales представителя sr
   НА ср.id = a.sales представитель id
   ГДЕ произошло МЕЖДУ ‘2016-09-05’ И ‘2016-09-15 23:59:59’
   ) а
   ON sr.id = a.sales rep id

4. Наконец, исключите из подзапроса торговых представителей, связанных с заказом, добавив условие WHERE a.order_id is NULL .

     ВЫБЕРИТЕ sr.id AS sales_rep_id,
          sr.name AS sales_rep_name,
          sr.region_id AS sales_rep_region_id
     ОТ demo.sales_reps sr
     

   ЛЕВОЕ СОЕДИНЕНИЕ (
   ВЫБЕРИТЕ o.id AS order id,
   sr.id as продажи представитель id
   ОТ demo.orders o
   ПРИСОЕДИНЯЙТЕСЬ demo.accounts a
   НА o.account id = a.id
   ПРИСОЕДИНЯЙТЕСЬ demo.sales представителя sr
   ON sr.id = a.sales rep id
   ГДЕ произошло МЕЖДУ ‘2016-09-05’ И ‘2016-09-15 23:59:59’
   ) а
   ON sr.id = a.sales rep id
   ГДЕ a.order_id равно NULL

Нет опыта программирования? Без проблем. Изучите SQL и Python с использованием реальных данных с помощью наших бесплатных руководств.

CROSS JOIN — Часть 3 — {coding} Прицел

CROSS JOIN в центре внимания.Эта статья завершает нашу небольшую серию публикаций, связанных с SQL JOIN. Если вы пропустили две предыдущие статьи, обратитесь к ним следующим образом:

SQL Server CROSS JOIN — самое простое из всех соединений. Он реализует комбинацию 2 таблиц без условия соединения. Если у вас есть 5 строк в одной таблице и 3 строки в другой, вы получите 15 комбинаций. Другое определение — декартово произведение.

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

Синтаксис SQL CROSS JOIN

Как и в случае с INNER JOIN, у вас может быть CROSS JOIN из 2 стандартов, SQL-92 и SQL-89. T-SQL поддерживает оба синтаксиса, но я предпочитаю SQL-92. Прочтите часть 1, посвященную INNER JOIN, если вы хотите знать, почему.

Синтаксис SQL-92

  ВЫБРАТЬ
 a.column1
, b.column2
ИЗ Таблицы 1 а
CROSS JOIN Table2 b
  

SQL-89

  ВЫБРАТЬ
 a.column1
, b.column2
ИЗ Таблицы 1 a, Таблицы 2 b
  

Во многом похож на SQL-89 — INNER JOIN без условия соединения.

5 примеров использования SQL Server CROSS JOIN

Вы можете задаться вопросом, когда можно использовать SQL CROSS JOIN. Конечно, это полезно для формирования комбинаций значений. Что еще?

1. Данные испытаний

Если вам нужен большой объем данных, CROSS JOIN поможет вам. Например, у вас есть таблица поставщиков и продуктов. Другая таблица содержит продукты, предлагаемые продавцом. Если он пуст и вам нужны данные быстро, вот что вы можете сделать:

  ВЫБРАТЬ
 П.Идантификационный номер продукта
, v.BusinessEntityID КАК VendorID
ИЗ
Производство.Продукт p
CROSS JOIN Purchasing.Vendor v
  

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

2. Получение результатов от пропущенных комбинаций

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

  - получить список магазинов 294 («Профессиональные продажи и обслуживание») без заказов на продажу за январь 2014 г.
ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
 б) Название КАК Продукт
ИЗ Продаж. Магазин
CROSS JOIN Производство.Продукт b
LEFT JOIN (ВЫБРАТЬ
c.StoreID
, a.ProductID
, СУММ (a.LineTotal) КАК OrderTotal
ОТ Sales.SalesOrderПодробнее
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderHeader b НА a.SalesOrderID = b.SalesOrderID
ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к Sales.Customer c ON b.CustomerID = c.CustomerID
ГДЕ c.StoreID = 294 И
б. ДАТА ЗАКАЗА МЕЖДУ 01.01.2014 И 31.01.2014
ГРУППА ПО c.StoreID, a.ProductID) d НА a.BusinessEntityID = d.StoreID
И b.ProductID = d.ProductID
ГДЕ d.OrderTotal ЕСТЬ NULL
И a.BusinessEntityID = 294
ЗАКАЗАТЬ ПО Б.ИМЯ
  

Как видите, нам сначала нужны все комбинации товаров и магазинов — мы используем CROSS JOIN. Затем нам понадобится список проданных товаров за январь 2014 года.Наконец, примените LEFT JOIN к этому списку и используйте предложение WHERE, чтобы получить только продукты без продаж. Затем мы получаем информацию о товарах, которые не были проданы.

3. Формирование слов из сочетаний букв

Если вам нравятся словесные игры с комбинациями букв, вы можете использовать CROSS JOIN с самосоединением. Вот пример, состоящий из трех букв «D», «O» и «G».

 
DECLARE @table TABLE (буква CHAR (1) NOT NULL)

ВСТАВИТЬ В @table
ЗНАЧЕНИЯ ('D'), ('O'), ('G')

ВЫБРАТЬ
 а.письмо
, б. буква
, c. письмо
ИЗ @table a
CROSS JOIN @table b
CROSS JOIN @table c
ГДЕ a.letter + b.letter + c.letter КАК '% O%'
  

Аналогичный код без предложения WHERE сгенерирует 27 записей. Вышеупомянутое предложение WHERE помогло исключить комбинации из трех похожих буквенных комбинаций, таких как «DDD» или «GGG». Ниже результат.

Рисунок 1. Результат использования CROSS JOIN для формирования комбинаций из трех букв D, O и G.

Конечно, поскольку я не вложил в запрос особого интеллекта, большинство результатов не являются словами.Тем не менее, это помогает в мыслительной части игры.

4. Рекомендации по питанию

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

 
ОБЪЯВЛЕНИЕ ТАБЛИЦЫ @FoodMenu (FoodItem VARCHAR (50) NOT NULL, ItemType CHAR (1) NOT NULL)

- основное блюдо
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
('Спагетти с фрикадельками', 'М'),
('Спагетти с жареным цыпленком', 'М'),
(«Рис с жареным цыпленком», «М»)

-- Гарнир
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
('Кукуруза и морковь в масле', 'S'),
('Картофель фри', 'S'),
('Овощной салат из капусты', 'S')

- напитки
ВСТАВИТЬ @FoodMenu
ЗНАЧЕНИЯ
("Апельсиновый сок", "Д"),
("Ананасовый сок", "Д"),
('Сода', 'Д')

ВЫБРАТЬ
 а.FoodItem AS MainCourse
, b.FoodItem как гарнир
, c.FoodItem AS Напитки
ОТ @FoodMenu a
CROSS JOIN @FoodMenu b
CROSS JOIN @FoodMenu c
ГДЕ a.ItemType = 'M' И
b.ItemType = 'S' И
c.ItemType = 'D'
  

Результат:

Рисунок 2. Комбинации продуктов с использованием CROSS JOIN и самостоятельного соединения.

Некоторые из них желательны. Некоторые говорят: «Забудь!» Это зависит от твоего вкуса.

5. Выбор дизайна футболки

Еще одно возможное использование CROSS JOIN — получение комбинаций дизайна для рубашек.Вот пример кода:

 
ОБЪЯВЛЕНИЕ ТАБЛИЦЫ @tshirts (тип атрибута CHAR (1) NOT NULL, атрибут VARCHAR (15))

--размер
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('S', 'Маленький'),
('S', 'Средний'),
('S', 'Большой')

--цвет
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('C', 'Красный'),
('C', 'Синий'),
('C', 'зеленый'),
('C', 'Черный'),
('C', 'Purple'),
('C', 'желтый'),
('C', 'Белый')

--дизайн
ВСТАВИТЬ @tshirts
ЗНАЧЕНИЯ
('D', 'Обычный'),
('D', 'Отпечатано')

ВЫБРАТЬ
 А. Размер атрибута
, б. Атрибут AS Color
, c. Атрибут AS Design
ОТ @tshirts a
CROSS JOIN @tshirts b
CROSS JOIN @tshirts c
Где.attributeType = 'S' И
b.attributeType = 'C' И
c.attributeType = 'D'
  

А результаты? Взгляните на его часть на рисунке 3:

Рис. 3. Первые 20 записей о комбинациях цвета, размера и дизайна с использованием CROSS JOIN.

Вы можете придумать еще несколько примеров?

Производительность CROSS JOIN для SQL Server

В чем выгода использования CROSS JOIN? Как бы то ни было, CROSS JOIN может вызвать проблемы с производительностью, если вы не будете осторожны. Самая страшная часть — это продукт из 2-х наборов.Таким образом, без ограничения результатов в предложении WHERE, Table1 с 1000 записями CROSS JOIN с Table2 с 1 000 000 записей станет 1 000 000 000 записей. Следовательно, SQL Server должен прочитать много страниц.

В качестве примера рассмотрим объединение сотрудников мужского и женского пола в AdventureWorks .

  ИСПОЛЬЗОВАТЬ AdventureWorks
ИДТИ

ВЫБРАТЬ
 P.LastName + ISNULL ('' + p.Suffix, '') + ',' + P.FirstName + ISNULL ('' + P.MiddleName, '') AS Male
, P1.LastName + ISNULL ('' + p1.Suffix, '') + ',' + P1.FirstName + ISNULL ('' + P1.MiddleName, '') AS Female
ОТ HumanResources.Employee e
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p ON e.BusinessEntityID = P.BusinessEntityID
CROSS JOIN HumanResources.Employee e1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p1 ON e1.BusinessEntityID = p1.BusinessEntityID
ГДЕ e.Gender = 'M'
И e1.Gender = 'F'
  

Приведенный выше код дает вам все возможные пары сотрудников мужского и женского пола. Я получил только 17 304 записи, но посмотрите на логические чтения на рис. 4:

Рисунок 4.Высокое логическое чтение после объединения сотрудников мужского и женского пола с помощью CROSS JOIN.

Вы видели логические чтения таблицы Person ? Это составляет 53 268 x 8 КБ страниц! Не говоря уже о WorkTable , логические чтения выполняются в tempdb .

Вывод? Проверьте STATISTICS IO, и если вам не удается увидеть большие логические операции чтения, выразите запрос по-другому. Дополнительные условия в предложении WHERE или подход «разделяй и властвуй» могут помочь.

Когда CROSS JOIN становится ВНУТРЕННИМ СОЕДИНЕНИЕМ

Да, верно.SQL Server может обрабатывать CROSS JOIN как INNER JOIN. Ранее мы упоминали, что когда RIGHT JOIN обрабатывается как LEFT JOIN, оно может применяться к CROSS JOIN. Взгляните на код ниже:

  ВЫБРАТЬ
 c.CustomerID
, c.AccountNumber
, P.BusinessEntityID
, P.LastName
, P.FirstName
ОТ Продажи.Клиент c
CROSS JOIN Person.Person p
ГДЕ c.PersonID = P.BusinessEntityID
  

Прежде чем мы проверим план выполнения, давайте рассмотрим эквивалент INNER JOIN.

  ВЫБРАТЬ
 c.Пользовательский ИД
, c.AccountNumber
, P.BusinessEntityID
, P.LastName
, P.FirstName
ОТ Продажи.Клиент c
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Person.Person p ON c.PersonID = P.BusinessEntityID
  

Теперь проверьте план выполнения ниже.

Рисунок 5. Два (2) плана выполнения. Один использует запрос с использованием CROSS JOIN, а другой — INNER JOIN. Наборы результатов такие же, как и планы выполнения.

Верхний план — это запрос с использованием CROSS JOIN. Нижний план — это запрос, использующий ВНУТРЕННЕЕ СОЕДИНЕНИЕ. У них же QueryHashPlan .

Вы обратили внимание на оператор Hash Match верхнего плана? Это ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Но мы использовали CROSS JOIN в коде. Предложение WHERE в первом запросе ( WHERE c.PersonID = P.BusinessEntityID ) заставляло результаты иметь комбинации только с одинаковыми ключами. Итак, логически это ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

Какой лучше? Это ваш личный выбор. Я предпочитаю INNER JOIN, потому что цель состоит в том, чтобы объединить 2 таблицы с одинаковыми ключами. Использование INNER JOIN делает это очень понятным.Но это только я.

Заключение

CROSS JOIN дает вам все возможные комбинации значений. Однако вас предупредили, что это может вызвать некоторый «взрыв» данных. Используйте это ПРИСОЕДИНЕНИЕ осторожно. Максимально ограничивайте результаты. Кроме того, вы можете написать CROSS JOIN, функционально эквивалентный INNER JOIN.

Эта статья завершает серию о соединениях SQL JOIN. Для будущих ссылок вы можете добавить эту статью в закладки. Или добавьте его в свою коллекцию в браузере.

И не забудьте опубликовать эту статью в своих любимых социальных сетях?

Разработчик программного обеспечения и менеджер проектов с более чем 20-летним опытом разработки программного обеспечения. Его последние технологические предпочтения включают C #, SQL Server BI Stack, Power BI и Sharepoint. Эдвин сочетает свои технические знания с новейшими навыками написания контента, чтобы помочь новому поколению энтузиастов технологий.

Последние сообщения Эдвина Санчеса (посмотреть все)

Как избежать условных соединений в T-SQL

То, что некоторые SQL JOIN возможны, не означает, что они являются хорошим вариантом.«Условное СОЕДИНЕНИЕ» — одно из них.

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

Термин «Условное СОЕДИНЕНИЕ» несколько неоднозначен, поскольку люди обращаются к различным проблемам, когда используют его.Кажется, что программисты хотят либо ПРИСОЕДИНЯТЬ таблицу к нескольким другим таблицам, либо ПРИСОЕДИНЯТЬ столбец в таблице к разным столбцам второй таблицы, выбирая столбец ПРИСОЕДИНЕНИЕ в зависимости от условия.

Поскольку T-SQL не имеет синтаксиса, который позволял бы поместить имя таблицы в оператор CASE, первое определение условного JOIN действительно не имеет других средств разрешения, кроме как просто JOIN всех таблиц (вероятно, как LEFT OUTER JOINs) и используйте операторы CASE для извлечения определенных элементов данных, требуемых из вторичных таблиц, в зависимости от ситуации.

Недавно я писал запрос, который можно разрешить во втором случае. Хотя я думал о нескольких других способах сделать это, с точки зрения кодирования использование оператора CASE в предложении ON в JOIN привело к простейшему синтаксису, чтобы запрос работал. Однако был ли синтаксис, который выглядел простым, самым эффективным способом сделать это?

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

Пример данных

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

Вы можете видеть, что четыре столбца данных (N1, N2, N3 и N4) содержат случайные числа в диапазоне от 1 до 100.Мы выбрали ТОП-10 строк только для иллюстрации, и если вы будете следовать по тексту, вы получите другие, но похожие результаты во втором наборе результатов. Закомментированные DROP предоставляются для очистки вашей песочницы позже, если вы хотите запустить эти примеры на своем сервере. Обратите внимание, что мы будем запускать их с использованием SQL 2012, но также можно использовать SQL 2008 или SQL 2005.

Сценарий 1: Условное соединение на основе данных в левой таблице

Теперь предположим, что у нас есть бизнес-требование, которое гласит, что мы хотим выполнить JOIN из левой таблицы (4 строки) во вторичную таблицу на основе диапазона, в который попадает значение в левой таблице.Такое СОЕДИНЕНИЕ может выглядеть так.

И он отображает результаты, которые выглядят следующим образом (показаны только первые пять строк из примерно 40 000 возвращенных).

Вы можете видеть, что, поскольку значение Num в строке в левой таблице равно 53, оно соответствует третьему столбцу N (N3) в таблице, к которой присоединилась.

Меня интересовали ВРЕМЯ и СТАТИСТИКА ВХОДА-ВЫВОДА, поэтому я запустил этот запрос с ними, и мы получили эти результаты.

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

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

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

Наша первая мысль, конечно, заключается в том, что, возможно, мы сможем создать ИНДЕКС, чтобы немного ускорить работу этого плохого парня.Так что давай попробуем.

Когда мы снова запускаем тот же запрос, мы получаем эти временные результаты.

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

Мы видим, что пока используется созданный нами ИНДЕКС, похоже, это не сильно помогло.Если вы повторите этот запрос и наведите курсор на операторы Index Scan (NonClustered) и Table Spool в графическом плане запроса, вы обнаружите, что в запросе использовались те же четыре миллиона фактических строк.

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

Вместо нашего условного JOIN мы можем разделить большую таблицу на четыре части, выполняя последовательные JOINs на каждом из разделов, а затем повторно объединяя части, как это (используя UNION ALL):

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

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

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

SQL 2012 несколько ворчал, что отсутствует ИНДЕКС, который мог бы немного улучшить запрос, поэтому давайте продолжим и создадим ИНДЕКС, который он рекомендует, и попробуем еще раз.

Теперь мы получаем эти временные результаты:

Мне кажется, что рекомендуемый INDEX ни капли не помог! И я действительно проверил план запроса и обнаружил, что оптимизатор использовал рекомендуемый ИНДЕКС.

С моей точки зрения, я бы сказал, что переписывание сработало достаточно, чтобы я без колебаний использовал его в продакшене без рекомендованного ИНДЕКСа, тем самым сэкономив пространство, которое потребовало бы создание ИНДЕКСА, не говоря уже о накладных расходах, связанных с этим ИНДЕКСОМ. в операторах INSERT, UPDATE и DELETE.

Я также должен отметить, что в нашей первой (условной JOIN) попытке мы работали со столбцами (N1,…, N4), которые все были одного типа данных. Если в вашем случае это не так, то, вероятно, есть дополнительные противники производительности в том, чтобы привести их к тому же типу данных, на которые вы должны обратить внимание и принять во внимание.

Давайте отбросим этот ИНДЕКС, прежде чем приступить к следующему сценарию.

Сценарий 2: Условное соединение на основе внешнего параметра

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

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

Действительно, план запроса, который мы здесь не будем показывать, очень похож на первый план запроса, который мы показали, вплоть до четырех миллионов фактических строк в операторах Clustered Index Scan и Table Spool.

Использование идентичного подхода к рефакторингу:

В результате мы получаем гораздо более сложный на вид запрос (с похожим, гораздо более сложным планом запроса), который выполняет быстрое разбиение!

Теперь, если вы должны передать переменную @Switch в хранимую процедуру, вполне возможно, что вы можете столкнуться с проблемами анализа параметров в одном или обоих случаях. Но мы не будем здесь вдаваться в подробности (и как их решать), поскольку они были довольно подробно освещены SQL MVP Эрландом Соммарскогом в его прекрасном блоге на эту тему «Медленно в приложении, быстро в SSMS?»

Выводы

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

Некоторые другие уроки, извлеченные из двух сценариев, которые мы исследовали в этой статье:

• Простейший план запроса не всегда может работать наиболее эффективно.

ИНДЕКС

• , рекомендуемые SQL 2012 в графическом плане запроса (и, предположительно, советником по настройке SQL), не всегда могут способствовать более быстрому выполнению запроса, даже если Оптимизатор решит их использовать.
• Если производительность имеет значение, а это обычно так, проверьте запросы на наличие проблем с производительностью и рассмотрите альтернативные способы достижения того же результата.

Наконец, вам может быть интересно, что произошло с моим запросом, о котором я упоминал ранее. Конечно, это было не так просто, как два сценария, которые мы здесь исследовали. В нем использовалась большая таблица, созданная с помощью VIEW, привязанного к схеме, и было не только четыре случая, которые нужно было рассмотреть (хотя было четыре потенциальных столбца для СОЕДИНЕНИЯ).Использование условного JOIN позволило бизнес-решению работать быстро, однако, когда я тестировал производительность, я обнаружил, что этого недостаточно. Так что я приложил дополнительные усилия, чтобы улучшить его, и в конечном итоге дополнительные усилия дали удовлетворительные результаты.

Спасибо за чтение, ребята! Я надеюсь, что однажды вы сможете использовать уроки, которые я здесь усвоил, чтобы повысить скорость хотя бы одного из ваших запросов.

SQL Join: Обзор типов SQL-соединений с примерами — Управление базами данных — Блоги

SQL JOIN — это предложение, которое используется для объединения нескольких таблиц и извлечения данных на основе общего поля в реляционных базах данных.Специалисты по базам данных используют нормализацию для обеспечения и улучшения целостности данных. В различных формах нормализации данные распределяются по нескольким логическим таблицам. Эти таблицы используют ссылочные ограничения — первичный ключ и внешние ключи — для обеспечения целостности данных в таблицах SQL Server. На изображении ниже мы видим процесс нормализации базы данных.

Понимание различных типов SQL JOIN

SQL JOIN генерирует значимые данные путем объединения нескольких реляционных таблиц.Эти таблицы связаны с помощью ключа и имеют отношения «один к одному» или «один ко многим». Чтобы получить правильные данные, вы должны знать требования к данным и правильные механизмы соединения. SQL Server поддерживает несколько объединений, и каждый метод имеет определенный способ извлечения данных из нескольких таблиц. На изображении ниже указаны поддерживаемые соединения SQL Server.

Внутреннее соединение SQL

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

В приведенном ниже примере обратите внимание на следующее:

• У нас есть две таблицы — [Сотрудники] и [Адрес].
• SQL-запрос объединяется в столбцы [Сотрудники]. [EmpID] и [Адрес]. [ID].

Выходные данные запроса возвращают записи сотрудников для EmpID, которые существуют в обеих таблицах.

Внутреннее соединение возвращает совпадающие строки из обеих таблиц; поэтому оно также известно как Equi join. Если мы не укажем ключевое слово inner, SQL Server выполнит операцию внутреннего соединения.

В другом типе внутреннего соединения, тета-соединении, мы не используем оператор равенства (=) в предложении ON. Вместо этого мы используем операторы неравенства, такие как <и>.

ВЫБРАТЬ * ИЗ Table1 T1, Table2 T2 ГДЕ T1.Price

Самостоятельное присоединение к SQL

При самосоединении SQL Server соединяет таблицу с самим собой. Это означает, что имя таблицы появляется дважды в предложении from.

Ниже у нас есть таблица [Emp], в которой есть данные о сотрудниках, а также их менеджеры.Самосоединение полезно для запроса иерархических данных. Например, в таблице сотрудников мы можем использовать самообъединение, чтобы узнать каждого сотрудника и имя его руководителя.

Приведенный выше запрос помещает самосоединение в таблицу [Emp]. Он соединяет столбец EmpMgrID со столбцом EmpID и возвращает соответствующие строки.

перекрестное соединение SQL

При перекрестном соединении SQL Server возвращает декартово произведение из обеих таблиц. Например, на изображении ниже мы выполнили перекрестное соединение для таблиц A и B.

Перекрестное соединение объединяет каждую строку из таблицы A с каждой строкой, доступной в таблице B. Следовательно, результат также известен как декартово произведение обеих таблиц. На изображении ниже обратите внимание на следующее:

• Таблица [Сотрудник] содержит три строки для Emp ID 1,2 и 3.
• Таблица [Адрес] содержит записи для Emp ID 1,2,7 и 8.

В выходных данных перекрестного объединения строка 1 таблицы [Сотрудник] объединяется со всеми строками таблицы [Адрес] и следует тому же шаблону для остальных строк.

Если первая таблица содержит x строк, а вторая таблица имеет n строк, перекрестное соединение дает x * n количество строк в выходных данных. Вам следует избегать перекрестного соединения для больших таблиц, потому что оно может вернуть огромное количество записей, а SQL Server требует большой вычислительной мощности (ЦП, память и ввод-вывод) для обработки таких обширных данных.

Внешнее соединение SQL

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

На изображении ниже показано общее левое, правое и полное внешнее соединение.

Левое внешнее соединение

Левое внешнее соединение SQL возвращает совпадающие строки обеих таблиц вместе с несогласованными строками из левой таблицы. Если запись из левой таблицы не имеет совпадающих строк в правой таблице, она отображает запись со значениями NULL.

В приведенном ниже примере левое внешнее соединение возвращает следующие строки:

• Соответствующие строки: Emp ID 1 и 2 существует как в левой, так и в правой таблицах.
• Несопоставленная строка: Emp ID 3 не существует в правой таблице. Следовательно, в выходных данных запроса у нас есть значение NULL.

Соединение правое наружное

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

В приведенном ниже примере у нас есть следующие выходные строки:

• Соответствующие строки: Emp ID 1 и 2 существует в обеих таблицах; следовательно, эти строки являются совпадающими строками.
• Несопоставленные строки: в правой таблице у нас есть дополнительные строки для Emp ID 7 и 8, но эти строки недоступны в левой таблице. Следовательно, мы получаем значение NULL в правом внешнем соединении для этих строк.

Полное внешнее соединение

Полное внешнее соединение возвращает следующие строки на выходе:

• Соответствующие строки между двумя таблицами.
• Несопоставленные строки, аналогичные левому внешнему соединению: значения NULL для несовпадающих строк из правой таблицы.
• Несопоставленные строки, аналогичные правому внешнему соединению: нулевые значения для несовпадающих строк из левой таблицы.

SQL объединяется с несколькими таблицами

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

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

 ИСПОЛЬЗОВАНИЕ [AdventureWorks2019]
ИДТИ
ВЫБРАТЬ
     д. [BusinessEntityID]
     ,п.[Имя]
     , стр. [MiddleName]
     , стр. [Фамилия]
     , д. [JobTitle]
     , д. [Название] AS [Отдел]
     , d. [GroupName]
     , edh. [Дата начала]
ОТ [HumanResources]. [Сотрудник] e
     INNER JOIN [Человек]. [Человек] p
     НА стр. [BusinessEntityID] = e. [BusinessEntityID]
     ВНУТРЕННЕЕ СОЕДИНЕНИЕ [HumanResources]. [EmployeeDepartmentHistory] edh
     НА e. [BusinessEntityID] = edh. [BusinessEntityID]
     INNER JOIN [HumanResources]. [Department] d
     НА edh.[DepartmentID] = d. [DepartmentID]
ГДЕ edh.EndDate ЕСТЬ NULL
ИДТИ
 

Давайте проанализируем запрос, выполнив следующие шаги:

• Промежуточный результат 1: Первое внутреннее соединение между таблицей [HumanResources]. [Employees] и [Person]. [Person].
• Промежуточный результат 2: Внутреннее соединение между таблицей [Промежуточный результат 1] и [HumanResources]. [EmployeeDepartmentHistory].
• Промежуточный результат 3: Внутреннее соединение между [Промежуточный результат 2] и [HumanResources].Таблица [Отделение].

После выполнения запроса с несколькими объединениями оптимизатор запросов подготавливает план выполнения. Он подготавливает оптимизированный по стоимости план выполнения, удовлетворяющий условиям соединения с использованием ресурсов — например, в приведенном ниже фактическом плане выполнения мы можем посмотреть на несколько вложенных циклов (внутреннее соединение) и хэш-соответствие (внутреннее соединение), объединяющее данные из нескольких соединяемых таблиц. .

значений NULL и SQL объединяет

Предположим, что у нас есть значения NULL в столбцах таблицы, и мы объединяем таблицы в этих столбцах.Соответствует ли SQL Server значениям NULL?

Значения NULL не соответствуют друг другу. Таким образом SQL Server не может вернуть соответствующую строку. В приведенном ниже примере у нас есть NULL в столбце EmpID таблицы [Сотрудники]. Следовательно, в выходных данных он возвращает соответствующую строку только для [EmpID] 2.

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

SQL присоединяйтесь к лучшим практикам

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

• Внутренние соединения выводят совпадающие строки из условия соединения в обеих таблицах.
• Перекрестное соединение возвращает декартово произведение обеих таблиц.
• Внешнее соединение возвращает совпадающие и несовпадающие строки в зависимости от левого, правого и полного ключевых слов.
• Самосоединение SQL объединяет таблицу с собой.
• При использовании объединений в запросах всегда следует использовать псевдоним таблицы.
• Всегда используйте формат имени [псевдоним таблицы]. [Столбец], состоящий из двух частей, для столбцов в запросах.
• В случае нескольких соединений SQL в запросе следует использовать логический порядок таблиц таким образом, чтобы удовлетворить ваши требования к данным и минимизировать поток данных между различными операторами плана выполнения.
• Вы можете комбинировать несколько соединений, таких как внутреннее соединение, внешнее соединение и самосоединение. Однако вы должны использовать объединения и их заказы для получения требуемых данных.

ошибок, ошибок и передовых методов T-SQL — присоединяется к

Эта статья — третья часть из серии об ошибках, подводных камнях и передовых методах T-SQL. Ранее я рассмотрел детерминизм и подзапросы. На этот раз я сосредотачиваюсь на присоединении. Некоторые из ошибок и передовых методов, которые я здесь описываю, являются результатом опроса, который я провел среди других MVP. Благодарим Эрланда Соммарскога, Аарона Бертрана, Алехандро Меса, Умахандара Джаячандрана (Калифорнийский университет), Фабиано Невеса Аморима, Милоша Радивоевича, Саймона Сабина, Адама Маханика, Томаса Грозера, Чан Мин Мана и Пола Уайта за ваши идеи!

В своих примерах я буду использовать образец базы данных под названием TSQLV5.Здесь вы можете найти скрипт, который создает и заполняет эту базу данных, а его ER-диаграмму — здесь.

В этой статье я сосредоточусь на четырех классических типичных ошибках: COUNT (*) во внешних соединениях, двойные агрегаты, противоречие ON-WHERE и противоречие OUTER-INNER соединения. Все эти ошибки связаны с основами запросов T-SQL, и их легко избежать, если вы будете следовать простым рекомендациям.

COUNT (*) во внешних соединениях

Наша первая ошибка связана с неправильным подсчетом пустых групп в результате использования внешнего соединения и агрегата COUNT (*).Рассмотрим следующий запрос, вычисляющий количество заказов и общий фрахт на одного клиента:

 ИСПОЛЬЗОВАТЬ TSQLV5; - http://tsql.solidq.com/SampleDatabases/TSQLV5.zip

  ВЫБЕРИТЕ custid, COUNT (*) КАК числа, СУММ (фрахт) КАК totalfreight
  ОТ Sales.Orders
  ГРУППА ПО custid
  ЗАКАЗАТЬ ПО custid; 

Этот запрос генерирует следующий вывод (сокращенно):

 пользовательских номеров totalfreight
  ------- ---------- -------------
  1 6 225,58
  2 4 97.42
  3 7 268,52
  4 13 471,95
  5 18 1559,52
  ...
  21 7 232,75
  23 5 637,94
  ...
  56 10 862,74
  58 6 277,96
  ...
  87 15 822,48
  88 9 194,71
  89 14 1353,06
  90 7 88,41
  91 7 175,74

  (Затронуто 89 строк) 

В настоящее время в таблице «Клиенты» находится 91 клиент, из которых 89 разместили заказы; следовательно, результат этого запроса показывает 89 групп клиентов и их правильное количество заказов и общие фрахтовые агрегаты.Клиенты с идентификаторами 22 и 57 присутствуют в таблице «Клиенты», но не разместили никаких заказов и поэтому не отображаются в результатах.

Предположим, вас просят включить клиентов, у которых нет связанных заказов, в результат запроса. В таком случае естественно выполнить левое внешнее соединение между клиентами и заказами, чтобы сохранить клиентов без заказов. Однако типичная ошибка при преобразовании существующего решения в решение, которое применяет соединение, заключается в том, чтобы оставить вычисление количества заказов как COUNT (*), как показано в следующем запросе (назовите его Query 1):

 ВЫБРАТЬ C.custid, COUNT (*) КАК числа, СУММ (O.freight) КАК totalfreight
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА C.custid = O.custid
  ГРУППА К.КУСТИДА
  ЗАКАЗ ОТ C.custid; 

Этот запрос генерирует следующий вывод:

 пользовательских номеров totalfreight
  ------- ---------- -------------
  1 6 225,58
  2 4 97,42
  3 7 268,52
  4 13 471,95
  5 18 1559.52
  ...
  21 7 232,75
  22 1 NULL
  23 5 637,94
  ...
  56 10 862,74
  57 1 NULL
  58 6 277,96
  ...
  87 15 822,48
  88 9 194,71
  89 14 1353,06
  90 7 88,41
  91 7 175,74

  (Затронута 91 строка) 

Обратите внимание, что клиенты 22 и 57 на этот раз появляются в результате, но их счетчик заказов показывает 1 вместо 0, потому что COUNT (*) считает строки, а не заказы.Общий фрахт отображается правильно, поскольку SUM (фрахт) игнорирует входные данные NULL.

План этого запроса показан на рисунке 1.

Рисунок 1: План запроса 1

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

Чтобы исправить эту ошибку, вы хотите применить агрегат COUNT к элементу с незащищенной стороны внешнего соединения и убедиться, что в качестве входных данных используется столбец, не имеющий значения NULLable. Столбец первичного ключа будет хорошим выбором. Вот запрос решения (назовите его Query 2) с исправленной ошибкой:

 ВЫБРАТЬ C.custid, COUNT (O.orderid) AS numorders, SUM (O.freight) AS totalfreight
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА C.custid = O.custid
  ГРУППА К.кустид
  ЗАКАЗ ОТ C.custid; 

Вот результат этого запроса:

 пользовательских номеров totalfreight
  ------- ---------- -------------
  1 6 225,58
  2 4 97,42
  3 7 268,52
  4 13 471,95
  5 18 1559,52
  ...
  21 7 232,75
  22 0 ПУСТО
  23 5 637,94
  ...
  56 10 862,74
  57 0 ПУСТО
  58 6 277.96
  ...
  87 15 822,48
  88 9 194,71
  89 14 1353,06
  90 7 88,41
  91 7 175,74

  (Затронута 91 строка) 

Обратите внимание, что на этот раз клиенты 22 и 57 показывают правильный счет, равный нулю.

План этого запроса показан на рисунке 2.

Рисунок 2: План запроса 2

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

При использовании объединений с осторожностью применяйте агрегат COUNT (*). Обычно это ошибка при использовании внешних объединений. Лучше всего применить агрегат COUNT к столбцу, отличному от NULLable, со стороны «многие» соединения «один ко многим». Столбец первичного ключа — хороший выбор для этой цели, поскольку он не допускает NULL. Это может быть хорошей практикой даже при использовании внутренних объединений, поскольку вы никогда не узнаете, понадобится ли вам позже изменить внутреннее соединение на внешнее из-за изменения требований.

Агрегаты с двойным окунанием

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

 ВЫБЕРИТЕ C.custid, COUNT (O.orderid) AS numorders, SUM (O.freight) AS totalfreight,
    CAST (SUM (OD.qty * OD.unitprice * (1 - OD .iscount)) КАК ЧИСЛО (12, 2)) КАК totalval
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА C.custid = O.custid
    LEFT OUTER JOIN Продажи.Детали заказа как OD
      ON O.orderid = OD.orderid
  ГРУППА К.КУСТИДА
  ЗАКАЗ ОТ C.custid; 

Этот запрос объединяет Customers, Orders и OrderDetails, группирует строки по custid и должен вычислять агрегированные показатели, такие как количество заказов, общий фрахт и общая стоимость на клиента. Этот запрос генерирует следующий вывод:

 пользовательских номеров totalfreight totalval
  ------- ---------- ------------- ---------
  1 12 419,60 4273,00
  2 10 306.59 1402,95
  3 17 667,29 7023,98
  4 30 1447,14 13390,65
  5 52 4835,18 24927,58
  ...
  87 37 2611,93 15648,70
  88 19 546,96 6068,20
  89 40 4017,32 27363,61
  90 17 262,16 3161,35
  91 16 461,53 3531,95 

Вы можете найти здесь ошибку?

Заголовки заказов хранятся в таблице заказов, а соответствующие им строки заказов хранятся в таблице OrderDetails.Когда вы объединяете заголовки заказов с соответствующими строками заказов, заголовок повторяется в результате объединения на каждой строке. В результате агрегат COUNT (O.orderid) неправильно отражает количество строк заказа, а не количество заказов. Точно так же СУММ (O.freight) неправильно учитывает фрахт несколько раз за заказ — столько, сколько строк заказа в заказе. Единственное правильное агрегированное вычисление в этом запросе — это то, которое используется для вычисления итогового значения, поскольку оно применяется к атрибутам строк заказа: SUM (OD.кол-во * OD. цена * (1 — OD. скидка).

Чтобы получить правильное количество заказов, достаточно использовать агрегат отдельного количества: COUNT (DISTINCT O.orderid). Вы можете подумать, что то же самое исправление можно применить к вычислению общего фрахта, но это только внесет новую ошибку. Вот наш запрос с отдельными агрегатами, примененными к показателям заголовка заказа:

 ВЫБЕРИТЕ C.custid, COUNT (DISTINCT O.orderid) AS numorders, SUM (DISTINCT O.freight) AS totalfreight,
    CAST (SUM (OD.кол-во * OD.unitprice * (1 - OD.discount)) AS NUMERIC (12, 2)) AS totalval
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА C.custid = O.custid
    LEFT OUTER JOIN Sales.OrderDetails AS OD
      ON O.orderid = OD.orderid
  ГРУППА К.КУСТИДА
  ЗАКАЗ ОТ C.custid; 

Этот запрос генерирует следующий вывод:

 пользовательских номеров totalfreight totalval
  ------- ---------- ------------- ---------
  1 6 225,58 4273,00
  2 4 97.42 1402,95
  3 7 268,52 7023,98
  4 13 448,23 13390,65 *****
  5 18 1559,52 24927,58
  ...
  87 15 822,48 15648,70
  88 9 194,71 6068,20
  89 14 1353,06 27363,61
  90 7 87,66 3161,35 *****

  91 7 175,74 3531,95 

Количество заказов теперь правильное, но общая стоимость фрахта — нет.Вы заметили новую ошибку?

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

Используйте следующий запрос (требуется SQL Server 2017 или более поздней версии), чтобы определить нечеткие значения фрахта для одного и того же клиента:

 С C AS
  (
    ВЫБЕРИТЕ custid, фрахт,
      STRING_AGG (CAST (порядковый номер как VARCHAR (MAX)); ',')
        ВНУТРИ ГРУППЫ (ЗАКАЗАТЬ ПО ЗАКАЗУ) КАК заказы
    ОТ ПРОДАЖ.Заказы
    ГРУППА ПО таможне, фрахт
    ИМЕЕТ СЧЕТЧИК (*)> 1
  )
  ВЫБЕРИТЕ custid,
    STRING_AGG (CONCAT ('(фрахт:', фрахт, ', заказы:', заказы, ')'), ',') как дубликаты
  ОТ C
  ГРУППА ПО custid; 

Этот запрос генерирует следующий вывод:

 костидных дубликата
  ------- ---------------------------------------
  4 (фрахт: 23,72, заказов: 10743, 10953)
  90 (фрахт: 0,75, заказы: 10615, 11005) 

С этими выводами вы понимаете, что запрос с ошибкой сообщил о неверных общих значениях фрахта для клиентов 4 и 90.Запрос сообщил правильные общие значения фрахта для остальных клиентов, поскольку их значения фрахта оказались уникальными.

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

 С О КАК
  (
    ВЫБЕРИТЕ custid, COUNT (orderid) КАК числа, СУММ (фрахт) КАК totalfreight
    ОТ Sales.Orders
    ГРУППА ПО custid
  ),
  OD AS
  (
    ВЫБЕРИТЕ O.custid,
      CAST (SUM (OD.qty * OD.цена за единицу * (1 - OD. скидка)) КАК ЧИСЛО (12, 2)) КАК totalval
    ИЗ продаж.Заказы как O
      ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к Sales.OrderDetails AS OD
        ON O.orderid = OD.orderid
    ГРУППА ПО O.custid
  )
  ВЫБЕРИТЕ C.custid, O.numorders, O.totalfreight, OD.totalval
  ОТ продаж.Клиенты AS C
    ЛЕВЫЙ ВНЕШНИЙ СОЕДИНЕНИЕ O
      НА C.custid = O.custid
    ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ OD
      ВКЛ C.custid = OD.custid
  ЗАКАЗ ОТ C.custid; 

Этот запрос генерирует следующий вывод:

 пользовательских номеров totalfreight totalval
  ------- ---------- ------------- ---------
  1 6 225.58 4273,00
  2 4 97,42 1402,95
  3 7 268,52 7023,98
  4 13 471,95 13390,65 *****
  5 18 1559,52 24927,58
  ...
  87 15 822,48 15648,70
  88 9 194,71 6068,20
  89 14 1353,06 27363,61
  90 7 88,41 3161,35 *****
  91 7 175,74 3531,95 

Обратите внимание: общая стоимость фрахта для клиентов 4 и 90 теперь выше.Это правильные числа.

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

Итак, ошибка двойного погружения агрегатов исправлена. Однако в этом запросе потенциально есть еще одна ошибка.Вы можете это заметить? Я расскажу подробности о такой потенциальной ошибке в качестве четвертого случая, о котором я расскажу позже в разделе «ВНЕШНЕЕ противоречие при соединении».

НА-ГДЕ противоречие

Наша третья ошибка является результатом смешения ролей, которые должны играть предложения ON и WHERE. В качестве примера предположим, что вам была поставлена ​​задача сопоставить клиентов и заказы, которые они разместили с 12 февраля 2019 г., но также включить в выходные данные клиентов, которые с тех пор не размещали заказы. Вы пытаетесь решить задачу с помощью следующего запроса (назовите его Query 3):

 ВЫБРАТЬ C.custid, C. имя компании, O.orderid, O.orderdate
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА O.custid = C.custid
  ГДЕ O.orderdate> = '201'; 

При использовании внутреннего соединения ON и WHERE играют одни и те же роли фильтрации, и поэтому не имеет значения, как вы организуете предикаты между этими предложениями. Однако при использовании внешнего соединения, как в нашем случае, эти предложения имеют разное значение.

Предложение ON играет роль сопоставления, означающую, что будут возвращены все строки с сохраненной стороны соединения (в нашем случае — клиенты).Те, у которых есть совпадения на основе предиката ON, связаны со своими совпадениями и, как следствие, повторяются для каждого совпадения. Те, которые не имеют совпадений, возвращаются с NULL в качестве заполнителей в атрибутах несохраненной стороны.

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

Помните, что атрибуты с незащищенной стороны внешнего соединения (в нашем случае заказы) помечаются как NULL для внешних строк (несовпадения). Каждый раз, когда вы применяете фильтр, включающий элемент с незащищенной стороны соединения, предикат фильтра оценивается как неизвестный для всех внешних строк, что приводит к их удалению. Это соответствует трехзначной логике предикатов, которой следует SQL. Фактически в результате соединение становится внутренним соединением. Единственное исключение из этого правила — когда вы специально ищете NULL в элементе с несохраненной стороны, чтобы идентифицировать несовпадения (элемент IS NULL).

Наш ошибочный запрос дает следующий результат:

 custid название компании orderid orderdate
  ------- --------------- -------- ----------
  1 Клиент NRZBB 11011 2019-04-09
  1 Клиент NRZBB 10952 16.03.2019
  2 Клиент MLTDN 10926 04.03.2019
  4 Заказчик HFBZG 11016 2019-04-10
  4 Заказчик HFBZG 10953 16.03.2019
  4 Заказчик HFBZG 10920 2019-03-03
  5 Заказчик HGVLZ 10924 04.03.2019
  6 Заказчик XHXJV 11058 2019-04-29
  6 Заказчик XHXJV 10956 17.03.2019
  8 Клиент QUHWH 10970 2019-03-24
  ...
  20 Заказчик THHDP 10979 26.03.2019
  20 Клиент THHDP 10968 2019-03-23
  20 Заказчик THHDP 10895 18.02.2019
  24 Клиент CYZTN 11050 2019-04-27
  24 Клиент CYZTN 11001 2019-04-06
  24 Клиент CYZTN 10993 2019-04-01
  ...

  (Затронуто 195 строк) 

Предполагается, что желаемый результат будет иметь 213 строк, включая 195 строк, представляющих заказы, размещенные с 12 февраля 2019 г., и 18 дополнительных строк, представляющих клиентов, которые с тех пор не размещали заказы.Как видите, фактический результат не включает клиентов, которые не размещали заказы с указанной даты.

План этого запроса показан на рисунке 3.

Рисунок 3: План запроса 3

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

Я встречал случаи, когда люди пытались исправить ошибку, добавляя предикат OR O.orderid IS NULL для предложения WHERE, например:

 ВЫБЕРИТЕ C.custid, C.companyname, O.orderid, O.orderdate
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА O.custid = C.custid
  ГДЕ O.orderdate> = '201'
     ИЛИ O.orderid ЕСТЬ NULL; 

Единственный подходящий предикат — это тот, который сравнивает идентификаторы клиентов с двух сторон. Таким образом, само объединение возвращает клиентов, которые разместили заказы в целом, вместе с соответствующими им заказами, а также клиентов, которые вообще не размещали заказы, с NULL в их атрибутах заказа.Затем предикаты фильтрации фильтруют клиентов, разместивших заказы с указанной даты, а также клиентов, которые вообще не размещали заказы (клиенты 22 и 57). В запросе отсутствуют клиенты, которые разместили несколько заказов, но не с указанной даты!

Этот запрос генерирует следующий вывод:

 custid название компании orderid orderdate
  ------- --------------- -------- ----------
  1 Клиент NRZBB 11011 2019-04-09
  1 Клиент NRZBB 10952 16.03.2019
  2 Клиент MLTDN 10926 04.03.2019
  4 Заказчик HFBZG 11016 2019-04-10
  4 Заказчик HFBZG 10953 16.03.2019
  4 Заказчик HFBZG 10920 2019-03-03
  5 Заказчик HGVLZ 10924 04.03.2019
  6 Заказчик XHXJV 11058 2019-04-29
  6 Заказчик XHXJV 10956 17.03.2019
  8 Клиент QUHWH 10970 2019-03-24
  ...
  20 Заказчик THHDP 10979 26.03.2019
  20 Клиент THHDP 10968 2019-03-23
  20 Заказчик THHDP 10895 18.02.2019
  22 Клиент DTDMN NULL NULL
  24 Клиент CYZTN 11050 2019-04-27
  24 Клиент CYZTN 11001 2019-04-06
  24 Клиент CYZTN 10993 2019-04-01
  ...

  (Затронуто 197 строк) 

Чтобы правильно исправить ошибку, вам потребуется как предикат, сравнивающий идентификаторы клиентов с двух сторон, так и предикат с датой заказа, который будет считаться совпадающим предикатом.Для этого оба должны быть указаны в предложении ON, например (назовите этот запрос 4):

 ВЫБЕРИТЕ C.custid, C.companyname, O.orderid, O.orderdate
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА O.custid = C.custid
     И O.orderdate> = '201'; 

Этот запрос генерирует следующий вывод:

 custid название компании orderid orderdate
  ------- --------------- -------- ----------
  1 Клиент NRZBB 11011 2019-04-09
  1 Клиент NRZBB 10952 16.03.2019
  2 Клиент MLTDN 10926 04.03.2019
  3 Клиент KBUDE NULL NULL
  4 Заказчик HFBZG 11016 2019-04-10
  4 Заказчик HFBZG 10953 16.03.2019
  4 Заказчик HFBZG 10920 2019-03-03
  5 Заказчик HGVLZ 10924 04.03.2019
  6 Заказчик XHXJV 11058 2019-04-29
  6 Заказчик XHXJV 10956 17.03.2019
  7 Клиент QXVLA NULL NULL
  8 Клиент QUHWH 10970 2019-03-24
  ...
  20 Заказчик THHDP 10979 26.03.2019
  20 Клиент THHDP 10968 2019-03-23
  20 Заказчик THHDP 10895 18.02.2019
  21 Клиент KIDPX NULL NULL
  22 Клиент DTDMN NULL NULL
  23 Клиент WVFAF NULL NULL
  24 Клиент CYZTN 11050 2019-04-27
  24 Клиент CYZTN 11001 2019-04-06
  24 Клиент CYZTN 10993 2019-04-01
  ...

  (Затронуто 213 строк) 

План этого запроса показан на рисунке 4.

Рисунок 4: План запроса 4

Как видите, на этот раз оптимизатор обработал соединение как внешнее соединение.

Это очень простой запрос, который я использовал в иллюстративных целях. С гораздо более сложными и сложными запросами даже опытные разработчики могут с трудом определить, принадлежит ли предикат предложению ON или предложению WHERE. Что упрощает мне задачу, так это просто спросить себя, является ли предикат совпадающим или фильтрующим.Если первое, то это относится к предложению ON; в последнем случае он относится к предложению WHERE.

OUTER-INNER объединить противоречие

Наша четвертая и последняя ошибка в некотором смысле является разновидностью третьей ошибки. Обычно это происходит в запросах с несколькими соединениями, где вы смешиваете типы соединений. В качестве примера предположим, что вам нужно объединить таблицы Customers, Orders, OrderDetails, Products и Suppliers, чтобы определить пары клиент-поставщик, которые имели совместную деятельность. Вы пишете следующий запрос (назовите его Query 5):

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
    С.custid, C.companyname КАК клиент,
    S.supplierid, S.companyname AS поставщик
  ОТ продаж.Клиенты AS C
    ВНУТРЕННИЙ ПРИСОЕДИНЯЙТЕСЬ к продажам.Заказы как O
      НА O.custid = C.custid
    ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
    INNER JOIN Production.Products AS P
      ON P.productid = OD.productid
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Поставщики AS S
      ПО S.supplierid = P.supplierid; 

Этот запрос генерирует следующий вывод с 1236 строками:

 заказчик поставщик поставщик
  ------- --------------- ----------- ---------------
  1 Заказчик NRZBB 1 Поставщик SWRXU
  1 Заказчик NRZBB 3 Поставщик STUAZ
  1 Заказчик NRZBB 7 Поставщик GQRCV
  ...
  21 Заказчик KIDPX 24 Поставщик JNNES
  21 Заказчик KIDPX 25 Поставщик ERVYZ
  21 Заказчик KIDPX 28 Поставщик OAVQT
  23 Заказчик WVFAF 3 Поставщик STUAZ
  23 Заказчик WVFAF 7 Поставщик GQRCV
  23 Заказчик WVFAF 8 Поставщик BWGYE
  ...
  56 Заказчик QNIVZ 26 Поставщик ZWZDM
  56 Заказчик QNIVZ 28 Поставщик OAVQT
  56 Заказчик QNIVZ 29 Поставщик OGLRK
  58 Заказчик AHXHT 1 Поставщик SWRXU
  58 Заказчик AHXHT 5 Поставщик EQPNC
  58 Заказчик AHXHT 6 Поставщик QWUSF
  ...

  (Затронуто 1236 строк) 

План этого запроса показан на рисунке 5.

Рисунок 5: План запроса 5

Все объединения в плане обрабатываются как внутренние объединения, как и следовало ожидать.

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

Предположим, вы получили требование изменить запрос таким образом, чтобы он включал клиентов, которые не разместили заказы. Напомним, что в настоящее время у нас есть два таких клиента (с идентификаторами 22 и 57), поэтому желаемый результат должен иметь 1238 строк. Распространенной ошибкой в ​​таком случае является изменение внутреннего соединения между клиентами и заказами на левое внешнее соединение, но оставление всех остальных соединений внутренними, например:

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
    С.custid, C.companyname КАК клиент,
    S.supplierid, S.companyname AS поставщик
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА O.custid = C.custid
    ВНУТРЕННИЕ ПРИСОЕДИНЯЙТЕСЬ к Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
    INNER JOIN Production.Products AS P
      ON P.productid = OD.productid
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Поставщики AS S
      ПО S.supplierid = P.supplierid; 

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

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

Обычная попытка исправить ошибку — сделать все соединения левым внешним соединением, например:

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
    C.custid, C.companyname AS клиент,
    С.supplierid, С.название компании КАК поставщик
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      НА O.custid = C.custid
    LEFT OUTER JOIN Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
    ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Продукция AS P
      ON P.productid = OD.productid
    LEFT OUTER JOIN Производство. Поставщики AS S
      ПО S.supplierid = P.supplierid; 

Этот запрос генерирует следующий вывод, который включает клиентов 22 и 57:

 заказчик поставщик поставщик
  ------- --------------- ----------- ---------------
  1 Заказчик NRZBB 1 Поставщик SWRXU
  1 Заказчик NRZBB 3 Поставщик STUAZ
  1 Заказчик NRZBB 7 Поставщик GQRCV
  ...
  21 Заказчик KIDPX 24 Поставщик JNNES
  21 Заказчик KIDPX 25 Поставщик ERVYZ
  21 Заказчик KIDPX 28 Поставщик OAVQT
  22 Клиент DTDMN NULL NULL
  23 Заказчик WVFAF 3 Поставщик STUAZ
  23 Заказчик WVFAF 7 Поставщик GQRCV
  23 Заказчик WVFAF 8 Поставщик BWGYE
  ...
  56 Заказчик QNIVZ 26 Поставщик ZWZDM
  56 Заказчик QNIVZ 28 Поставщик OAVQT
  56 Заказчик QNIVZ 29 Поставщик OGLRK
  57 Клиентский WVAXS NULL NULL
  58 Заказчик AHXHT 1 Поставщик SWRXU
  58 Заказчик AHXHT 5 Поставщик EQPNC
  58 Заказчик AHXHT 6 Поставщик QWUSF
  ...

  (Затронуто 1238 строк) 

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

Другая проблема заключается в том, что при использовании внешних объединений вы накладываете на оптимизатор больше ограничений в плане перегруппировок, которые ему разрешено исследовать в рамках оптимизации с упорядочением соединений. Оптимизатор может переставить соединение A LEFT OUTER JOIN B на B RIGHT OUTER JOIN A, но это практически единственное изменение, которое ему разрешено исследовать.С помощью внутренних объединений оптимизатор также может переупорядочивать таблицы, не только переворачивая стороны, например, он может изменить порядок соединения (join (join (join (A, B), C), D), E)))) для соединения (A, join (B, join (join (E, D), C))), как показано ранее на рисунке 5.

Если задуматься, то на самом деле вам нужно левое присоединение к Customers с результатом внутренних соединений между остальными таблицами. Очевидно, этого можно добиться с помощью табличных выражений. Однако T-SQL поддерживает еще один прием. На самом деле порядок логического соединения определяется не совсем порядком таблиц в предложении FROM, а скорее порядком предложений ON.Однако для того, чтобы запрос был действительным, каждое предложение ON должно появиться прямо под двумя модулями, к которым оно присоединяется. Итак, чтобы рассматривать соединение между клиентами и остальными как последнее, все, что вам нужно сделать, это переместить предложение ON, которое связывает клиентов, а остальные должны появиться последними, например:

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
    C.custid, C.companyname AS клиент,
    S.supplierid, S.companyname AS поставщик
  ОТ продаж.Клиенты AS C
    LEFT OUTER JOIN Sales.Orders AS O
      - двигаться отсюда -----------------------
    ВНУТРЕННИЕ СОЕДИНЕНИЕ Продажи.Детали заказа как OD -
      ON OD.orderid = O.orderid -
    INNER JOIN Production.Products AS P -
      ON P.productid = OD.productid -
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Поставщики AS S -
      НА S.supplierid = P.supplierid -
      НА O.custid = C.custid; - <- сюда - 

Теперь логический порядок соединения следующий: leftjoin (Клиенты, join (join (join (Orders, OrderDetails), Products), Suppliers)). На этот раз вы сохраните клиентов, которые не разместили заказы, но не сохраните заголовки заказов, у которых нет совпадающих строк заказа.Кроме того, вы предоставляете оптимизатору полную гибкость упорядочивания соединений во внутренних соединениях между заказами, деталями заказа, продуктами и поставщиками.

Единственный недостаток этого синтаксиса - удобочитаемость. Хорошей новостью является то, что это можно легко исправить, используя круглые скобки, например так (назовите этот запрос 6):

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
    C.custid, C.companyname AS клиент,
    S.supplierid, S.companyname AS поставщик
  ОТ продаж.Клиенты AS C
    ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
      (Sales.Orders AS O
          ВНУТРЕННИЕ СОЕДИНЕНИЕ Продажи.Детали заказа как OD
            ON OD.orderid = O.orderid
          INNER JOIN Production.Products AS P
            ON P.productid = OD.productid
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Поставщики AS S
            НА S.supplierid = P.supplierid)
        НА O.custid = C.custid; 

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

План этого запроса показан на рисунке 6.

Рисунок 6: План запроса 6

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

Заключение

В этой статье я рассмотрел четыре классических ошибки, связанные с объединениями. При использовании внешних соединений вычисление агрегата COUNT (*) обычно приводит к ошибке. Лучше всего применять агрегат к столбцу, не допускающему NULL, с незащищенной стороны соединения.

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

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

В запросах с несколькими соединениями левое внешнее соединение, за которым следует внутреннее соединение, или правое внешнее соединение, при котором вы сравниваете элемент с несохраненной стороны соединения с другими (кроме теста IS NULL), внешние ряды левого внешнего соединения отбрасываются. Чтобы избежать этой ошибки, вы хотите применить левое внешнее соединение последним, и это может быть достигнуто путем сдвига предложения ON, которое соединяет сохраненную сторону этого соединения с остальными, чтобы появиться последним.Для ясности используйте круглые скобки, даже если они не требуются.

JOIN Relationships and JOINing Tables

Последнее изменение: 7 февраля 2021 г.

До сих пор мы работали с каждой таблицей отдельно, но, как вы, возможно, догадались по таблицам с названиями треков , альбомов и исполнителей и некоторым столбцам, имеющим имена вроде album_id , это возможно ОБЪЕДИНЕНИЕ этих таблиц вместе для получения результатов из обеих!

Перед тем, как мы начнем ОБЪЕДИНЕНИЕ данных, необходимо описать несколько ключевых концепций:

Отношения

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

Отношения для трех таблиц, которые мы использовали до сих пор, визуализированы здесь:

Первичные ключи

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

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

Внешние ключи

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

Другой пример - album_id в базе данных треков . Ранее в этом уроке мы искали все треки с album_id из 89. Мы также искали, какие альбомы имеют id из 89, и обнаружили, что треки относятся к альбому «American Idiot». ЗАДАЧИ: Исправьте этот абзац / пример.

Очень часто внешний ключ получает имя в формате [другое имя таблицы] _id как album_id и artist_id , но опять же это не требуется.Столбец внешнего ключа может быть любого типа и связан с любым столбцом в другой таблице, если этот другой столбец является первичным ключом, однозначно идентифицирующим одну строку.

Почему отношения

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

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

Это уже становится запутанным даже для нашего небольшого примера и просто нереально для внедрения в реальной компании. Мир (и данные) лучше работают с отношениями.

СОЕДИНЕНИЕ СТОЛОВ

Итак, приступим! Чтобы указать, как мы соединяем две таблицы, мы используем следующий формат

  ВЫБРАТЬ * ИЗ [table1] JOIN [table2] ON [table1.primary_key] = [table2.foreign_key];
  

Обратите внимание, что порядок таблиц table1 и table2 и ключей действительно не имеет значения.

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

Попробуйте сами присоединиться к таблицам треков и альбомов.

Мы даже можем объединить все 3 таблицы вместе, если нам нужно использовать несколько команд JOIN.

Типы JOIN

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

Мы можем продемонстрировать каждый из них, выполнив СЧЁТ (*) и показывая, сколько строк находится в каждом наборе данных.Во-первых, следующий запрос показывает нам, сколько столбцов находится в таблицах исполнителей и альбомов .

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Внутреннее соединение получит список всех альбомов, связанных с их исполнителями. Итак, мы знаем, что до тех пор, пока у каждого альбома есть исполнитель в базе данных (а он есть), мы получим 347 строк данных, поскольку в базе данных 347 альбомов.И действительно, это то, что мы получаем от INNER JOIN:

ПРАВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

OUTER JOIN будет извлекать все соединенные строки, а также любые строки в указанном направлении (ВПРАВО или ВЛЕВО), которые не имели никаких соединений. В нашей базе данных у многих артистов нет альбомов. Итак, если мы выполним здесь RIGHT OUTER JOIN, который указывает, что указанная справа таблица художников является целевой OUTER таблицей, мы вернем все совпадения, которые мы сделали из INNER JOIN выше и , все несоответствующие строки из таблица художников .И здесь мы показываем, что делаем:

418 ВНЕШНИХ результатов минус 347 ВНУТРЕННИХ результатов показывает, что в базе данных 71 артиста , которые не связаны ни с одним из наших альбомов . Можете ли вы дважды проверить, что это так с SQL, добавив условие WHERE в приведенный выше запрос, фильтрующий результаты для тех, где нет альбомов . id ?

ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

Если бы мы выбрали ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, мы бы выбрали таблицу альбомов как ВНЕШНЮЮ цель.И здесь мы проверяем, нет ли дополнительных альбомов, с которыми не связан исполнитель.

ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ

И, наконец, ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ будет возвращать результаты СОЕДИНЕНИЯ и любые несоответствующие строки из или таблиц. Мы знаем, что в случае этого набора данных они будут поступать только из таблицы исполнителей, и результат будет таким же, как и наше ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ выше.

Объединяя все вместе

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

Прокрутив вправо, вы увидите, что имеется много столбцов, так как результат содержит все столбцы каждого объединенного набора. Вы также можете увидеть конфликт, поскольку есть 2 столбца: название , имя . Один взят из таблицы дорожек , а другой - из таблицы исполнителей , и набор результатов не обрабатывает это должным образом. Он просто использует имена из таблицы исполнителя в обоих столбцах!

Мы можем исправить это, используя псевдонимы.Далее мы пытаемся получить названия 8 треков вместе с именем исполнителя. Запустите, и вы убедитесь в этом сами. Можете ли вы исправить это смешение в обоих столбцах с одинаковыми именами, используя псевдонимы AS "Track" и AS "Artist" .

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

Написано:

Дэйв Фаулер

Автор отзыва:

Мэтт Дэвид

.