サーチ…


前書き

構造化問合せ言語(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 JOINLEFT JOINRIGHT 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'


Modified text is an extract of the original Stack Overflow Documentation
ライセンスを受けた CC BY-SA 3.0
所属していない Stack Overflow