Microsoft SQL Server
Ansluta sig
Sök…
Introduktion
I Structured Query Language (SQL) är en JOIN en metod för att länka två datatabeller i en enda fråga, vilket gör det möjligt för databasen att returnera en uppsättning som innehåller data från båda tabellerna samtidigt eller att använda data från en tabell som kan användas som en Filtrera på det andra bordet. Det finns flera typer av JOIN: er definierade i ANSI SQL-standarden.
Inre koppling
Inner join returnerar endast de poster / rader som matchar / finns i båda tabellerna baserat på en eller flera villkor (specificeras med hjälp av ON nyckelord). Det är den vanligaste typen av kopplingar. Den allmänna syntaxen för inner join är:
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
Det kan också förenklas som bara JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
Exempel
/* 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
Använda inre skarv med vänster yttre koppling (Ersättare för existerar inte)
Denna fråga returnerar data från tabell 1 där fält matchar med tabell2 med en nyckel och data som inte finns i tabell 1 när man jämför med tabell2 med ett villkor och nyckel
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 är en kartesisk koppling, vilket betyder en kartesisk produkt från båda tabellerna. Denna sammanfogning behöver inte något villkor för att gå med i två tabeller. Varje rad i det vänstra bordet kommer att gå med i varje rad i det högra bordet. Syntax för ett korsförband:
SELECT * FROM table_1
CROSS JOIN table_2
Exempel:
/* 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;
Resultat:
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
Observera att det finns andra sätt att ett CROSS JOIN kan tillämpas. Detta är en "gammal stil" -koppling (avskrivet sedan ANSI SQL-92) utan villkor, vilket resulterar i en korsning / kartesisk koppling:
SELECT *
FROM @Animal, @AnimalSound;
Denna syntax fungerar också på grund av ett "alltid sant" anslutningsvillkor, men rekommenderas inte och bör undvikas, till förmån för uttrycklig CROSS JOIN syntax, för läsbarhetens skull.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Yuter Join
Vänster yttre del
LEFT JOIN returnerar alla rader från det vänstra bordet, matchade till raderna från det högra bordet där ON klausulvillkoren är uppfyllda. Rader där ON klausulen inte uppfylls har NULL i alla kolumnerna till höger i tabellen. Syntaxen för ett LEFT JOIN är:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Right Yuter Join
RIGHT JOIN returnerar alla rader från det högra bordet, matchade till raderna från det vänstra bordet, där ON klausulen är uppfyllda. Rader där ON klausulen inte uppfylls har NULL i alla kolumnerna i vänstra tabellen. Syntaxen för RIGHT JOIN är:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Full Yuter Join
FULL JOIN kombinerar LEFT JOIN och RIGHT JOIN . Alla rader returneras från båda tabellerna, oavsett om villkoren i ON klausulen är uppfyllda. Rader som inte uppfyller ON klausulen returneras med NULL i alla motsatta bordets kolumner (det vill säga för en rad i den vänstra tabellen kommer alla kolumner i den högra tabellen att innehålla NULL , och vice versa). Syntaxen för ett FULL JOIN är:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
exempel
/* 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. */
Vänster yttre medlem
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultat för 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
RIGHT OUTER JOIN
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultat för 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
FULL YTTERLEDNING
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultat för 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
Använda Gå med i en uppdatering
Joins kan också användas i ett UPDATE uttalande:
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
)
Uppdatera SomeSetting kolumnen i Preferences filtrering med ett predikat i tabellen Users enligt följande:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p är ett alias för Preferences definieras i FROM klausulen i uttalandet. Endast rader med ett matchande AccountId från tabellen Users kommer att uppdateras.
Uppdatera med vänster yttre sammanslutningar
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Uppdatera tabeller med inre sammanfogning och aggregerad funktion
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
Gå med på en undersökning
Att gå med i en subfråga används ofta när du vill hämta sammanlagda data (som Räkna, Genomsnitt, Max eller Min) från en tabell under / information och visa det tillsammans med poster från över- / huvudtabellen. Till exempel kanske du vill hämta raden för den översta / första barnen baserat på datum eller id eller kanske du vill ha en räkning av alla barnrader eller ett genomsnitt.
Detta exempel använder alias som gör frågor lättare att läsa när du har flera tabeller involverade. I det här fallet hämtar vi alla rader från föräldstabellen Inköpsorder och hämtar bara den sista (eller senaste) raden från underordnatabellen InköpOrderLineItems. I det här exemplet antas att barntabellen använder stegvisa numeriska ID: er.
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
Självmedlem
Ett bord kan förenas på sig själv i det som kallas en self-join, och kombinerar poster i tabellen med andra poster i samma tabell. Självförbindelser används vanligtvis i frågor där en hierarki i tabellens kolumner definieras.
Betrakta exempeldata i en tabell som heter Employees :
| ID | namn | Boss_ID |
|---|---|---|
| 1 | Guppa | 3 |
| 2 | Jim | 1 |
| 3 | Sam | 2 |
Varje anställds Boss_ID till en annan anställds ID . För att hämta en lista över anställda med deras respektive chefens namn kan tabellen sammanfogas på sig själv med hjälp av denna kartläggning. Observera att det att använda en tabell på detta sätt kräver användning av ett alias ( Bosses i detta fall) i den andra referensen till tabellen för att skilja sig från den ursprungliga tabellen.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
Genom att utföra denna fråga kommer följande resultat att visas:
| namn | Chef |
|---|---|
| Guppa | Sam |
| Jim | Guppa |
| Sam | Jim |
Radera med hjälp av Gå
Joins kan också användas i ett DELETE uttalande. Får ett schema enligt följande:
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
)
Vi kan radera rader från tabellen Preferences och filtrera efter ett predikat i tabellen Users enligt följande:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Här är p ett alias för Preferences definieras i FROM klausulen i uttalandet och vi raderar bara rader som har ett matchande AccountId från tabellen Users .
Oavsiktligt förvandla ett yttre fog till ett inre fog
Ytterkopplingar returnerar alla rader från ett eller båda tabellerna, plus matchande rader.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
Vänster ansluter sig till borden:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Returns:
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
Om du ville returnera alla människor, med alla tillämpliga matematiska poäng, är ett vanligt misstag att skriva:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Detta skulle ta bort Eve från dina resultat, förutom att ta bort Bobs vetenskapliga poäng, eftersom Subject är NULL för henne.
Rätt syntax för att ta bort poster som inte är matematiska samtidigt som alla personer i tabellen People skulle behållas skulle vara:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'