外部結合の使用

外部結合の使用

両方のテーブルで結合条件に一致する行が 1 つでもある場合だけ、内部結合は行を返します。つまり、内部結合は他方のテーブルと一致しない行を除去します。これに対し、外部結合の場合は、FROM 句で指定されたテーブルまたはビューのどれかにあり、WHERE 検索条件または HAVING 検索条件のどれかを満たしている行をすべて返します。左外部結合の場合、参照された左テーブルのすべての行が取得されます。右外部結合の場合、参照された右テーブルのすべての行が取得されます。完全外部結合の場合、両方のテーブルのすべての行が返されます。

Microsoft® SQL Server™ では、FROM 句で指定する外部結合に対して次の SQL-92 キーワードを使用します。

  • LEFT OUTER JOIN または LEFT JOIN

  • RIGHT OUTER JOIN または RIGHT JOIN

  • FULL OUTER JOIN または FULL JOIN

SQL Server は、SQL-92 外部結合構文をサポートするほか、WHERE 句で *= 演算子と =* 演算子を使用する従来の外部結合指定構文もサポートします。従来の Transact-SQL 外部結合ではあいまいな結果が返されることがあるので、SQL-92 構文を使用することをお勧めします。

左外部結合の使用

authors テーブルと publishers テーブルを city 列で結合する場合を考えてみます。結果には、出版社がある都市に住む著者 (この場合 Abraham Bennet および Cheryl Carson) のみが表示されます。

出版社と同じ都市に住んでいるかどうかにかかわらず、すべての著者が結果に含まれるようにするには、SQL-92 左外部結合を使います。 この Transact-SQL 左外部結合のクエリおよび結果は以下のようになります。

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

以下に結果セットを示します。

au_fname             au_lname                       pub_name
-------------------- ------------------------------ -----------------
Reginald             Blotchet-Halls                 NULL
Michel               DeFrance                       NULL
Innes                del Castillo                   NULL
Ann                  Dull                           NULL
Marjorie             Green                          NULL
Morningstar          Greene                         NULL
Burt                 Gringlesby                     NULL
Sheryl               Hunter                         NULL
Livia                Karsen                         NULL
Charlene             Locksley                       NULL
Stearns              MacFeather                     NULL
Heather              McBadden                       NULL
Michael              O'Leary                        NULL
Sylvia               Panteley                       NULL
Albert               Ringer                         NULL
Anne                 Ringer                         NULL
Meander              Smith                          NULL
Dean                 Straight                       NULL
Dirk                 Stringer                       NULL
Johnson              White                          NULL
Akiko                Yokomoto                       NULL
Abraham              Bennet                         Algodata Infosystems
Cheryl               Carson                         Algodata Infosystems

(23 row(s) affected)

この左外部結合は、publishers テーブルの city 列と一致するかどうかにかかわらず、authors テーブルのすべての行を結果に含めます。 この結果では、返された著者のほとんどに一致するデータがなく、pub_name 列のこれらの行には NULL 値が入っていることに注意してください。

右外部結合の使用

authors テーブルと publishers テーブルを city 列で結合する場合を考えてみます。結果には、出版社がある都市に住む著者 (この場合 Abraham Bennet および Cheryl Carson) のみが表示されます。SQL-92 右外部結合演算子 RIGHT OUTER JOIN は、1 つ目のテーブルに一致するデータがあるかどうかにかかわらず、2 つ目のテーブルのすべての行が結果に含まれることを示します。

著者が住んでいる都市に出版社があるかどうかにかかわらず、すべての出版社を結果に含めるには、SQL-92 右外部結合を使用します。この外部結合の Transact-SQL クエリおよび結果は次のようになります。

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

以下に結果セットを示します。

