Понимание того, как работает JOIN, когда задействованы 3 или более таблиц. [SQL]

Интересно, может ли кто-нибудь помочь улучшить мое понимание JOINs в SQL. [Если это важно для проблемы, я думаю, MS SQL Server конкретно.]

Возьмем 3 таблицы A, B [A, связанные с некоторыми A.AId], и C [B, связанные с C некоторым B.BId]

Если я составляю запрос, например,

SELECT *
FROM A JOIN B 
ON A.AId = B.AId

Все хорошо - я доволен тем, как это работает.

Что происходит, когда таблица C (или добавляются другие D, E,....)

В ситуации

SELECT *
FROM A JOIN B 
  ON A.AId = B.AId
JOIN C ON C.BId = B.BId

К чему присоединяется C? - это таблица B (и значения в таблице B?) Или это какой-то другой временный результирующий набор, являющийся результатом объединения A + B, к которому привязана таблица C?

[Импликация - это не все значения, которые находятся в таблице B, обязательно будут во временном результирующем наборе A + B на основе условия соединения для A, B]

Конкретный (и довольно надуманный) пример того, почему я спрашиваю, заключается в том, что я пытаюсь понять поведение, которое я вижу в следующем:

Tables 
Account (AccountId, AccountBalanceDate, OpeningBalanceId, ClosingBalanceId)
Balance (BalanceId)
BalanceToken (BalanceId, TokenAmount)

Where:
Account->Opening, and Closing Balances are NULLABLE 
(may have opening balance, closing balance, or none)

Balance->BalanceToken is 1:m - a balance could consist of many tokens

Концептуально, итоговый баланс даты, будет означать открытие баланса

Если я пытался найти список всех открытых и закрывающих балансов для учетной записи

Я мог бы сделать что-то вроде

SELECT AccountId
, AccountBalanceDate
, Sum (openingBalanceAmounts.TokenAmount) AS OpeningBalance
, Sum (closingBalanceAmounts.TokenAmount) AS ClosingBalance
FROM Account A 
   LEFT JOIN BALANCE OpeningBal 
      ON A.OpeningBalanceId = OpeningBal.BalanceId
   LEFT JOIN BALANCE ClosingBal 
      ON A.ClosingBalanceId = ClosingBal.BalanceId
   LEFT JOIN BalanceToken openingBalanceAmounts 
      ON openingBalanceAmounts.BalanceId = OpeningBal.BalanceId
   LEFT JOIN BalanceToken closingBalanceAmounts 
      ON closingBalanceAmounts.BalanceId = ClosingBal.BalanceId
   GROUP BY AccountId, AccountBalanceDate

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

[Я могу исправить с помощью DISTINCT - но я пытаюсь понять, почему происходит то, что происходит]

Мне сказали, что проблема в том, что отношения между Balance и BalanceToken равны 1: M - и что, когда я привожу последний JOIN, я получаю дубликаты, потому что третий JOIN уже несколько раз вводил BalanceIds в ( Я предполагаю) временный набор результатов.

Я знаю, что таблицы примеров не соответствуют хорошему дизайну БД

Извинения за эссе, спасибо за любое восстание:)

Изменить в ответ на вопрос Marc

Концептуально для учетной записи не должно быть дубликатов в BalanceToken для учетной записи (на учетную дату). Я думаю, проблема возникает из-за того, что 1 баланс счета/счета-счетов-счетов является тем, что баланс счета начинается на следующий день - поэтому, когда я присоединяюсь к Баланс, BalanceToken несколько раз, чтобы получить открывающие и закрывающие балансы. Думаю, балансы (BalanceId's) несколько раз приводятся в "результат". Если это поможет прояснить второй пример, подумайте об этом как о ежедневной сверке - следовательно, о левом соединении - закрытие счета открытия (и/или) может не быть рассчитано для данной учетной записи/accountdate.

Ответы

Ответ 1

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

Оптимизатор имеет план, который включает в себя порядок соединения. Это могут быть A, B, C или C, B, A или любая из комбинаций
Механизм выполнения запроса применяет любые предикаты (предложение WHERE) к первой таблице, которая не включает ни одну из других таблиц. Он выбирает столбцы, упомянутые в условиях JOIN или в списке SELECT или в списке ORDER BY. Вызвать этот результат A
Он присоединяет этот результирующий набор ко второй таблице. Для каждой строки она присоединяется ко второй таблице, применяя любые предикаты, которые могут применяться ко второй таблице. Это приводит к еще одному временному набору результатов.
Затем он присоединяется к финальной таблице и применяет ORDER BY

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

Например, на самом деле нет необходимости создавать полные результирующие наборы. Вместо этого ORDER BY можно выполнить путем доступа к данным с использованием индекса в первую очередь. Существует много типов объединений, которые могут быть выполнены также.

Ответ 2

Мы знаем, что данные из B будут отфильтрованы (внутренним) соединением до A (данные в A также будут отфильтрованы). Итак, если мы (внутренние) соединяемся от B до C, значит, набор C также фильтруется отношением к A. Также обратите внимание, что любые дубликаты из соединения будут включены.

Тем не менее; какой порядок это происходит, зависит от оптимизатора; он может решить сначала выполнить соединение B/C, затем ввести A или любую другую последовательность (вероятно, основанную на оценочном числе строк из каждого соединения и соответствующих индексов).

ОДНАКО; в следующем примере вы используете соединение LEFT OUTER; поэтому Account вообще не фильтруется, и, вполне возможно, мой дубликат, если какая-либо из других таблиц имеет несколько совпадений.

Существуют ли дубликаты (для каждой учетной записи) в BalanceToken?

Ответ 3

Я часто нахожу, что это помогает просмотреть фактический план выполнения. В студии анализатора запросов/управления вы можете включить это для запросов в меню Query или использовать Ctrl + M. После выполнения запроса выполненный план показан на другой вкладке результатов. Из этого вы увидите, что C и B соединяются сначала, а затем результат объединяется с A. План может варьироваться в зависимости от информации, которую СУБД имеет, поскольку оба соединения являются внутренними, что делает его A-и-B-и-C, Я имею в виду, что результат будет тем же самым, независимо от того, что связано первым, но время, которое требуется, может сильно различаться, и именно здесь вступает в игру оптимизатор и подсказки.

Ответ 4

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

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

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

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

Измените начальную часть

ВЫБОР учетной записи Account Account Accountate, sum (...) как балансировка, sum (...) как closbalance FROM

просто

"SELECT * FROM"

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

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