Microsoft SQL Server
toetreden
Zoeken…
Invoering
In Structured Query Language (SQL) is een JOIN een methode om twee gegevenstabellen in één query te koppelen, waardoor de database een set kan retourneren die gegevens uit beide tabellen tegelijk bevat of gegevens uit één tabel gebruikt om te gebruiken als een Filter op de tweede tafel. Er zijn verschillende typen JOINs gedefinieerd binnen de ANSI SQL-standaard.
Innerlijke join
Inner join retourneert alleen die records / rijen die overeenkomen / bestaan in beide tabellen op basis van een of meer voorwaarden (opgegeven met het trefwoord ON ). Het is het meest voorkomende type join. De algemene syntaxis voor inner join is:
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
Het kan ook worden vereenvoudigd als alleen JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
Voorbeeld
/* 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
Inner join gebruiken met linker outer join (vervanging voor niet bestaat)
Deze query retourneert gegevens uit tabel 1 waarbij velden die overeenkomen met tabel 2 met een sleutel en gegevens die niet in tabel 1 staan vergeleken met tabel 2 met een voorwaarde en sleutel
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 is een Cartesiaanse verbinding, wat betekent een Cartesiaans product van beide tabellen. Deze join heeft geen voorwaarde nodig om twee tabellen samen te voegen. Elke rij in de linkertabel wordt verbonden met elke rij van de rechtertabel. Syntaxis voor een cross join:
SELECT * FROM table_1
CROSS JOIN table_2
Voorbeeld:
/* 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;
resultaten:
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
Merk op dat er andere manieren zijn waarop een CROSS JOIN kan worden toegepast. Dit is een "oude stijl" join (verouderd sinds ANSI SQL-92) zonder voorwaarde, wat resulteert in een kruis / Cartesiaanse join:
SELECT *
FROM @Animal, @AnimalSound;
Deze syntaxis werkt ook vanwege een "altijd echte" join-voorwaarde, maar wordt niet aanbevolen en moet worden vermeden, ten gunste van expliciete CROSS JOIN syntaxis, omwille van de leesbaarheid.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Buitenste join
Linker buitenaansluiting
LEFT JOIN retourneert alle rijen uit de linker tafel, afgestemd op de rijen van de juiste tafel waar de ON clausule voorwaarden wordt voldaan. Rijen waarin niet aan de clausule ON wordt voldaan, hebben NULL in alle kolommen van de juiste tabel. De syntaxis van een LEFT JOIN is:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Right Outer Join
RIGHT JOIN geeft alle rijen uit de rechter tabel terug, gekoppeld aan rijen uit de linker tabel waar aan de voorwaarden van de ON clausule is voldaan. Rijen waarin niet aan de clausule ON wordt voldaan, hebben NULL in alle kolommen van de linkertabel. De syntaxis van een RIGHT JOIN is:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Volledige deelname aan de buitenwereld
FULL JOIN combineert LEFT JOIN en RIGHT JOIN . Alle rijen worden uit beide tabellen geretourneerd, ongeacht of aan de voorwaarden in de clausule ON is voldaan. Rijen die niet voldoen aan de ON clausule worden met NULL geretourneerd in alle kolommen van de tegenovergestelde tabel (dat wil zeggen dat voor een rij in de linkertabel alle kolommen in de rechtertabel NULL bevatten en vice versa). De syntaxis van een FULL JOIN is:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Voorbeelden
/* 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. */
LINKER BUITENKANTEL
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultaten voor 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
JUISTE BUITENKANT
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultaten voor 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
VOLLEDIGE BUITENKANTE
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Resultaten voor 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
Join gebruiken in een update
Joins kunnen ook worden gebruikt in een UPDATE instructie:
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
)
Werk de kolom SomeSetting van de filtering van de tabel Preferences als volgt bij door een predikaat in de tabel Users :
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p is een alias voor Preferences gedefinieerd in de clausule FROM van de instructie. Alleen rijen met een overeenkomend AccountId uit de tabel Users worden bijgewerkt.
Update met links buiten join-instructies
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Werk tabellen bij met inner join en aggregatiefunctie
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
Doe mee aan een subquery
Deelnemen aan een subquery wordt vaak gebruikt wanneer u verzamelde gegevens (zoals Count, Avg, Max of Min) wilt ophalen uit een onderliggende / detailtabel en deze samen met records uit de bovenliggende / koptabel wilt weergeven. U wilt bijvoorbeeld de bovenste / eerste onderliggende rij ophalen op basis van Datum of ID of misschien wilt u een telling van alle onderliggende rijen of een gemiddelde.
In dit voorbeeld worden aliassen gebruikt, waardoor zoekopdrachten gemakkelijker te lezen zijn als er meerdere tabellen bij betrokken zijn. In dit geval halen we alle rijen op uit de bovenliggende tabel Inkooporders en halen alleen de laatste (of meest recente) onderliggende rij uit de onderliggende tabel PurchaseOrderLineItems. In dit voorbeeld wordt ervan uitgegaan dat de onderliggende tabel incrementele numerieke id's gebruikt.
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
Zelf meedoen
Een tabel kan aan zichzelf worden gekoppeld in een zogenaamde self-join, waarbij records in de tabel worden gecombineerd met andere records in dezelfde tabel. Self joins worden meestal gebruikt in query's waarbij een hiërarchie in de kolommen van de tabel is gedefinieerd.
Bekijk de voorbeeldgegevens in een tabel met de naam Employees :
| ID kaart | Naam | Boss_ID |
|---|---|---|
| 1 | Bob | 3 |
| 2 | Jim | 1 |
| 3 | Sam | 2 |
De Boss_ID elke medewerker wordt Boss_ID aan de ID van een andere medewerker. Om een lijst met werknemers met de naam van hun respectieve baas op te halen, kan de tabel op deze manier worden samengevoegd. Merk op dat het op deze manier deelnemen aan een tabel het gebruik van een alias ( Bosses in dit geval) op de tweede verwijzing naar de tabel vereist om zichzelf te onderscheiden van de oorspronkelijke tabel.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
Het uitvoeren van deze zoekopdracht levert de volgende resultaten op:
| Naam | Baas |
|---|---|
| Bob | Sam |
| Jim | Bob |
| Sam | Jim |
Verwijderen met Join
Joins kunnen ook worden gebruikt in een DELETE instructie. Gegeven een schema als volgt:
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
)
We kunnen rijen verwijderen uit de tabel Preferences , filteren op een predikaat op de tabel Users als volgt:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Hier is p een alias voor Preferences gedefinieerd in de clausule FROM van de instructie en we verwijderen alleen rijen met een overeenkomende AccountId uit de tabel Users .
Per ongeluk veranderen van een buitenste join in een binnenste join
Buitenste joins retourneren alle rijen uit een of beide tabellen, plus overeenkomende rijen.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
Links aan de tafels gevoegd:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Geeft terug:
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
Als je alle mensen wilt terugbrengen, met alle toepasselijke wiskundige scores, is een veelgemaakte fout om te schrijven:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Dit zou Eva uit je resultaten verwijderen, naast het verwijderen van de wetenschappelijke score van Bob, omdat Subject NULL voor haar.
De juiste syntaxis om niet-wiskundige records te verwijderen met behoud van alle personen in de tabel People zou zijn:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'
