セージ の メモ書き

メモこそ命の恩人だ

SQL - 結合操作

実験用テーブル

USE SampleDB

/* テーブル削除 */
IF ObJECt_ID('[SampleTable1]') IS NOT NULL DROP TABLE [SampleTable1];
IF ObJECt_ID('[SampleTable2]') IS NOT NULL DROP TABLE [SampleTable2];
IF ObJECt_ID('[SampleTable3]') IS NOT NULL DROP TABLE [SampleTable3];

/* テーブル追加 */
CREATE TABLE [SampleTable1]
(
    [Id] int NOT NULL,
    [Sample1] nvarchar(50),
    PRIMARY KEY ([Id])
);
CREATE TABLE [SampleTable2]
(
    [Id] int NOT NULL,
    [Sample2] nvarchar(50),
    PRIMARY KEY ([Id])
);
CREATE TABLE [SampleTable3]
(
    [Id] int NOT NULL,
    [Sample3] nvarchar(50),
    PRIMARY KEY ([Id])
);


GO

/* レコード追加 */
INSERT INTO [dbo].[SampleTable1]([Id],[Sample1]) VALUES (1, 'サンプル1-1')
INSERT INTO [dbo].[SampleTable1]([Id],[Sample1]) VALUES (2, 'サンプル1-2')
INSERT INTO [dbo].[SampleTable1]([Id],[Sample1]) VALUES (3, 'サンプル1-3')
INSERT INTO [dbo].[SampleTable2]([Id],[Sample2]) VALUES (1, 'サンプル2-1')
INSERT INTO [dbo].[SampleTable2]([Id],[Sample2]) VALUES (2, 'サンプル2-2')
INSERT INTO [dbo].[SampleTable2]([Id],[Sample2]) VALUES (4, 'サンプル2-4')
INSERT INTO [dbo].[SampleTable3]([Id],[Sample3]) VALUES (1, 'サンプル3-1')
INSERT INTO [dbo].[SampleTable3]([Id],[Sample3]) VALUES (5, 'サンプル3-5')


GO


直積

SELECT * FROM SampleTable1,SampleTable2 

Id  Sample1 Id  Sample2
1  サンプル1-1   1  サンプル2-1
2  サンプル1-2   1  サンプル2-1
3  サンプル1-3   1  サンプル2-1
1  サンプル1-1   2  サンプル2-2
2  サンプル1-2   2  サンプル2-2
3  サンプル1-3   2  サンプル2-2
1  サンプル1-1   4  サンプル2-4
2  サンプル1-2   4  サンプル2-4
3  サンプル1-3   4  サンプル2-4
SELECT * FROM SampleTable1, SampleTable2, SampleTable3

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1
1  サンプル1-1   2  サンプル2-2   1  サンプル3-1
1  サンプル1-1   4  サンプル2-4   1  サンプル3-1
2  サンプル1-2   1  サンプル2-1   1  サンプル3-1
2  サンプル1-2   2  サンプル2-2   1  サンプル3-1
2  サンプル1-2   4  サンプル2-4   1  サンプル3-1
3  サンプル1-3   1  サンプル2-1   1  サンプル3-1
3  サンプル1-3   2  サンプル2-2   1  サンプル3-1
3  サンプル1-3   4  サンプル2-4   1  サンプル3-1
1  サンプル1-1   1  サンプル2-1   5  サンプル3-5
1  サンプル1-1   2  サンプル2-2   5  サンプル3-5
1  サンプル1-1   4  サンプル2-4   5  サンプル3-5
2  サンプル1-2   1  サンプル2-1   5  サンプル3-5
2  サンプル1-2   2  サンプル2-2   5  サンプル3-5
2  サンプル1-2   4  サンプル2-4   5  サンプル3-5
3  サンプル1-3   1  サンプル2-1   5  サンプル3-5
3  サンプル1-3   2  サンプル2-2   5  サンプル3-5
3  サンプル1-3   4  サンプル2-4   5  サンプル3-5

INNER JOIN

SELECT * FROM SampleTable1 
INNER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id

