Microsoft SQL Server
Присоединиться
Поиск…
Вступление
В языке структурированных запросов (SQL) JOIN представляет собой метод связывания двух таблиц данных в одном запросе, позволяя базе данных возвращать набор, содержащий данные из обеих таблиц сразу, или используя данные из одной таблицы, которые будут использоваться как Фильтр по второй таблице. Существует несколько типов JOIN, определенных в стандарте ANSI SQL.
Внутреннее соединение
Inner join возвращает только те записи / строки, которые соответствуют / существуют в обеих таблицах на основе одного или нескольких условий (заданных с помощью ключевого слова ON ). Это наиболее распространенный тип соединения. Общий синтаксис для inner join :
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
Он также может быть упрощен как просто JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
пример
/* Sample data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpets');
/* Sample data prepared. */
SELECT
*
FROM
@Animal
JOIN @AnimalSound
ON @Animal.AnimalId = @AnimalSound.AnimalId;
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpets
Использование внутреннего соединения с левым внешним соединением (Substitute for Not exists)
Этот запрос вернет данные из таблицы 1, где поля, совпадающие с таблицей 2, с ключом и данными, не указанными в таблице 1, сравниваются с таблицей 2 с условием и ключом
select *
from Table1 t1
inner join Table2 t2 on t1.ID_Column = t2.ID_Column
left join Table3 t3 on t1.ID_Column = t3.ID_Column
where t2.column_name = column_value
and t3.ID_Column is null
order by t1.column_name;
Крест
A cross join - это декартово объединение, означающее декартово произведение обеих таблиц. Это соединение не требует каких-либо условий для объединения двух таблиц. Каждая строка в левой таблице будет соединяться с каждой строкой правой таблицы. Синтаксис для кросс-соединения:
SELECT * FROM table_1
CROSS JOIN table_2
Пример:
/* Sample data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
/* Sample data prepared. */
SELECT
*
FROM
@Animal
CROSS JOIN @AnimalSound;
Результаты:
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 1 1 Barks
3 Elephant 1 1 Barks
1 Dog 2 2 Meows
2 Cat 2 2 Meows
3 Elephant 2 2 Meows
1 Dog 3 3 Trumpet
2 Cat 3 3 Trumpet
3 Elephant 3 3 Trumpet
Обратите внимание, что есть другие способы, которыми может применяться CROSS JOIN. Это соединение «старого стиля» (устарело с ANSI SQL-92) без каких-либо условий, что приводит к перекрестному / декартовому соединению:
SELECT *
FROM @Animal, @AnimalSound;
Этот синтаксис также работает из-за условия «всегда истинного» соединения, но не рекомендуется и его следует избегать в пользу явного синтаксиса CROSS JOIN для удобства чтения.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Outer Join
Левая внешняя связь
LEFT JOIN возвращает все строки из левой таблицы, соответствующие строкам из правой таблицы, где выполняются условия предложения ON . Строки, в которых условие ON не выполнено, имеют NULL во всех столбцах правой таблицы. Синтаксис LEFT JOIN :
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Правостороннее соединение
RIGHT JOIN возвращает все строки из правой таблицы, соответствующие строкам из левой таблицы, где выполняются условия предложения ON . Строки, в которых условие ON не выполнено, имеют NULL во всех столбцах левой таблицы. Синтаксис RIGHT JOIN :
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Полная внешняя связь
FULL JOIN объединяет LEFT JOIN и RIGHT JOIN . Все строки возвращаются из обеих таблиц, независимо от того, выполнены ли условия в предложении ON . Строки, которые не удовлетворяют условию ON , возвращаются с NULL во всех столбцах противоположной таблицы (то есть для строки в левой таблице все столбцы в правой таблице будут содержать NULL и наоборот). Синтаксис FULL JOIN :
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Примеры
/* Sample test data. */
DECLARE @Animal table (
AnimalId Int IDENTITY,
Animal Varchar(20)
);
DECLARE @AnimalSound table (
AnimalSoundId Int IDENTITY,
AnimalId Int,
Sound Varchar(20)
);
INSERT INTO @Animal (Animal) VALUES ('Dog');
INSERT INTO @Animal (Animal) VALUES ('Cat');
INSERT INTO @Animal (Animal) VALUES ('Elephant');
INSERT INTO @Animal (Animal) VALUES ('Frog');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (1, 'Barks');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (2, 'Meows');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (3, 'Trumpet');
INSERT INTO @AnimalSound (AnimalId, Sound) VALUES (5, 'Roars');
/* Sample data prepared. */
ЛЕВЫЙ ВНЕШНИЙ ВСТУПИТЕЛЬ
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Результаты для LEFT JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
4 Frog NULL NULL NULL
ПРАВО НА ВСТУПЛЕНИЕ
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Результаты для RIGHT JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
NULL NULL 4 5 Roars
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Результаты для FULL JOIN
AnimalId Animal AnimalSoundId AnimalId Sound
----------- -------------------- ------------- ----------- --------------------
1 Dog 1 1 Barks
2 Cat 2 2 Meows
3 Elephant 3 3 Trumpet
4 Frog NULL NULL NULL
NULL NULL 4 5 Roars
Использование подключения к обновлению
Соединения также могут использоваться в UPDATE :
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
Обновите столбец SomeSetting фильтрации таблицы Preferences предикатом в таблице Users следующим образом:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p является псевдонимом для Preferences , определенного в FROM пункта заявления. Будут обновляться только строки с соответствующим AccountId из таблицы Users .
Обновление с помощью левых внешних операторов соединения
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Обновление таблиц с внутренней функцией объединения и агрегата
UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
from table2
group by field3) as t2
on t2.field3 = t1.field3
Присоединиться к подзапросу
Объединение в подзапрос часто используется, когда вы хотите получить агрегированные данные (например, Count, Avg, Max или Min) из таблицы child / details и отобразить их вместе с записями из таблицы parent / header. Например, вы можете захотеть получить верхнюю / первую дочернюю строку на основе даты или идентификатора, или, возможно, вам нужен граф всех дочерних строк или средний.
В этом примере используются псевдонимы, которые упрощают чтение запросов при использовании нескольких таблиц. В этом случае мы извлекаем все строки из родительской таблицы «Заказы на поставку» и извлекаем только последнюю (или самую последнюю) дочернюю строку из дочерней таблицы PurchaseOrderLineItems. В этом примере предполагается, что дочерняя таблица использует инкрементные числовые идентификаторы.
SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN
(
SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Max(l.id) as Id
FROM PurchaseOrderLineItems l
GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
) AS item ON item.PurchaseOrderId = po.Id
Self Join
Таблицу можно объединить в себя в так называемом самосоединении, совмещая записи в таблице с другими записями в одной таблице. Self-соединения обычно используются в запросах, где определена иерархия в столбцах таблицы.
Рассмотрим образцы данных в таблице « Employees :
| Я БЫ | название | Boss_ID |
|---|---|---|
| 1 | боб | 3 |
| 2 | Джим | 1 |
| 3 | Сэм | 2 |
Boss_ID каждого сотрудника сопоставляется с ID другого сотрудника. Чтобы получить список сотрудников с указанием имени своего босса, таблица может быть объединена сама по себе, используя это сопоставление. Обратите внимание, что присоединение к таблице таким образом требует использования псевдонима ( Bosses в этом случае) во второй ссылке на таблицу, чтобы отличить себя от исходной таблицы.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
Выполнение этого запроса даст следующие результаты:
| название | босс |
|---|---|
| боб | Сэм |
| Джим | боб |
| Сэм | Джим |
Удалить с помощью Join
Соединения также могут использоваться в инструкции DELETE . Учитывая схему следующим образом:
CREATE TABLE Users (
UserId int NOT NULL,
AccountId int NOT NULL,
RealName nvarchar(200) NOT NULL
)
CREATE TABLE Preferences (
UserId int NOT NULL,
SomeSetting bit NOT NULL
)
Мы можем удалять строки из таблицы Preferences , фильтруя предикат в таблице Users следующим образом:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Здесь p является псевдонимом для Preferences определенных в предложении FROM инструкции, и мы удаляем только строки, имеющие соответствующий AccountId из таблицы Users .
Случайное превращение внешнего соединения во внутреннее соединение
Внешние соединения возвращают все строки из одной или обеих таблиц плюс совпадающие строки.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
Левое соединение таблиц:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Возвращает:
PersonID FirstName PersonID Subject Score
1 Alice 1 Math 100
2 Bob 2 Math 54
2 Bob 2 Science 98
3 Eve NULL NULL NULL
Если вы хотите вернуть всех людей, с любыми применимыми математическими оценками, распространенная ошибка заключается в том, чтобы написать:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Это удалит Еву из ваших результатов, в дополнение к удалению научной оценки Боба, поскольку Subject для нее NULL .
Правильный синтаксис для удаления записей без Math при сохранении всех лиц в таблице People :
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'