au_fname             au_lname                 pub_name
-------------------- ------------------------ --------------------
Abraham              Bennet                   Algodata Infosystems
Cheryl               Carson                   Algodata Infosystems
NULL                 NULL                     Binnet & Hardley
NULL                 NULL                     Five Lakes Publishing
NULL                 NULL                     GGG&G
NULL                 NULL                     Lucerne Publishing
NULL                 NULL                     New Moon Books
NULL                 NULL                     Ramona Publishers
NULL                 NULL                     Scootney Books

(9 row(s) affected)

述語 (結合を定数と比較するなど) を使用すると、外部結合をさらに制限できます。 以下の例は、同じ右外部結合ですが、販売部数が 50 部未満のタイトルは除去されます。

USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
   ON s.title_id = t.title_id
   AND s.qty > 50
ORDER BY s.stor_id ASC

以下に結果セットを示します。

stor_id qty    title
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
            Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets
(null) (null) Emotional Security: A New Algorithm
(null) (null) Fifty Years in Buckingham Palace Kitchens
7066   75     Is Anger the Enemy?
(null) (null) Life Without Fear
(null) (null) Net Etiquette
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
            Mediterranean
(null) (null) Prolonged Data Deprivation: Four Case Studies
(null) (null) Secrets of Silicon Valley
(null) (null) Silicon Valley Gastronomic Treats
(null) (null) Straight Talk About Computers
(null) (null) Sushi, Anyone?
(null) (null) The Busy Executive's Database Guide
(null) (null) The Gourmet Microwave
(null) (null) The Psychology of Computer Cooking
(null) (null) You Can Combat Computer Stress!

(18 row(s) affected)

述語の詳細については、「」を参照してください。

完全外部結合の使用

結合の結果に一致しない行も含むことによって、一致しない情報を保持することが望ましい場合、完全外部結合を使うことができます。 Microsoft® SQL Server™ には、完全外部結合演算子 FULL OUTER JOIN があります。この演算子を使用すると、他方のテーブルに一致する値があるかどうかにかかわらず、両方のテーブルのすべての行が結果に含まれます。

authors テーブルと publishers テーブルを city 列で結合する場合を考えてみます。結果には、出版社がある都市に住む著者 (この場合 Abraham Bennet および Cheryl Carson) のみが表示されます。SQL-92 FULL OUTER JOIN 演算子は、両方のテーブルのデータが一致しているかどうかにかかわらず、結果に両方のテーブルのすべての行を含めることを示します。

著者が住んでいる都市に出版社があるかどうか、または出版社と同じ都市に住んでいるかどうかにかかわらず、すべての出版社とすべての著者を結果に含めるには、完全外部結合を使用します。 この Transact-SQL 完全外部結合のクエリおよび結果は以下のようになります。

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

以下に結果セットを示します。

au_fname             au_lname                     pub_name
-------------------- ---------------------------- --------------------
Reginald             Blotchet-Halls               NULL
Michel               DeFrance                     NULL
Innes                del Castillo                 NULL
Ann                  Dull                         NULL
Marjorie             Green                        NULL
Morningstar          Greene                       NULL
Burt                 Gringlesby                   NULL
Sheryl               Hunter                       NULL
Livia                Karsen                       NULL
Charlene             Locksley                     NULL
Stearns              MacFeather                   NULL
Heather              McBadden                     NULL
Michael              O'Leary                      NULL
Sylvia               Panteley                     NULL
Albert               Ringer                       NULL
Anne                 Ringer                       NULL
Meander              Smith                        NULL
Dean                 Straight                     NULL
Dirk                 Stringer                     NULL
Johnson              White                        NULL
Akiko                Yokomoto                     NULL
Abraham              Bennet                       Algodata Infosystems
Cheryl               Carson                       Algodata Infosystems
NULL                 NULL                         Binnet & Hardley
NULL                 NULL                         Five Lakes Publishing
NULL                 NULL                         GGG&G
NULL                 NULL                         Lucerne Publishing
NULL                 NULL                         New Moon Books
NULL                 NULL                         Ramona Publishers
NULL                 NULL                         Scootney Books

(30 row(s) affected)

関連項目

式内の演算子の使用

読みやすいコードの作成

表示: