自己結合の使用

自己結合の使用

自己結合では、テーブルをそれ自体と結合できます。 たとえば、自己結合を使って、カリフォルニア州オークランド内の同じ郵便番号の地域に住んでいる作家を検索することができます。

次のクエリでは、authors テーブルのそれ自体との結合を利用しているため、authors テーブルは 2 つの役割で現れることになります。 これらの役割を区別するために、FROM 句において、authors テーブルに 2 つの異なる別名、au1 および au2 を与える必要があります。これらの別名は、クエリのほかの部分において、列名を修飾するのに使われます。以下に自己結合 Transact-SQL ステートメントを示します。

USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
   ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
ORDER BY au1.au_fname ASC, au1.au_lname ASC

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

au_fname             au_lname            au_fname             au_lname
-------------------- ------------------- -------------------- ---------
Dean                 Straight            Dean                 Straight
Dean                 Straight            Dirk                 Stringer
Dean                 Straight            Livia                Karsen
Dirk                 Stringer            Dean                 Straight
Dirk                 Stringer            Dirk                 Stringer
Dirk                 Stringer            Livia                Karsen
Livia                Karsen              Dean                 Straight
Livia                Karsen              Dirk                 Stringer
Livia                Karsen              Livia                Karsen
Marjorie             Green               Marjorie             Green
Stearns              MacFeather          Stearns              MacFeather

(11 row(s) affected)

結果から、著者が本人と照合されている行と、著者の順序が逆なだけで、同じことを表している行を除去するために、この Transact-SQL 自己結合クエリを次のように変更します。

USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
   ON  au1.zip = au2.zip
WHERE au1.city = 'Oakland'
   AND au1.state = 'CA'
   AND au1.au_id < au2.au_id
ORDER BY au1.au_lname ASC, au1.au_fname ASC

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

au_fname     au_lname          au_fname             au_lname
------------ ----------------- -------------------- --------------------
Dean         Straight          Dirk                 Stringer
Dean         Straight          Livia                Karsen
Dirk         Stringer          Livia                Karsen

(3 row(s) affected)

これで、Dean Straight、Dirk Stringer、および Livia Karsen の 3 人がカリフォルニア州オークランドの同じ郵便番号の地域に住んでいることがはっきりわかります。

関連項目

式内の演算子の使用

表示: