Поиск…


Вступление

В языке структурированных запросов (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'


Modified text is an extract of the original Stack Overflow Documentation
Лицензировано согласно CC BY-SA 3.0
Не связан с Stack Overflow