Microsoft SQL Server
Przystąp
Szukaj…
Wprowadzenie
W Structured Query Language (SQL) JOIN to metoda łączenia dwóch tabel danych w jednym zapytaniu, umożliwiająca bazie danych zwrócenie zestawu zawierającego dane z obu tabel jednocześnie lub użycie danych z jednej tabeli do użycia jako Filtruj na drugim stole. Istnieje kilka rodzajów JOIN zdefiniowanych w standardzie ANSI SQL.
Przyłączenie wewnętrzne
Inner join zwraca tylko te rekordy / wiersze, które pasują / istnieją w obu tabelach na podstawie jednego lub więcej warunków (określonych za pomocą słowa kluczowego ON ). Jest to najczęstszy rodzaj łączenia. Ogólna składnia inner join jest następująca:
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
Można to również uprościć, JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
Przykład
/* 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
Używanie złączenia wewnętrznego z lewym łączeniem zewnętrznym (Zastępowanie nie istnieje)
To zapytanie zwróci dane z tabeli 1, gdzie pola pasujące do tabeli 2 z kluczem i dane nie w tabeli 1, w porównaniu z tabelą 2 z warunkiem i kluczem
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;
Cross Join
A cross join jest A cross join kartezjańskim, co oznacza iloczyn kartezjański obu tabel. To połączenie nie wymaga żadnego warunku, aby połączyć dwie tabele. Każdy wiersz w lewej tabeli zostanie dołączony do każdego wiersza w prawej tabeli. Składnia dla połączenia krzyżowego:
SELECT * FROM table_1
CROSS JOIN table_2
Przykład:
/* 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;
Wyniki:
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
Należy pamiętać, że istnieją inne sposoby zastosowania połączenia krzyżowego. Jest to sprzężenie w „starym stylu” (przestarzałe od ANSI SQL-92) bez warunków, co powoduje sprzężenie krzyżowe / kartezjańskie:
SELECT *
FROM @Animal, @AnimalSound;
Ta składnia działa również z powodu warunku „zawsze prawdziwego” łączenia, ale nie jest zalecana i należy jej unikać, na rzecz jawnej składni CROSS JOIN , ze względu na czytelność.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Outer Join
Lewy zewnętrzny łącznik
LEFT JOIN zwraca wszystkie wiersze z lewej tabeli, dopasowane do wierszy z prawej tabeli, w których spełnione są warunki klauzuli ON . Wiersze, w których klauzula ON nie jest spełniona, mają NULL we wszystkich kolumnach prawej tabeli. Składnia LEFT JOIN to:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Right Outer Join
RIGHT JOIN zwraca wszystkie wiersze z prawej tabeli, dopasowane do wierszy z lewej tabeli, w których spełnione są warunki klauzuli ON . Wiersze, w których klauzula ON nie jest spełniona, mają NULL we wszystkich kolumnach lewej tabeli. Składnia RIGHT JOIN jest następująca:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Pełne połączenie zewnętrzne
FULL JOIN łączy LEFT JOIN i RIGHT JOIN . Wszystkie wiersze są zwracane z obu tabel, niezależnie od tego, czy warunki w klauzuli ON są spełnione. Wiersze, które nie spełniają klauzuli ON , są zwracane z NULL we wszystkich przeciwnych kolumnach tabeli (to znaczy, dla wiersza w lewej tabeli wszystkie kolumny w prawej tabeli będą zawierać NULL i odwrotnie). Składnia FULL JOIN jest następująca:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Przykłady
/* 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. */
DOŁĄCZ DO LEWEGO ZEWNĘTRZNEGO
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Wyniki dla 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
DOŁĄCZ DO PRAWEJ ZEWNĘTRZNEJ
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Wyniki dla 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
DOŁĄCZ DO ZEWNĘTRZNEGO
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Wyniki dla 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
Korzystanie z dołączenia w aktualizacji
Połączenia mogą być również używane w instrukcji 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
)
Zaktualizuj kolumnę SomeSetting filtrowania tabeli Preferences według predykatu w tabeli Users w następujący sposób:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p jest aliasem dla Preferences zdefiniowanych w klauzuli FROM instrukcji. AccountId będą tylko wiersze z pasującym AccountId z tabeli Users .
Zaktualizuj z lewymi zewnętrznymi instrukcjami łączenia
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Aktualizuj tabele z wewnętrznym połączeniem i funkcją agregującą
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
Dołącz do podzapytania
Dołączanie do podzapytania jest często używane, gdy chcesz uzyskać dane zagregowane (takie jak Liczba, Śr., Maks. Lub Min.) Z tabeli podrzędnej / szczegółów i wyświetlić ją wraz z rekordami z tabeli nadrzędnej / nagłówka. Na przykład możesz chcieć pobrać górny / pierwszy wiersz podrzędny na podstawie daty lub identyfikatora, a może chcesz liczbę wszystkich wierszy podrzędnych lub średnią.
W tym przykładzie użyto aliasów, które ułatwiają odczytywanie zapytań, gdy w grę wchodzi wiele tabel. W tym przypadku pobieramy wszystkie wiersze z tabeli nadrzędnej Zamówienia zakupu i pobieramy tylko ostatni (lub najnowszy) wiersz podrzędny z tabeli podrzędnej PurchaseOrderLineItems. W tym przykładzie założono, że tabela potomna używa przyrostowych identyfikatorów numerycznych.
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
Tabelę można łączyć ze sobą w tak zwane samo łączenie, łącząc rekordy w tabeli z innymi rekordami w tej samej tabeli. Połączenia automatyczne są zwykle używane w zapytaniach, w których zdefiniowana jest hierarchia w kolumnach tabeli.
Rozważ przykładowe dane w tabeli o nazwie Employees :
| ID | Nazwa | Boss_ID |
|---|---|---|
| 1 | Kok | 3) |
| 2) | Jim | 1 |
| 3) | Sam | 2) |
Boss_ID każdego pracownika odwzorowuje na ID innego pracownika. Aby pobrać listę pracowników z nazwiskiem ich szefa, tabelę można połączyć na sobie za pomocą tego mapowania. Zauważ, że dołączenie do tabeli w ten sposób wymaga użycia aliasu (w tym przypadku Bosses ) w drugim odwołaniu do tabeli, aby odróżnić się od oryginalnej tabeli.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
Wykonanie tego zapytania spowoduje wyświetlenie następujących wyników:
| Nazwa | Szef |
|---|---|
| Kok | Sam |
| Jim | Kok |
| Sam | Jim |
Usuń za pomocą Dołącz
Złączeń można także używać w instrukcji DELETE . Biorąc pod uwagę następujący schemat:
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
)
Możemy usunąć wiersze z tabeli Preferences , filtrując według predykatu z tabeli Users w następujący sposób:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Tutaj p jest aliasem dla Preferences zdefiniowanych w klauzuli FROM instrukcji i usuwamy tylko te wiersze, które mają pasujące AccountId z tabeli Users .
Przypadkowe przekształcenie złącza zewnętrznego w złącze wewnętrzne
Sprzężenia zewnętrzne zwracają wszystkie wiersze z jednej lub obu tabel oraz pasujące wiersze.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
Lewe dołączanie do tabel:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Zwroty:
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
Jeśli chciałbyś zwrócić wszystkie osoby, z odpowiednimi wynikami matematycznymi, częstym błędem jest napisanie:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Spowoduje to usunięcie Ewy z twoich wyników, oprócz usunięcia wyniku naukowego Boba, ponieważ Subject jest dla niej NULL .
Prawidłowa składnia do usuwania rekordów innych niż matematyczne przy jednoczesnym zachowaniu wszystkich osób w tabeli People to:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'