1-2．自己結合の使い方

重複順列・順列・組み合わせ

　次のような商品テーブルに、「バナナ、りんご、みかん」の3レコードが登録されているとします。売上を調べる統計表を作成する場合などに、これらの品物の組み合わせを取得したいことがあります。

Products

name price

（商品名） （値段）

バナナ 80

みかん 50

りんご 100

　「組み合わせ」とひとことで言っても、その種類は2つあります。一つが、並び順を意識した順序対（ordered pair）、もう一つが順序を意識しない非順序対（unordered pair）です。順序対は、<1, 2>のように尖った括弧で、非順序対は{1, 2}のような括弧で表記します。順序対は、順序が違えば別物なので、<1, 2> ≠ <2, 1>ですが、非順序対の場合は順序を無視するので、{1, 2} = {2, 1}です。

　さて、次のように単純に直積を作ると、順序対が得られます。

P1 P2

--重複順列を得るSQL

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products P1,

Products P2;

結果：

name_1 name_2

バナナ バナナ

みかん バナナ

りんご バナナ

バナナ みかん

みかん みかん

りんご みかん

バナナ りんご

みかん りんご

りんご りんご

一行が一つの順序対を表します。結果行数は重複順列で ３Λ２= 9 です。この結果には冗長な（りんご, りんご）という行が含まれますし、（りんご, みかん）と（みかん, りんご）という順序を変えただけの組み合わせも異なる行として現れます。これは、先に述べたように順序を意識した集合だからです。

　ここから、冗長な集合を排除する変更を考えます。まず、（りんご, りんご）のような同一要素の組み合わせを除外するには、次のように条件を追加した結合を行います。

--順列を得るSQL

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products P1,

Products P2

WHERE P1.name <> P2.name;

　　結果：

name_1 name_2

みかん バナナ

りんご バナナ

バナナ みかん

りんご みかん

バナナ りんご

みかん りんご

「WHERE P1.name <> P2.name」という結合条件によって、同一要素の組み合わせを排除しています。結果行数の計算は順列で 3P2 = 6 です。この結合を理解するポイントは、次のような2つのテーブルが本当に2つあるのだと想像することです。

参考

   nPr = [n から１ずつ降りて行ってr個掛け合わせる] なので例えば 10P3 というと [10 から１ずつ降りて行って3個掛け合わせる]ということで 10P3 = 10 × 9 × 8 = 720 となる、ということです。

（バナナ、バナナ）の組み合わせはダメ

　　　　　　P1　　　　　　　　　　P2

　もちろん、P1もP2も、物理的には同じ「Products」テーブルとして格納されています。しかし、SQLにおいて異なる別名が与えられたなら、たとえ同一のテーブルであっても、それらは異なるテーブル（集合）と見なされます。P1とP2はたまたま保持するデータが等しかっただけの、異なる2つの集合として考えられる、ということです。すると、この自己結合の動作は

• P1の「バナナ」行の結合対象は、P2の「りんご、みかん」の2行

• P1の「りんご」行の結合対象は、P2の「みかん、バナナ」の2行

• P1の「みかん」行の結合対象は、P2の「りんご、バナナ」の2行

　というように、異なるテーブルを使う通常の結合と同様に考えることができます。このように考えれば、自己結合の「自己」という接頭辞にも大きな意味はありません。

　さて、この結果も、まだ順序対です。ここからさらに、（りんご, みかん）と（みかん, りんご）のような順序を入れ替えた組み合わせを排除することを考えます。次のSQLを見てください。

--組み合わせを得るSQL

SELECT P1.name AS name_1, P2.name AS name_2

FROM Products P1,

Products P2

WHERE P1.name > P2.name;

　　　結果：

name_1 name_2

みかん バナナ

りんご バナナ

りんご みかん

　ここでもやはり、P1、P2という2つのテーブルが存在すると考えてください。結果行数の計算は組み合わせで 3C2 = 3 です。ここまで絞ってようやく非順序対が得られました。恐らく、私たちが普段「組み合わせ」と言うとき、念頭においているのはこのタイプのものでしょう。

　3つ以上の列を使いたいときも、次のように簡単に拡張できます。

--組み合わせを得るSQL 3列の拡張版

SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3

FROM Products P1,

Products P2,

Products P3

WHERE P1.name > P2.name

AND P2.name > P3.name;

　この例題のように等号「=」以外の比較演算子である < や >、<> を使って行う結合を「非等値結合」と言います。それを自己結合と組み合わせているので、「自己非等値結合」です。列の組み合わせを作りたいときに多用するので、覚えておくとよいでしょう。

　また、>、< などの比較演算子は数値型の列に限らず、文字型でも辞書順比較として機能するということも、今回のちょっとしたワンポイントです。

https://codezine.jp/article/detail/460

自己結合の使い方 (1/2)：CodeZine（コードジン）

重複行を削除する

　重複行というのは、リレーショナル・データベースの世界においてNULLと並んで嫌われる存在です。そのため、これを排除するための方法も数多く考えられています。例えば、先の例題で使った商品テーブルで、「みかん」に重複が生じているテーブルを考えます。このテーブルには恐ろしいことに主キーすら設定されていません（というより、設定できません）。こんなテーブルはすぐにでも「掃除」する必要があります。

SQL Server では、Oracleのrowid　が無いため下記の方法を使います。

INF: テーブルから重複行を削除する方法　より

例外的な状況の下では重複した主キーが発生する可能性があり、その場合は重複キーを除去する必要があります。主キーが重複する状況の 1 つは、SQL Server 外の非リレーショナル データ中に重複キーがあり、PK の一意性を強制しないでそのデータをインポートした場合です。主キーが重複するもう 1 つの状況は、各テーブルで実体の整合性を強制していないなど、データベースのデザイン エラーが原因となる場合です。

多くの場合、重複 PK が見つかるのは、一意のインデックスの作成時です。重複キーが見つかるとインデックスの作成は中止され、次のメッセージが表示されます。

Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.

ここでは、テーブル内の重複した主キーを検索して削除する方法について説明します。ただし、重複キーの再発を予防するために、重複が発生した理由を綿密に調べる必要があります。

この例では、次に示す、重複 PK 値を持つテーブルを使用します。このテーブルの主キーは、2 つの列 (col1、col2) から構成されています。2 つの行の PK が重複しているため、一意のインデックスおよび PRIMARY KEY 制約を作成することができません。テーブルに続くプロシージャで、重複した PK を識別して削除する方法を示します。

create table t1(col1 int, col2 int, col3 char(50))

insert into t1 values (1, 1, 'data value one')

insert into t1 values (1, 1, 'data value one')

insert into t1 values (1, 2, 'data value two')

SELECT col1, col2, count(*) AS '重複数'

FROM t1

GROUP BY col1, col2

HAVING count(*) > 1

上記のコードにより、テーブル内にある重複 PK 値の各セットについて 1 行が返されます。この結果の最後の列は、特定の PK 値が重複している数を示します。

col1 col2 重複数

1 1 2

1. 部分的に不一致なキーの検索

　まずは部分的に不一致なキーを検索するSQLについてです。「自己結合の使い方」では、以下の自己非等値結合を使うSQLが提示されています。

CREATE TABLE Addresses

(name VARCHAR(32),

family_id INTEGER,

address VARCHAR(32),

PRIMARY KEY(name, family_id));

INSERT INTO Addresses VALUES('前田 義明', '100', '東京都港区虎ノ門3-2-29');

INSERT INTO Addresses VALUES('前田 由美', '100', '東京都港区虎ノ門3-2-92');

INSERT INTO Addresses VALUES('加藤 茶', '200', '東京都新宿区西新宿2-8-1');

INSERT INTO Addresses VALUES('加藤 勝', '200', '東京都新宿区西新宿2-8-1');

INSERT INTO Addresses VALUES('ホームズ', '300', 'ベーカー街221B');

INSERT INTO Addresses VALUES('ワトソン', '400', 'ベーカー街221B');

INSERT INTO Addresses VALUES('織田 信長', '500', '京都');

INSERT INTO Addresses VALUES('織田 信忠', '500', '京都');

INSERT INTO Addresses VALUES('徳川 家康', '600', '関ヶ原');

INSERT INTO Addresses VALUES('松平 忠吉', '600', '関ヶ原');

INSERT INTO Addresses VALUES('徳川 秀忠', '600', '上田城');

/* 同じ家族だけど、住所が違うレコードを検索する */

SELECT DISTINCT A1.name, A1.address

FROM Addresses A1, Addresses A2

WHERE A1.family_id = A2.family_id

AND A1.address <> A2.address ;