内部結合の使用

内部結合の使用

内部結合は、結合する列の値を比較演算子を使用して比較します。

SQL-92 規格では、内部結合は FROM 句でも WHERE 句でも指定できます。SQL-92 の場合、WHERE 句では内部結合のみができます。 WHERE 句で指定する内部結合を旧形式の内部結合と呼んでいます。

次の Transact-SQL クエリは、内部結合例です。

USE pubs
SELECT *
FROM authors AS a INNER JOIN publishers AS p
   ON a.city = p.city
ORDER BY a.au_lname DESC

この内部結合を等結合と呼んでいます。両方のテーブル内のすべての列を返し、結合した列の値が同じ行だけを返します。

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

au_id        au_lname  au_fname phone         address          city
-----------  --------  -------- ------------  ---------------  --------
238-95-7766  Carson    Cheryl   415 548-7723  589 Darwin Ln.    Berkeley
409-56-7008  Bennet    Abraham  415 658-9932  6223 Bateman St.  Berkeley

state zip   contract pub_id pub_name              city     state country
----- ----- -------- ------ --------------------- -------- ----- -------
CA    94705 1        1389   Algodata Infosystems  Berkeley CA    USA
CA    94705 1        1389   Algodata Infosystems  Berkeley CA    USA

(2 row(s) affected)

結果セットの city 列は 2 回表示されています。

 同じ情報を繰り返す必要はないため、選択リストを変更することでこれらの 2 つの同一の列の 1 つを除去することができます。この結果は自然結合と呼ばれます。前の Transact-SQL クエリを自然結合が形成されるように書き換えると、次のようになります。

USE pubs
SELECT p.pub_id, p.pub_name, p.state, a.*
FROM publishers p INNER JOIN authors a
   ON p.city = a.city
ORDER BY a.au_lname ASC, a.au_fname ASC

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

pub_id pub_name              state    au_id        au_lname  au_fname
------ ---------------       -------- -----------  --------  -------- 1389   Algodata Infosystems  CA       409-56-7008  Bennet    Abraham
1389   Algodata Infosystems  CA       238-95-7766  Carson    Cheryl

phone         address          city      state zip   contract
---------------  ------------- --------  ----- ----- ---------
415 658-9932  6223 Bateman St. Berkeley  CA    94705 1
415 548-7723  589 Darwin Ln.   Berkeley  CA    94705 1

(2 row(s) affected)

この例では、publishers.city は結果には表示されません。

等しい演算子以外を使用した結合

2 つの列の値が等しくなくても結合することができます。内部結合で使用する演算子と述語は、等結合以外でも使用できます。 結合で使用できる演算子と述語の詳細については、「式内の演算子の使用」および「」を参照してください。

次の、より大きい (>) 結合の Transact-SQL の例では、New Moon Books の所在地であるマサチューセッツ州よりもアルファベット順で後ろに来る州に住んでいる New Moon の著者が検索されます。

USE pubs
SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state
FROM publishers p INNER JOIN authors a
   ON a.state > p.state
WHERE p.pub_name = 'New Moon Books'
ORDER BY au_lname ASC, au_fname ASC

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

pub_name         state   au_lname             au_fname             state
---------------- ------- -------------------- -------------------- -----
New Moon Books   MA    Blotchet-Halls         Reginald             OR
New Moon Books   MA    del Castillo           Innes                MI
New Moon Books   MA    Greene                 Morningstar          TN
New Moon Books   MA    Panteley               Sylvia               MD
New Moon Books   MA    Ringer                 Albert               UT
New Moon Books   MA    Ringer                 Anne                 UT

(6 row(s) affected)
不等号演算子を使用した結合

不等号結合 (< >) はめったに使われません。一般的な規則として、不等号結合が意味を成すのは、自己結合と共に使われる場合のみです。たとえば、次の等しくない Transact-SQL 結合と自己結合は、価格の異なる $15 未満の廉価な書籍が 2 冊以上含まれるカテゴリを検索するのに使われます。

USE pubs
SELECT DISTINCT t1.type, t1.price
FROM titles t1 INNER JOIN titles t2
   ON t1.type = t2.type
   AND t1.price <> t2.price
WHERE t1.price < $15 AND t2.price < $15

  式 NOT column_name = column_namecolumn_name <> column_name と同じです。

この Transact-SQL の例では、不等号結合と自己結合を組み合わせて使用し、同じ title_id だが異なる au_id 番号の行が 2 行以上ある、つまり複数の著者による書籍の行を、titleauthor テーブルからすべて検索します。

USE pubs
SELECT DISTINCT t1.au_id, t1.title_id
FROM titleauthor t1 INNER JOIN titleauthor t2
   ON t1.title_id = t2.title_id
WHERE t1.au_id <> t2.au_id
ORDER BY t1.au_id

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

au_id            title_id
-----------         --------
213-46-8915         BU1032
267-41-2394         BU1111
267-41-2394         TC7777
409-56-7008         BU1032
427-17-2319         PC8888
472-27-2349         TC7777
672-71-3249         TC7777
722-51-5454         MC3021
724-80-9391         BU1111
724-80-9391         PS1372
756-30-7391         PS1372
846-92-7186         PC8888
899-46-2035         MC3021
899-46-2035         PS2091
998-72-3567         PS2091

(15 row(s) affected)

関連項目

変換関数

表示: