В этой истории мы начнем с нуля, чтобы понять, как мы можем объединять две или более таблиц в SQL Server. Мы будем использовать SQL Server 2019, но почти каждый запрос, описанный ниже, может быть выполнен в более старых версиях SQL Server или даже в других базах данных SQL, таких как Oracle или MySQL, с очень небольшими настройками.

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

Настройка среды

Мы будем использовать следующие таблицы и примеры данных для всех запросов. Представьте, что вы просматриваете (очень упрощенную версию) хранилище данных службы проката электронных скутеров, такой как Bird, Lime, Dott.

У нас есть следующие таблицы:

  • Таблица [dbo].[Trip]: таблица транзакций со всеми поездками. Он имеет одну строку на поездку или, другими словами, одна строка генерируется каждый раз, когда пользователь разблокирует самокат, использует его для поездок на работу и снова блокирует.

  • Таблица [dbo].[User]: таблица измерения пользователей.

  • Таблица [dbo].[Scooter]: таблица размеров самокатов.

  • Таблица [dbo].[RepairedScooter]: это таблица, которая содержит список скутеров, прошедших хотя бы один раз ремонтную палату.

Вы можете просмотреть код для создания и заполнения данных из db‹›fiddle, а также просмотреть предварительный просмотр.

Поиск комбинации разных столбцов

Мы можем использовать CROSS JOIN (или декартово соединение) в разных ситуациях, но, вероятно, его наиболее часто используемая область применения — это когда вы хотите ВЫБРАТЬ все комбинации двух или более полей из одной или нескольких таблиц.

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

— Запрос A001
SELECT DISTINCT A.[ID],A.[Имя пользователя],B.[ID],B.[Бренд],B.[Модель]
FROM [Пользователь] A,[Scooter] B
ORDER BY A.[ID],B.[ID]

Работа со столбцами разных таблиц

JOIN используется, когда вы хотите добавить в таблицу (таблица A) один или несколько столбцов из другой таблицы (таблица B). Это, вероятно, самая важная конструкция для освоения, если вы хотите работать с базой данных на основе SQL.

Введение в синтаксис

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

Следующий запрос представляет собой базовое JOIN из таблицы [Trip] (с псевдонимом «A») и таблицы [Scooter] (с псевдонимом «B»). Тип соединения — INNER JOIN, мы поймем, что это значит, позже в этом разделе статьи. Условия соединения перечислены после предложения ON. Здесь есть одно простое равенство между столбцами, но их может быть больше одного (в этом случае вы должны использовать операторы И, ИЛИ, НЕ) или что-то более сложное, например, МЕЖДУ или НРАВИТСЯ. В условиях JOIN вы также можете найти функции, применяемые к столбцам (например, LEFT), но это введение, поэтому мы не найдем ни одного из этих случаев.

— Запрос B001
ВЫБЕРИТЕ A.[ID],A.[DateTimeStart],A.[DurationDuration],B.[Бренд],B.[Модель]
FROM [ Поездка] A
INNER JOIN [Scooter] B
ON A.[ID_Scooter]=B.[ID]

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

— Запрос B003
SELECT [Поездка].[ID],[Поездка].[ДатаВремяНачала],[Поездка].[Продолжительность поездки],[Скутер].[Марка],[Скутер] .[Модель]
FROM [Поездка]
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [Скутер]
ON [Поездка].[ID_Scooter]=[Скутер].[ID]

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

— Запрос B002
ВЫБЕРИТЕ A. [ID], [DateTimeStart], [TripDuration], [Brand], [Model]
FROM [Trip] A
INNER ПРИСОЕДИНЯЙТЕСЬ к [Scooter] B
ON [ID_Scooter]=B.[ID]

Конечно, вы можете расширить все эти правила с помощью JOIN трех или более таблиц, в этом случае ограничений нет.

— Запрос B004
ВЫБЕРИТЕ A.[ID],A.[DateTimeStart],A.[DurationDuration],B.[Бренд],B.[Модель],C.*
FROM [Trip] A
INNER JOIN [Scooter] B
ON A.[ID_Scooter]=B.[ID]
INNER JOIN [User] C
ON A. [ID_User]=C.[ID]

Наконец, вы можете найти некоторые предложения JOIN с другим синтаксисом (например, если вы используете пакет SAP Business Objects), похожим на предложение CROSS JOIN. Это также допустимый синтаксис, известный как ANSI-92, но в этой статье мы его не используем, поэтому не стесняйтесь находить множество статей о нем в Интернете.

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

Результаты FULL OUTER JOIN будут включать по крайней мере одну строку из таблицы A и по крайней мере одну строку из таблицы B, даже если условия JOIN не выполняются, в этом случае строка со значениями NULL будет получена из строки, которая не соответствует. т матч.

См. следующий пример, чтобы прояснить эту концепцию.

— Запрос C001
SELECT *
FROM [Trip] A
FULL OUTER JOIN [User] B
ON A.[ID_User]=B.[ Я БЫ]

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

LEFT OUTER JOIN похож на FULL OUTER JOIN, но асимметричен. Это означает, что в результатах будет хотя бы одна строка из таблицы слева (таблица A в следующем примере), но строки таблицы справа (таблица B в примере), которые не удовлетворяют правилам JOIN, не будет показано.

— Запрос D001
SELECT *
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID ]

Обратите внимание, что мы можем использовать конструкцию LEFT OUTER JOIN, чтобы найти строки таблицы слева (таблица A), которые не имеют строки соответствия в таблице справа (таблица B) на основе правил JOIN. Это очень полезное приложение, которое можно использовать, просто добавив предложение WHERE в запрос, как в следующем примере.

— Запрос D002
SELECT A.*
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B. [ID]
ГДЕ B.[ID] IS NULL

ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Как вы, вероятно, ожидаете, все соображения, которые мы сделали для LEFT OUTER JOIN, могут быть симметрично использованы в RIGHT OUTER JOIN.

Не стесняйтесь взглянуть на следующие два примера, чтобы подтвердить или опровергнуть ваши ожидания относительно RIGHT OUTER JOIN после понимания LEFT OUTER JOIN в разделе выше.

— Запрос E001
SELECT *
FROM [Trip] A
RIGHT JOIN [User] B
ON A.[ID_User]=B.[ID ]

— Запрос E002
SELECT B.*
FROM [Trip] A
RIGHT JOIN [User] B
ON A.[ID_User]=B. [ID]
ГДЕ A.[ID] IS NULL

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

Концептуально INNER JOIN представляет собой комбинацию LEFT и RIGHT OUTER JOIN, поэтому в результатах этого JOIN будут только строки, соответствующие правилам JOIN.

— Запрос F001
SELECT *
FROM [Trip] A
INNER JOIN [User] B
ON A.[ID_User]=B.[ID ]

АНТИ ПРИСОЕДИНЯЙСЯ

И наоборот, ANTI JOIN покажет только строки таблицы слева (таблица A) и строки таблицы справа (таблица B), которые не соответствуют правилам JOIN.

— Запрос G001
SELECT *
FROM [Trip] A
FULL OUTER JOIN [User] B
ON A.[ID_User]=B.[ ID]
ГДЕ A.[ID] IS NULL ИЛИ B.[ID] IS NULL

ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ

CROSS APPLY похож на INNER JOIN, но он используется, когда вы хотите указать более сложные правила о количестве или порядке в JOIN. Наиболее распространенное практическое использование CROSS APPLY, вероятно, когда вы хотите сделать JOIN между двумя (или более) таблицами, но вы хотите, чтобы каждая строка таблицы A вычисляла одну и только одну строку таблицы B. В следующем примере в более подробно, каждый пользователь (таблица A) будет соответствовать своей самой продолжительной поездке (таблица B). Обратите внимание, что пользователи, которые не совершили ни одной поездки, не будут включены в результаты, так как мы уже говорили, что CROSS APPLY в чем-то похож на INNER JOIN.

— Запрос H001
ВЫБЕРИТЕ A.*,B.[TripDuration],B.[ID]
FROM [User] A
ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ (
SELECT TOP 1 *
FROM [Trip] C
WHERE C.[ID_User]=A.[ID]
ORDER BY C.[TripDuration] DESC
) B

НАРУЖНОЕ ПРИМЕНИТЬ

OUTER APPLY — это конструкция, очень близкая к CROSS APPLY, которую мы только что обсуждали. Основное отличие состоит в том, что OUTER APPLY будет включать в результаты также строки из таблицы A, которые не соответствуют правилам соответствия, определенным в предложении WHERE.

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

— Запрос I001
SELECT A.*,B.[TripDuration],B.[ID]
FROM [User] A
OUTER APPLY (
SELECT TOP 1 *
FROM [Trip] C
WHERE C.[ID_User]=A.[ID]
ORDER BY C.[TripDuration] DESC
) B

Использование условий WHERE в JOINS

Прежде чем двигаться дальше, важно понять использование предложения WHERE при использовании JOIN.

Мы рассмотрим четыре примера, с четырьмя совершенно разными результатами, которые, надеюсь, помогут понять, как правильно писать правила JOIN и WHERE.

В запросе L001 можно увидеть важный факт о СОЕДИНЕНИИ, так как база данных, в первую очередь, сделает СОЕДИНЕНИЕ между таблицами, основываясь на правилах СОЕДИНЕНИЯ, и только после этого будет фильтровать с помощью предложения WHERE. Таким образом, результаты будут включать только строки, ГДЕ B.[Имя пользователя]='Бренда'.

— Запрос L001
SELECT *
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID ]
ГДЕ B.[Имя пользователя]='Бренда'

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

Другая сторона медали заключается в том, что вы должны знать и всегда обращать внимание на то, что в LEFT OUT JOIN будут учитываться только правила фильтрации, примененные к правой таблице (таблица B).

— Запрос L002
SELECT *
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID ] И B.[Имя пользователя]='Бренда'

— Запрос L003
SELECT *
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID ]
ГДЕ A.[ID_Scooter]='SR001'

Как мы уже говорили в примере с запросом L002, в LEFT OUT JOIN будут учитываться только правила фильтрации, примененные к правой таблице (таблица B). В этом примере видно, что правило фильтрации A.[ID_Scooter]=’SR001’ игнорируется, и в результатах извлекаются все самокаты. Пожалуйста, выделите немного времени, чтобы понять разницу между результатами запросов L003 и L004.

— Запрос L004
SELECT *
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID ] И А.[ID_Scooter]='SR001'

Работа со строками разных таблиц

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

СОЮЗ и СОЮЗ ВСЕХ

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

— Запрос M001
SELECT *
FROM [Scooter]
UNION ALL
SELECT *
FROM [RepairedScooter]

Сообщение 205 Уровень 16 Состояние 1 Строка 2 Все запросы, объединенные с использованием оператора UNION, INTERSECT или EXCEPT, должны иметь одинаковое количество выражений в своих целевых списках.

Без изменения структуры таблиц вы можете легко объединить две таблицы, выбрав только те столбцы, которые существуют в обеих из них.

— Запрос M002
SELECT [ID], [Бренд], [Модель], [Год выпуска]
FROM [Scooter]
UNION ALL
SELECT [ ID],[Бренд],[Модель],[Год выпуска]
ИЗ [RepairedScooter]

Разница между UNION и UNION ALL заключается в том, что первый включает в результаты только значения DISTINCT, и по этой причине он немного медленнее, чем другой.

— Запрос M003
ВЫБЕРИТЕ [ID], [Бренд], [Модель], [Год выпуска]
FROM [Scooter]
UNION
ВЫБЕРИТЕ [ID ],[Бренд],[Модель],[Год выпуска]
ОТ [RepairedScooter]

КРОМЕ

Мы можем использовать EXCEPT, если хотим извлечь строки из первой таблицы, но не из второй таблицы.

— Запрос N001
ВЫБЕРИТЕ [ID], [Бренд], [Модель], [Год выпуска]
ИЗ [Скутер]
ЗА ИСКЛЮЧЕНИЕМ
ВЫБЕРИТЕ [ID ],[Бренд],[Модель],[Год выпуска]
ОТ [RepairedScooter]

ПЕРЕСЕЧАТЬ

Мы можем использовать INTERSECT, если хотим извлечь строки как из первой, так и из второй таблицы.

— Запрос O001
ВЫБЕРИТЕ [ID], [Бренд], [Модель], [Год выпуска]
FROM [Scooter]
INTERSECT
ВЫБЕРИТЕ [ID ],[Бренд],[Модель],[Год выпуска]
ОТ [RepairedScooter]

Небольшой обзор с использованием блок-схемы

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



SQL Server 2019 | db fiddle
Бесплатная онлайн-среда SQL для экспериментов и совместного использования.dbfiddle.uk