Join t sql: Joins (SQL Server) — SQL Server

T-SQL.RU | Join Hints

Join Hints (LOOP | HASH | MERGE | REMOTE) — Подсказки оптимизатору запросов на выбор определенной стратегии соединения двух таблиц (используется в SELECT, UPDATE и DELETE).

Оптимизатор запросов SQL Server обычно автоматически выбирает наилучший план выполнения запроса. Поэтому подсказки, в том числе <подсказки_по_соединению>, рекомендуется использовать только опытным пользователям и администраторам базы данных в случае крайней необходимости.

Без явного указания аргумента (LOOP | HASH | MERGE | REMOTE) оптимизатор выбирает, на его взгляд, самый оптимальный план. Но мы всегда можем повлиять на него, если явно укажем подсказку.

Ниже разберем каждый из аргументов подробнее.

Loop Join

Соединение LOOP JOIN, называемое также nested iteration, использует одну таблицу в качестве внешней (на графическом плане она является верхней), а второй в качестве внутренней (нижней). LOOP JOIN построчно сравнивает внешнюю таблицу с внутренней. В цикле для каждой внешней строки производится сканирование внутренней таблицы и выводятся совпадающие строки.

В простейшем случае во время поиска целиком сканируется таблица или индекс (naive nested loops join). Если при поиске используется индекс, то такой поиск называется index nested loops join. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется temporary index nested loops join. Оптимизатор сам выбирает один из этих поисков.

LOOP JOIN является особенно эффективным в случае, когда внешняя таблица сравнительно невелика, а внутренняя гораздо больше и для неё существуют индексы. В запросах с небольшим объёмом строк, index nested loops join превосходит как MERGE JOIN, так и HASH JOIN. Однако в больших запросах LOOP JOIN часто являются не лучшим вариантом.

Для демонстрации создадим 2 тестовые таблицы:

CREATE TABLE LoopLeftTable (ID INT)
CREATE TABLE LoopRightTable (ID INT IDENTITY PRIMARY KEY)

И посмотрим план запроса:

SELECT * FROM
	LoopLeftTable
	INNER jOIN
	LoopRightTable
	ON LoopLeftTable.ID=LoopRightTable.ID

Как и описано выше оптимизатор выбрал LOOP JOIN. Но если мы вставим в таблицу достаточно большое кол-во строк, то оптимизатор откажется от соединения LOOP JOIN:

INSERT INTO LoopLeftTable
SELECT 1
GO 10000

Оптимизатор при выполнении запроса выбрал HASH JOIN, так как посчитал, что стимость этого соединения будет ниже. Но если мы не доверяем оптимизатору то можем явно указать ему использовать LOOP JOIN:

SELECT * FROM
	LoopLeftTable
	INNER LOOP jOIN
	LoopRightTable
	ON LoopLeftTable.ID=LoopRightTable.ID

