Microsoft SQL Server
Aderire
Ricerca…
introduzione
In Structured Query Language (SQL), un JOIN è un metodo per collegare due tabelle di dati in una singola query, consentendo al database di restituire una serie che contiene i dati di entrambe le tabelle contemporaneamente o utilizzando i dati di una tabella per utilizzarli come Filtro sul secondo tavolo. Esistono diversi tipi di JOIN definiti nello standard ANSI SQL.
Join interno
Inner join restituisce solo quei record / righe che corrispondono / esistono in entrambe le tabelle in base a una o più condizioni (specificate usando la parola chiave ON ). È il tipo più comune di join. La sintassi generale per l' inner join è:
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
Può anche essere semplificato semplicemente come JOIN :
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
Esempio
/* 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
Utilizzo di inner join con outer join sinistro (sostituto di non esiste)
Questa query restituirà i dati dalla tabella 1 dove i campi che corrispondono a table2 con una chiave e i dati non nella Tabella 1 quando si confrontano con Table2 con una condizione e una chiave
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 è un join cartesiano, che significa un prodotto cartesiano di entrambi i tavoli. Questo join non ha bisogno di alcuna condizione per unire due tabelle. Ogni riga nella tabella di sinistra si unirà a ciascuna riga della tabella di destra. Sintassi per un cross join:
SELECT * FROM table_1
CROSS JOIN table_2
Esempio:
/* 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;
risultati:
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
Nota che ci sono altri modi in cui un CROSS JOIN può essere applicato. Questo è un join "vecchio stile" (deprecato dal ANSI SQL-92) senza condizione, che risulta in un cross / Cartesian join:
SELECT *
FROM @Animal, @AnimalSound;
Questa sintassi funziona anche a causa di una condizione di join "sempre true", ma non è raccomandata e dovrebbe essere evitata, a favore della sintassi CROSS JOIN esplicita, per motivi di leggibilità.
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
Outer Join
Left Outer Join
LEFT JOIN restituisce tutte le righe dalla tabella sinistra, abbinate alle righe della tabella di destra in cui sono soddisfatte le condizioni della clausola ON . Le righe in cui non viene soddisfatta la clausola ON hanno NULL in tutte le colonne della tabella destra. La sintassi di un LEFT JOIN è:
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Giusto outer join
RIGHT JOIN restituisce tutte le righe dalla tabella di destra, abbinate alle righe della tabella sinistra in cui sono soddisfatte le condizioni della clausola ON . Le righe in cui non viene soddisfatta la clausola ON hanno NULL in tutte le colonne della tabella sinistra. La sintassi di RIGHT JOIN è:
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Full Outer Join
FULL JOIN combina LEFT JOIN e RIGHT JOIN . Tutte le righe vengono restituite da entrambe le tabelle, indipendentemente dal fatto che siano soddisfatte le condizioni nella clausola ON . Le righe che non soddisfano la clausola ON vengono restituite con NULL in tutte le colonne della tabella opposta (ovvero, per una riga nella tabella sinistra, tutte le colonne nella tabella destra conterranno NULL e viceversa). La sintassi di un FULL JOIN è:
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
Esempi
/* 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. */
SINISTRA ESTERNO
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Risultati per 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
GIUSTO ESTERNO
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Risultati per 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 OUTER JOIN
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
Risultati per 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
Utilizzo Partecipa a un aggiornamento
I join possono anche essere utilizzati in un'istruzione 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
)
Aggiorna la colonna SomeSetting del filtro delle tabelle delle Preferences base a un predicato sulla tabella Users come segue:
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
p è un alias per le Preferences definite nella clausola FROM dell'istruzione. Verranno aggiornate solo le righe con un AccountId corrispondente dalla tabella Users .
Aggiorna con dichiarazioni di outer outer a sinistra
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
Aggiorna tabelle con join interno e funzione di aggregazione
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
Partecipa a una sottoquery
Unirsi a una sottoquery viene spesso utilizzato quando si desidera ottenere dati aggregati (come Count, Avg, Max o Min) da una tabella child / details e visualizzarli insieme ai record dalla tabella parent / header. Ad esempio, potresti voler recuperare la riga superiore / primo figlio in base a Data o Id o forse vuoi un Conteggio di tutte le Righe figlio o una Media.
Questo esempio utilizza alias che facilita la lettura delle query quando sono coinvolte più tabelle. In questo caso, recuperiamo tutte le righe dalla tabella padre Ordini d'acquisto e recuperiamo solo l'ultima (o più recente) riga secondaria dalla tabella figlio PurchaseOrderLineItems. Questo esempio presuppone che la tabella figlio utilizza ID numerici incrementali.
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
Una tabella può essere unita a se stessa in quello che è noto come self join, combinando i record nella tabella con altri record nella stessa tabella. I join automatici vengono in genere utilizzati nelle query in cui è definita una gerarchia nelle colonne della tabella.
Considera i dati di esempio in una tabella denominata Employees :
| ID | Nome | Boss_ID |
|---|---|---|
| 1 | peso | 3 |
| 2 | Jim | 1 |
| 3 | Sam | 2 |
Ogni dipendente Boss_ID mappato di un altro dipendente ID . Per recuperare un elenco di dipendenti con il nome del rispettivo capo, la tabella può essere unita su se stessa utilizzando questa mappatura. Si noti che unirsi a una tabella in questo modo richiede l'uso di un alias ( Bosses in questo caso) sul secondo riferimento alla tabella per distinguersi dalla tabella originale.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
L'esecuzione di questa query produrrà i seguenti risultati:
| Nome | Capo |
|---|---|
| peso | Sam |
| Jim | peso |
| Sam | Jim |
Elimina usando Join
I join possono anche essere utilizzati in una istruzione DELETE . Dato uno schema come segue:
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
)
Possiamo eliminare le righe dalla tabella delle Preferences , filtrandole secondo un predicato sulla tabella Users come segue:
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
Qui p è un alias per le Preferences definite nella clausola FROM AccountId e cancelliamo solo le righe che hanno un AccountId corrispondente dalla tabella Users .
Girando accidentalmente un'unione esterna in un'unione interna
Join esterni restituiscono tutte le righe da una o entrambe le tabelle, più le righe corrispondenti.
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
A sinistra si uniscono ai tavoli:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
Ritorna:
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
Se si desidera restituire tutte le persone, con qualsiasi punteggio matematico applicabile, un errore comune è scrivere:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
Ciò eliminerebbe Eva dai tuoi risultati, oltre a rimuovere il punteggio scientifico di Bob, poiché Subject è NULL per lei.
La sintassi corretta per rimuovere i record non matematici mantenendo tutti gli individui nella tabella People potrebbe essere:
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'