Id  Sample1 Id  Sample2
1  サンプル1-1   1  サンプル2-1
2  サンプル1-2   2  サンプル2-2
-- ON を USING に変更した場合、エラー発生
-- SQL Server の場合、USING は対応していない
SELECT * FROM SampleTable1 
INNER JOIN SampleTable2 USING (Id)
-- "Id" はテーブル ヒント オプションとして認識されません。使用目的がテーブル値関数または CHANGETABLE 関数のパラメーターの場合は、データベース互換性モードが 90 に設定されていることを確認してください。
-- 上記の"INNER JOIN" と "直積から等しいIDの抽出" は同じ結果
SELECT * 
FROM SampleTable1, SampleTable2
WHERE SampleTable1.Id = SampleTable2.Id

Id  Sample1 Id  Sample2
1  サンプル1-1   1  サンプル2-1
2  サンプル1-2   2  サンプル2-2
-- 3つのテーブルを結合する。結果がどんどん減る。
SELECT * 
FROM SampleTable1 
INNER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
INNER JOIN SampleTable3 ON SampleTable2.Id = SampleTable3.Id

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1
-- 上記の結合順序を変えても結果は同じ。
SELECT * 
FROM SampleTable1 
INNER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
INNER JOIN SampleTable3 ON SampleTable1.Id = SampleTable3.Id

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1

LEFT OUTERJOIN

SELECT * 
FROM SampleTable1 
LEFT OUTER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id

Id  Sample1 Id  Sample2
1  サンプル1-1   1  サンプル2-1
2  サンプル1-2   2  サンプル2-2
3  サンプル1-3   NULL    NULL
-- ON を USING に変更した場合、エラー発生
-- SQL Server の場合、USING は対応していない
SELECT * 
FROM SampleTable1 
LEFT OUTER JOIN SampleTable2 USING (Id)
-- "Id" はテーブル ヒント オプションとして認識されません。使用目的がテーブル値関数または CHANGETABLE 関数のパラメーターの場合は、データベース互換性モードが 90 に設定されていることを確認してください。
-- 3つのテーブルを結合する。
SELECT * 
FROM SampleTable1 
LEFT OUTER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
LEFT OUTER JOIN SampleTable3 ON SampleTable1.Id = SampleTable3.Id

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1
2  サンプル1-2   2  サンプル2-2   NULL    NULL
3  サンプル1-3   NULL    NULL    NULL    NULL
-- 上記の結合順序を変えても結果は同じ。
-- 結合したものをベースに、結合処理が行われる。
-- SampleTable2 と SampleTable3 で等しいIDがあっても抽出できない。
SELECT * 
FROM SampleTable1 
LEFT OUTER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
LEFT OUTER JOIN SampleTable3 ON SampleTable2.Id = SampleTable3.Id

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1
2  サンプル1-2   2  サンプル2-2   NULL    NULL
3  サンプル1-3   NULL    NULL    NULL    NULL
-- 最後に INNER JOIN も加えてみる。
-- INNER JOIN で処理するので、結果が激減した。
-- SampleTable1 と SampleTable2 の結果をもとに、SampleTable3 で INNER JOIN されてる。
SELECT * 
FROM SampleTable1 
LEFT OUTER JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
INNER JOIN SampleTable3 ON SampleTable1.Id = SampleTable3.Id

Id  Sample1 Id  Sample2 Id  Sample3
1  サンプル1-1   1  サンプル2-1   1  サンプル3-1

NATURAL JOIN

-- SQL Server の場合、NATURAL JOIN は対応していない
SELECT * FROM SampleTable1 
NATURAL JOIN SampleTable2 ON SampleTable1.Id = SampleTable2.Id
-- マルチパート識別子 "SampleTable1.Id" をバインドできませんでした。

試験問題:LEFT OUTER JOIN

  • テーブル名のところに、SELECT がある場合もある。
SELECT * FROM テーブル名, (SELECT ..) AS A1 LEFT OUTER JOIN (SELECT ...) AS A2



以上