Кстати, если сравнить стоимость выполнения запроса выбранного оптимизатором и наш с подсказкой, то можно убедиться, что оптимизатор действительно выбрал верный план. (http://msdn.microsoft.com/en-us/library/ms191318.aspx)

Аргумент LOOP не может указываться вместе с параметрами RIGHT или FULL в качестве типа соединения.

 

Merge Join

Merge Join требует сортировки обоих наборов входных данных по столбцам слияния, которые определены предложениями равенства (ON) предиката соединения. (т.е. если мы имеем предикат соединения «T1.a = T2.b», таблица T1 должна быть отсортирована по T1.a, а таблица T2 должна быть сортирована по T2.b).

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

MERGE JOIN может поддерживать слияние «многие ко многим». В этом случае, при каждом соединении двух строк нужно сохранять копию каждой строки второго входного потока. Это позволяет, при последующем обнаружении в первом входном потоке дубликатов строк, воспроизвести сохраненные строки. С другой стороны, если будет ясно, что следующая строка первого входного потока не является дубликатом, от сохраненных строк можно отказаться. Такие строки сохраняются во временно таблице базы tempdb. Размер дискового пространства, который для этого необходим, зависит от числа дубликатов во втором входном потоке.

MERGE JOIN «один ко многим» всегда будет эффективнее слияния «многие ко многим», поскольку для него не требуется временная таблица. Для того, что бы задействовать слиянием «один ко многим», оптимизатор должен иметь возможность определить, что один из входных потоков состоит из уникальных строк. Как правило, это означает, что у такого входного потока существует уникальный индекс или в плане запроса присутствует явным образом оператор (например, сортировка при DISTINCT или группировка), который гарантирует, что строки на входе будут уникальны.

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

Для демонстрации создадим 2 таблицы очень похожие на те, что были созданы в примере с LOOP JOIN:

CREATE TABLE MergeLeftTable (ID INT IDENTITY PRIMARY KEY)
CREATE TABLE MergeRightTable (ID INT IDENTITY PRIMARY KEY)

Если посмотреть какой план выбрал оптимизатор на пустых таблицах, если в качестве запроса указать:

SELECT MergeLeftTable.ID FROM
	MergeLeftTable
	INNER jOIN
	MergeRightTable
	ON MergeLeftTable.ID=MergeRightTable.ID

то окажется, что опять используется LOOP JOIN.

Можно явно указать оптимизатору использовать MERGE JOIN:

SELECT MergeLeftTable.ID FROM
	MergeLeftTable
	INNER MERGE jOIN
	MergeRightTable
	ON MergeLeftTable. ID=MergeRightTable.ID

Но оптимизатор сам выберет для этих таблиц MERGE JOIN, если наполнить их (таблицы) хотя бы небольшим кол-ом данных:

INSERT INTO MergeLeftTable
DEFAULT VALUES
GO 40

INSERT INTO MergeRightTable
DEFAULT VALUES
GO 40

Тогда первый запрос для этих таблиц без подсказки выполнится по плану с MERGE JOIN: (http://msdn.microsoft.com/en-us/library/ms190967.aspx)

 

Hash Join

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

Соединение происходит с использованием хеширования, вычесляя хеш записей из меньшей таблицы (Build-таблица) и вставляя их в хеш-таблицу, затем обрабатывается большая таблица (Probe-таблица) по одной записи, сканируя хеш-таблицу для поиска совпадений.

Создадим две таблицы для демонстрации:

CREATE TABLE HashLeftTable (ID INT)
CREATE TABLE HashRightTable (ID INT)

Если посмотреть план запроса:

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

То увидем, что оптимизатор выбрал Hash Join:

Но если оптимизатор выбрал другой план, но мы явно желаем использовать хеш-объединение, то мы так же можем «подсказать» это оптимизатору:

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER HASH jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

Hash Join бывают 3х видов:

  • In-Memory Hash Join Когда таблицы небольшого размера и могут полностью быть помещенны в память

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

  • Recursive Hash Join Этот вид объединения используется для сложных таблиц и для таблиц, которые являются очень большими и требуют многоуровневое соединение в несколько шагов.

(http://msdn.microsoft.com/en-us/library/ms189313.aspx)

 

Remote Join

Remote Join может быть использован только при операциях INNER JOIN.

Remote Join задает, что операция соединения проводится на странице таблицы, расположенной справа. Данный аргумент удобно использовать в случае, когда таблица, расположенная слева, является локальной, а справа располагается удаленная таблица (Linked Server). Аргумент REMOTE может использоваться в случае, когда в таблице слева содержится меньшее количество строк, чем в таблице справа. Если таблица, расположенная справа, является локальной, то операция соединения также проводится локально. Если обе таблицы являются удаленными, но

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

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

SELECT HashLeftTable.ID FROM
	HashLeftTable
	INNER REMOTE jOIN
	HashRightTable
	ON HashLeftTable.ID=HashRightTable.ID

 

 

 

По теме:

  • Loop Join

  • Merge Join

  • Hash Join

Язык SQL – объединение JOIN | Info-Comp.ru

Продолжаем изучать основы SQL, и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN, другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.

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

Примечание! Все примеры будем писать в Management Studio SQL Server 2008.

Мы с Вами уже давно изучаем основы SQL, и если вспомнить начинали мы с оператора select, и вообще было уже много материала на этом сайте по SQL, например:

  • Строковые функции SQL – Примеры использования
  • Перекрестные запросы SQL или кросс табличные выражения
  • Как написать функцию на PL/pgSQL

И много другого, даже уже рассматривали объединения union и union all, но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.

И начнем мы как обычно с небольшой теории.

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

Содержание

  1. Объединение SQL LEFT и RIGHT JOIN
  2. Объединение SQL INNER JOIN
  3. Объединение SQL CROSS JOIN

Объединение SQL LEFT и RIGHT JOIN

LEFT JOIN – это объединение данных по левому ключу, т.е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.

RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.

Теперь давайте рассматривать примеры, и для начала создадим две таблицы:

   
   CREATE TABLE [test_table](
        [number] [numeric](18, 0) NULL,
        [text] [varchar](50) NULL
   ) ON [PRIMARY]

   GO
   CREATE TABLE [test_table_2](
        [number] [numeric](18, 0) NULL,
        [text] [varchar](50) NULL
   ) ON [PRIMARY]

   GO

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

Теперь давайте напишем запрос с объединением этих таблиц по ключу number, для начала по LEFT:

Код:

   
   SELECT t1. number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text 
   FROM test_table t1 
   LEFT JOIN test_table_2 t2 ON t1.number=t2.number

Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.

А что будет, если бы мы объединяли по средствам right join, а было бы вот это:

Код:

   
   SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text 
   FROM test_table t1 
   RIGHT JOIN test_table_2 t2 ON t1.number=t2.number

Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.

Объединение SQL INNER JOIN

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


Код:

   
   SELECT t1.number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text 
   FROM test_table t1 
   INNER JOIN test_table_2 t2 on t1.number=t2.number

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

   
   ALTER TABLE test_table ADD  number2 INT
   ALTER TABLE test_table_2 ADD number2 INT

Обновим наши данные, просто проставим в колонку number2 значение 1:

   
   UPDATE test_table SET number2 = 1
   UPDATE test_table_2 SET number2 = 1

И давайте напишем запрос с объединением по двум ключам:

   
   SELECT t1. number as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as t2_text 
   FROM test_table t1 
   INNER JOIN test_table_2 t2 ON t1.number=t2.number AND t1.number2=t2.number2

И результат будет таким же, как и в предыдущем примере:

Но если мы, допустим во второй таблице в одной строке изменим, поле number2 на значение скажем 2, то результат будет уже совсем другой.

Обновим:

   
   UPDATE test_table_2 set number2 = 2 
   WHERE number=1

Запрос тот же самый, а вот результат:

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

Объединение SQL CROSS JOIN

CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:

Код:

   
   SELECT  t1. number as t1_number,t1.text as t1_text, 
           t2.number as t2_number, t2.text as t2_text 
   FROM test_table t1 
   CROSS JOIN test_table_2 t2

Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.

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

Код:

   
   SELECT t1.number as t1_number, t1.text as t1_text, 
          t2.number as t2_number, t2.text as t2_text,
          t3.number as t3_number, t3.text as t3_text,
          t4.number as t4_number, t4.text as t4_text  
   FROM test_table t1 
   LEFT JOIN test_table_2 t2 on t1. number=t2.number 
   RIGHT JOIN test_table_2 t3 on t1.number=t3.number 
   INNER JOIN test_table_2 t4 on t1.number=t4.number

Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.

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

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

СОЕДИНЕНИЯ T-SQL — javatpoint

следующий →
← предыдущая

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

См. две приведенные ниже таблицы, (a) КЛИЕНТЫ таблица выглядит следующим образом —

ID ИМЯВОЗРАСТ АДРЕС ЗАРПЛАТА
1 Гамильтон 23 Австралия 34000
2 Уорнер 34 Англия 22000
3 Мартин 28 Китай 25000
4 Мерцание 30 Турция 50000
5 Тину 32 Непал 45000
6 Михал 31 Бутан 20000
7 Харпер 20 Бангладеш 15000

(б) Другая таблица ЗАКАЗЫ, выглядит следующим образом —

OID ДАТА CUSTOMER_ID СУММА
100 08. 10.2020 00.00.000 3 15000
101 20.11.2020 00.00.000 2 15600
102 08.10.2020 00.00.000 3 30000
103 2019-05-20 00.00.000 4 20600

Давайте объединим две таблицы в нашем операторе SELECT, как показано ниже —

ВЫБЕРИТЕ ID, ИМЯ, ВОЗРАСТ, СУММУ
ОТ КЛИЕНТОВ, ЗАКАЗЫ
ГДЕ CUSTOMERS.ID = ЗАКАЗЫ.CUSTOMER_ID
ИЛИ
ВЫБЕРИТЕ A.ID, A.ИМЯ, A.ВОЗРАСТ, B.СУММА
ОТ КЛИЕНТОВ A внутреннее соединение ЗАКАЗОВ B на A.ID = B.Customer_ID

Команда выдает заданный результат.

Соединение выполняется в предложении WHERE. Многие операторы будут использоваться для соединения таблиц, например 9.0008 =, <, >, <>, <=, >=, ! =, КАК, МЕЖДУ и НЕ .

Типы соединений в MS SQL:

Существует множество типов соединений, используемых в MS SQL Server –

.

  • ВНУТРЕННЕЕ СОЕДИНЕНИЕ
  • ЛЕВОЕ СОЕДИНЕНИЕ
  • ПРАВОЕ СОЕДИНЕНИЕ
  • ПОЛНОЕ СОЕДИНЕНИЕ
  • САМОСОЕДИНЕНИЕ
  • ДАРТОВОЕ СОЕДИНЕНИЕ

Серийный номер Описание
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Возвращает строки при совпадении в таблицах.
ЛЕВОЕ СОЕДИНЕНИЕ Выбирает записи из первой ( самой левой ) таблицы с соответствующими правильными записями таблицы.
ПРАВОЕ СОЕДИНЕНИЕ Выбирает все строки из правой таблицы, если в левой таблице не найдено совпадений.
ПОЛНОЕ СОЕДИНЕНИЕ Если в одной из таблиц есть какое-либо совпадение, строки объединяются в полное соединение.
САМОСОЕДИНЕНИЕ SELF JOIN используется для присоединения к таблице, если в ней две таблицы, переименуйте хотя бы одну таблицу в MS SQL SERVER .
ДЕКОРОВОЕ СОЕДИНЕНИЕ Декартово произведение устанавливает рекорд количества записей многосвязной таблицы в T-SQL .

Пример:

Вот две таблицы с именами Color и Size, , которые мы объединили с помощью полного соединения.

Следующая тема Хранимые процедуры T-SQL

← предыдущая
следующий →

Написание внешних соединений в T-SQL

Проверка внутреннего соединения

Наиболее часто используемым соединением является ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Этот тип соединения
объединяет строки из двух таблиц только тогда, когда они совпадают при объединении
состояние. Обычно первичный ключ из одной таблицы совпадает с внешним ключом
на другой таблице, но условия соединения могут быть более сложными.

(Примечание. Большая часть информации в этой статье применима к представлениям
а также столы. Для простоты будем использовать слово «таблица».
означает таблицу или представление, если не указано иное. Ключи не определены на
представления, но ключевой столбец или столбцы базовой таблицы часто
включены в представление. Для простоты предположим, что это
случай.)

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

ВЫБЕРИТЕ s.SalesOrderID, OrderDate, ProductID
FROM Sales.SalesOrderHeader AS s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderDetail AS d ON s.SalesOrderID = d. SalesOrderID
ORDER BY s.SalesOrderID, ProductID

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

ВНЕШНЕЕ СОЕДИНЕНИЕ используется для соединения двух таблиц, даже если совпадений нет.
ВНЕШНЕЕ СОЕДИНЕНИЕ может использоваться для возврата списка всех клиентов и
заказы, даже если для некоторых клиентов не было размещено ни одного заказа. А
ключевое слово RIGHT или LEFT используется для указания стороны соединения.
возвращает все возможные строки. Мне нравится использовать LEFT, потому что это имеет смысл
сначала перечислить самую важную таблицу. За исключением одного примера
демонстрируя RIGHT OUTER JOIN, в этой статье будут использоваться левые соединения. Только
примечание: ключевые слова INNER и OUTER необязательны.

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

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID

В нем используется ключевое слово LEFT, поскольку таблица Sales. Customer расположена на
слева, и мы хотим, чтобы из этой таблицы возвращались все строки, даже если
в таблице Sales.SalesOrderHeader нет совпадений. Это
важная точка. Обратите также внимание на то, что столбец CustomerID является основным.
ключ таблицы Sales.Customer и внешний ключ в
Таблица Sales.SalesOrderHeader. Это означает, что должен быть действующий
клиента за каждый размещенный заказ. Написание запроса, который возвращает все
заказы и клиенты, если они совпадают, не имеет смысла. Левый
table всегда должна быть таблицей первичного ключа при выполнении LEFT
ВНЕШНЕЕ СОЕДИНЕНИЕ.

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

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
FROM Sales.SalesOrderHeader s
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Sales.Customer c ON c.CustomerID = s.CustomerID

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

Если у меня есть LEFT OUTER JOIN, что возвращается из таблицы на
правая сторона соединения, где нет совпадения? Каждый столбец из
правая сторона вернет NULL. Попробуйте этот запрос, в котором перечислены
сначала несоответствующие строки:

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
ORDER BY s.SalesOrderID

Добавив предложение WHERE для проверки NULL SalesOrderID, вы можете
найти всех клиентов, которые не оформили заказ. Моя копия
AdventureWorks возвращает 66 клиентов без заказов:

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
WHERE s.SalesOrderID IS NULL

Иногда вам нужно быть более конкретным. Как вы можете найти
все клиенты, не оформившие заказ в 2002 году? Есть
несколько способов решения этой проблемы. Вы можете создать представление обо всех
заказов, размещенных в 2002 году, и присоединиться к представлению в таблице Sales. Customer.
Другой вариант — создать CTE или Common Table Expression для
заказы, размещенные в 2002 году. В этом примере показано, как использовать CTE для получения
требуемые результаты:

С s КАК
( ВЫБЕРИТЕ SalesOrderID, customerID
    ИЗ Sales.SalesOrderHeader
    ГДЕ Дата заказа между «1/1/2002» и «31/12/2002»
)
ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
ОТ Продажи.Клиент c
LEFT OUTER JOIN s ON c.customerID = s.customerID
WHERE s.SalesOrderID IS NULL

Мой любимый метод решения этой проблемы намного проще.
Дополнительные критерии, в данном случае фильтрация по OrderDate, могут быть
добавлено в условие соединения. Запрос объединяет всех клиентов в
заказы, размещенные в 2002 году. Затем результаты ограничиваются теми, где
нет совпадения. Этот запрос вернет точно такие же результаты, как
предыдущий, более сложный запрос:

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
                              и s. OrderDate между «01.01.2002» и «31.12.2002».
ГДЕ s.SalesOrderID IS NULL

Использование агрегатов с внешними соединениями

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

SELECT c.CustomerID, count(*) OrderCount
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
СГРУППИРОВАТЬ ПО c.CustomerID
ORDER BY OrderCount

Теперь клиенты без заказов выглядят так, как будто они разместили один заказ. Это потому, что этот запрос
подсчитывает возвращенные строки. Чтобы решить эту проблему, подсчитайте столбец SalesOrderID. НУЛЕВЫЕ значения
исключаются из подсчета.

SELECT c.CustomerID, count(SalesOrderID) OrderCount
FROM Sales.Customer c LEFT OUTER JOIN Sales.SalesOrderHeader s
ON c.CustomerID = s.CustomerID
СГРУППИРОВАТЬ ПО c. CustomerID
ЗАКАЗАТЬ ПО OrderCount

Множественные соединения

Если в запросе задействовано более двух таблиц,
немного сложнее. Когда таблица присоединена к ПРАВОЙ таблице, ЛЕВАЯ
Необходимо использовать ВНЕШНЕЕ СОЕДИНЕНИЕ. Это потому, что строки NULL СПРАВА
table не будет соответствовать ни одной строке в новой таблице.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ приводит к исключению несовпадающих строк из
Результаты. Если таблица Sales.SalesOrderDetail присоединена к
Таблица Sales.SalesOrderHeader и INNER JOIN используются, ни один из
клиенты без заказов появятся. NULL не может быть присоединен ни к одному
значение, даже не NULL.

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

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s. CustomerID
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID
ГДЕ s.SalesOrderID имеет значение NULL

Чтобы получить правильные результаты, измените INNER JOIN на LEFT JOIN.

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID, d.SalesOrderDetailID
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
LEFT OUTER JOIN Sales.SalesOrderDetail d ON s.SalesOrderID = d.SalesOrderID
ГДЕ s.SalesOrderID IS NULL

А как насчет дополнительных таблиц, присоединенных к Sales.Customer, таблицы слева?
Должны ли использоваться внешние соединения? Если возможно, что есть несколько строк без совпадений,
это должно быть внешнее соединение, чтобы гарантировать, что никакие результаты не будут потеряны. Таблица Sales.Customer
имеет внешний ключ, указывающий на таблицу Sales.SalesTerritory. Территория каждого клиента
Идентификатор должен соответствовать допустимому значению в Sales.SalesTerritory. Этот запрос возвращает 66 строк, как и ожидалось.
потому что невозможно исключить каких-либо клиентов, присоединившись к Sales.SalesTerritory:

ВЫБЕРИТЕ c.CustomerID, s.SalesOrderID, t.Name
ОТ Продажи.Клиент c
LEFT OUTER JOIN Sales.SalesOrderHeader s ON c.CustomerID = s.CustomerID
INNER JOIN Sales.SalesTerritory t ON c.TerritoryID = t.TerritoryID
ГДЕ SalesOrderID IS NULL

Sales.SalesTerritory — таблица первичного ключа; каждый клиент должен соответствовать действительной территории.
Если вы хотите написать запрос, в котором перечислены все территории, даже те, где нет клиентов,
будет использоваться внешнее соединение. На этот раз Sales.Customers находится на правой стороне соединения.

ВЫБЕРИТЕ t.Name, CustomerID
ОТ Sales.SalesTerritory t
LEFT OUTER JOIN Sales.Customer c ON t.TerritoryID = c.TerritoryID

Заключение

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