Microsoft SQL Server
参加する
サーチ…
前書き
構造化問合せ言語（SQL）では、JOINは、2つのデータ表を1つの問合せにリンクし、両方の表のデータを一度に含むセットを戻す方法、または1つの表のデータを使用して2番目のテーブルでフィルタリングします。 ANSI SQL標準にはいくつかのタイプのJOINが定義されています。
内部結合
Inner joinは、1つ以上の条件（ ONキーワードを使用して指定）に基づいて両方のテーブルに一致または存在するレコード/行のみを返します。これは最も一般的な結合タイプです。 inner joinの一般的な構文は次のとおりです。
SELECT *
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name
JOINとして単純化することもできます：
SELECT *
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name
例
/* 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
左外部結合を使用した内部結合の使用（存在しないための置換）
このクエリは、Table1とは異なるフィールドとキーを持つTable2のデータを返します。
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;
クロス結合
A cross joinはデカルト結合であり、両方のテーブルのデカルト積を意味します。この結合では、2つの表を結合する条件は必要ありません。左側の表の各行は、右側の表の各行に結合します。クロスジョインの構文：
SELECT * FROM table_1
CROSS JOIN table_2
例：
/* 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;
結果：
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
CROSS JOINを適用できる他の方法があることに注意してください。これは、条件付きの"古いスタイル"の結合（ANSI SQL-92以降で非推奨）です。その結果、クロス/デカルト結合になります。
SELECT *
FROM @Animal, @AnimalSound;
この構文は、「常に真の」結合条件のためにも機能しますが、読みやすくするために、明示的なCROSS JOIN構文を使用することをお勧めしません。回避する必要があります。
SELECT *
FROM
@Animal
JOIN @AnimalSound
ON 1=1
外部結合
左外部結合
LEFT JOINは、 ON句条件が満たされている右側の表の行に一致する、左の表からすべての行を戻します。 ON句が満たされていない行は、すべての右側の表の列にNULLがあります。 LEFT JOINの構文は次のとおりです。
SELECT * FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
右外部結合
RIGHT JOINは、 ON句条件が満たされている左側の表の行に一致する、右側の表からすべての行を戻します。 ON句が満たされていない行は、すべての左テーブルの列にNULLがあります。 RIGHT JOINの構文は次のとおりです。
SELECT * FROM table_1 AS t1
RIGHT JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
完全な外部結合
FULL JOINはLEFT JOINとRIGHT JOIN FULL JOIN組み合わせたものです。 ON句の条件が満たされているかどうかにかかわらず、すべての行が両方の表から戻されます。 ON句を満たさない行は、反対側の表の列すべてにNULLが戻されNULL （つまり、左側の表の行の場合は、右側の表のすべての列にNULL 、逆の場合も同様です）。 FULL JOINの構文は次のとおりです。
SELECT * FROM table_1 AS t1
FULL JOIN table_2 AS t2 ON t1.ID_Column = t2.ID_Column
例
/* 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. */
LEFT OUTER JOIN
SELECT *
FROM @Animal As t1
LEFT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
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
右外側のジョイン
SELECT *
FROM @Animal As t1
RIGHT JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
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
フル・アウター・ジョイン
SELECT *
FROM @Animal As t1
FULL JOIN @AnimalSound As t2 ON t1.AnimalId = t2.AnimalId;
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
更新での結合の使用
結合は、 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
)
次のように、 PreferencesテーブルのSomeSettingカラムをUsersテーブルの述語で更新します。
UPDATE p
SET p.SomeSetting = 1
FROM Users u
JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
pは、文のFROM句に定義されているPreferences別名です。 UsersテーブルのAccountIdと一致する行のみが更新されます。
左外部結合ステートメントによる更新
Update t
SET t.Column1=100
FROM Table1 t LEFT JOIN Table12 t2
ON t2.ID=t.ID
内部結合および集約関数を使用してテーブルを更新する
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
サブクエリに参加する
サブクエリへの結合は、子/詳細テーブルから集計データ（Count、Avg、Max、Minなど）を取得し、親/ヘッダーテーブルのレコードとともにそのデータを表示する場合によく使用されます。たとえば、DateまたはIdに基づいて上位/最初の子ローを取得したい場合や、すべての子ローまたはAverageのCountを必要とする場合があります。
この例では、複数のテーブルが関わっているときにクエリを読みやすくするエイリアスを使用しています。この場合、親テーブルPurchase Ordersからすべての行を取得し、子テーブルPurchaseOrderLineItemsから最後（または最新）の子行のみを取得しています。この例では、子テーブルがインクリメンタルな数値のIDを使用すると仮定しています。
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
自己結合
表は、自己結合と呼ばれるもので結合され、表のレコードを同じ表の他のレコードと結合します。セルフ・ジョインは、通常、表の列内の階層が定義されている問合せで使用されます。
Employeesという表のサンプル・データを考えてみましょう。
| ID | 名 | Boss_ID |
|---|---|---|
| 1 | ボブ | 3 |
| 2 | ジム | 1 |
| 3 | サム | 2 |
各従業員のBoss_IDは別の従業員のIDマップされID 。それぞれの上司の名前を持つ従業員のリストを取得するには、このマッピングを使用してテーブルを結合することができます。このようにテーブルを結合するには、元のテーブルとの区別のためにテーブルへの2番目の参照でエイリアス（この場合はBosses ）を使用する必要があることに注意してください。
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
このクエリを実行すると、次の結果が出力されます。
| 名 | ボス |
|---|---|
| ボブ | サム |
| ジム | ボブ |
| サム | ジム |
結合を使用して削除
結合は、 DELETEステートメントでも使用できます。次のようなスキーマを考えます。
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
)
Preferencesテーブルの行を削除するには、 Usersテーブルの述語を次のようにフィルタリングします。
DELETE p
FROM Users u
INNER JOIN Preferences p ON u.UserId = p.UserId
WHERE u.AccountId = 1234
ここで、 pはステートメントのFROM句で定義されたPreferencesエイリアスであり、一致するAccountIdを持つ行のみをUsersテーブルから削除します。
誤って外部結合を内部結合に変換する
外部結合は、1つまたは両方の表のすべての行と一致する行を戻します。
Table People
PersonID FirstName
1 Alice
2 Bob
3 Eve
Table Scores
PersonID Subject Score
1 Math 100
2 Math 54
2 Science 98
左にテーブルを結合する：
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
戻り値：
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
あなたがすべての人々を返すことを望むなら、適用可能な数学の得点で、よくある間違いは以下のように書くことです：
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
where Subject = 'Math'
これは、ボブの科学スコアを除去することに加えて、検索結果からイブを削除しますSubjectあるNULL彼女のために。
Peopleテーブルにすべての個人を保持したまま、非数式レコードを削除する正しい構文は次のとおりです。
Select * from People a
left join Scores b
on a.PersonID = b.PersonID
and b.Subject = 'Math'
