Microsoft SQL Server
Beitreten

Einführung

Inner Join

Inner join gibt nur die Datensätze / Zeilen zurück, die basierend auf einer oder mehreren Bedingungen (angegeben mit dem Schlüsselwort ON ) in beiden Tabellen übereinstimmen / existieren. Dies ist die häufigste Art der Verknüpfung. Die allgemeine Syntax für inner join lautet:

SELECT * 
FROM table_1
INNER JOIN table_2
  ON table_1.column_name = table_2.column_name

Es kann auch als JOIN vereinfacht werden:

SELECT * 
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name

Beispiel

/* 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 mit linkem Outer Join verwenden (Ersatz für Nicht vorhanden)

Diese Abfrage gibt Daten aus Tabelle 1 zurück, wobei Felder, die mit Tabelle2 mit einem Schlüssel übereinstimmen, und Daten, die nicht in Tabelle 1 enthalten sind, wenn mit Tabelle2 mit einer Bedingung und einem Schlüssel verglichen werden

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 ist eine kartesische Verknüpfung, dh ein kartesisches Produkt aus beiden Tabellen. Dieser Join benötigt keine Bedingung, um zwei Tabellen zu verbinden. Jede Zeile in der linken Tabelle wird mit jeder Zeile der rechten Tabelle verbunden. Syntax für einen Cross Join:

SELECT * FROM table_1
CROSS JOIN table_2 

Beispiel:

/* 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;

Ergebnisse:

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

Beachten Sie, dass ein CROSS JOIN auf andere Arten angewendet werden kann. Dies ist ein "alter Stil" -Join (veraltet seit ANSI SQL-92) ohne Bedingung, was zu einem Kreuz / kartesischen Join führt:

SELECT * 
FROM @Animal, @AnimalSound;

Diese Syntax funktioniert auch aufgrund einer Join-Bedingung "Always True", wird jedoch nicht empfohlen und sollte aus Gründen der Lesbarkeit zugunsten der expliziten CROSS JOIN Syntax vermieden werden.

SELECT * 
FROM 
    @Animal 
    JOIN @AnimalSound 
        ON 1=1

Äußere Join

Linke äußere Verbindung

LEFT JOIN gibt alle Zeilen der linken Tabelle zurück, die mit den Zeilen der rechten Tabelle übereinstimmen, in denen die Bedingungen der ON Klausel erfüllt sind. Zeilen, in denen die ON Klausel nicht erfüllt ist, weisen in allen Spalten der rechten Tabelle NULL auf. Die Syntax eines LEFT JOIN lautet:

SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

Rechter äußerer Join

RIGHT JOIN gibt alle Zeilen der rechten Tabelle zurück, die mit den Zeilen der linken Tabelle übereinstimmen, in denen die Bedingungen der ON Klausel erfüllt sind. Zeilen, in denen die ON Klausel nicht erfüllt ist, weisen in allen Spalten der linken Tabelle NULL auf. Die Syntax eines RIGHT JOIN lautet:

SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column 

Voller äußerer Join

FULL JOIN kombiniert LEFT JOIN und RIGHT JOIN . Alle Zeilen werden aus beiden Tabellen zurückgegeben, unabhängig davon, ob die Bedingungen in der ON Klausel erfüllt sind. Zeilen, die die ON Klausel nicht erfüllen, werden in allen Spalten der gegenüberliegenden Tabelle mit NULL zurückgegeben ( NULL für eine Zeile in der linken Tabelle enthalten alle Spalten in der rechten Tabelle NULL und umgekehrt). Die Syntax eines FULL JOIN lautet:

SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column  

Beispiele

/* 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. */

LINKE ÄUSSERE VERBINDUNG

SELECT * 
FROM @Animal As t1 
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Ergebnisse 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

RECHTE AUSSEN VERBINDEN

SELECT * 
FROM @Animal As t1 
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Ergebnisse 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

VOLL AUSSEN MITGLIED

SELECT * 
FROM @Animal As t1 
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;

Ergebnisse 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

Mitmachen bei einem Update

Joins können auch in einer UPDATE Anweisung verwendet werden:

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
)

Aktualisieren Sie die SomeSetting Spalte der Tabelle " Preferences mit einem Prädikat in der Tabelle Users , wie folgt:

UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

p ist ein Alias ​​für Preferences die in der FROM Klausel der Anweisung definiert sind. Nur Zeilen mit einer übereinstimmenden AccountId aus der Tabelle Users werden aktualisiert.

Aktualisieren Sie mit linken äußeren Join-Anweisungen

Update t 
SET  t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2 
ON t2.ID=t.ID

Tabellen mit Inner Join- und Aggregatfunktion aktualisieren

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  

Treten Sie einer Unterabfrage bei

Das Verknüpfen bei einer Unterabfrage wird häufig verwendet, wenn Sie aggregierte Daten (wie z. B. Count, Avg, Max oder Min) aus einer untergeordneten Tabelle / Details abrufen und diese zusammen mit Datensätzen aus der übergeordneten / Header-Tabelle anzeigen möchten. Beispielsweise möchten Sie möglicherweise die oberste / erste untergeordnete Zeile basierend auf Datum oder ID abrufen, oder Sie möchten vielleicht eine Zählung aller untergeordneten Zeilen oder einen Durchschnitt.

In diesem Beispiel werden Aliase verwendet, sodass Abfragen leichter lesbar sind, wenn mehrere Tabellen beteiligt sind. In diesem Fall rufen wir alle Zeilen aus der übergeordneten Tabelle "Bestellungen" und nur die letzte (oder letzte) untergeordnete Zeile aus der untergeordneten Tabelle "PurchaseOrderLineItems" ab. In diesem Beispiel wird davon ausgegangen, dass die untergeordnete Tabelle inkrementelle numerische IDs verwendet.

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

Selbst beitreten

Eine Tabelle kann in einem sogenannten Self-Join mit sich selbst verbunden werden, wobei Datensätze in der Tabelle mit anderen Datensätzen in derselben Tabelle kombiniert werden. Self-Joins werden normalerweise in Abfragen verwendet, in denen eine Hierarchie in den Tabellenspalten definiert ist.

Betrachten Sie die Beispieldaten in einer Tabelle mit dem Namen Employees :

Jeder Mitarbeiter Boss_ID Karten auf einem anderen Mitarbeiter - ID . Um eine Liste von Mitarbeitern mit dem Namen ihres jeweiligen Chefs abzurufen, kann die Tabelle mithilfe dieser Zuordnung in sich zusammengefügt werden. Beachten Sie, dass das Verknüpfen einer Tabelle auf diese Weise die Verwendung eines Alias ​​( Bosses ) in der zweiten Referenz der Tabelle erfordert, um sich von der Originaltabelle zu unterscheiden.

SELECT Employees.Name,
    Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses 
    ON Employees.Boss_ID = Bosses.ID

Beim Ausführen dieser Abfrage werden folgende Ergebnisse ausgegeben:

Mit Join löschen

Joins können auch in einer DELETE Anweisung verwendet werden. Ein Schema wie folgt gegeben:

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
)

Wir können Zeilen aus der Preferences Tabelle löschen und nach einem Prädikat in der Users Tabelle wie folgt filtern:

DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234

Hier ist p ein Alias ​​für Preferences die in der FROM Klausel der Anweisung definiert sind. Wir löschen nur Zeilen mit einer übereinstimmenden AccountId aus der Tabelle Users .

Versehentliches Verwandeln einer äußeren Verbindung in eine innere Verbindung

Äußere Joins geben alle Zeilen aus einer oder beiden Tabellen plus übereinstimmende Zeilen zurück.

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 bei den Tischen:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID

Kehrt zurück:

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

Wenn Sie alle Personen mit den zutreffenden mathematischen Ergebnissen zurückgeben möchten, lautet ein häufiger Fehler:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'

Dies würde Eve aus Ihren Ergebnissen entfernen und zusätzlich Bobs Wissenschaftswert entfernen, da Subject für sie NULL .

Die korrekte Syntax zum Entfernen von Nicht-Mathematikdatensätzen unter Beibehaltung aller Personen in der Tabelle People lautet:

Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'