SQL指南書 部分的に不一致なキーの検索（自己結合）

SQL 指南書集 目次 - ray88’s diary
■事例１
【テーブル説明】
・同じ家族の人間は家族IDが一致する
・基本的に同じ家族は同じ住所に住んでいるものとする
・ホームズとワトソンのように家族ではないが同居のカップルもいる
【お題】
前田夫妻のように同じ家族だけれど住所が不一致なレコードを検出する

【回答】
自己非等値結合を使うと簡潔なコードが書ける

-- 同じ家族だけど、住所が違うレコードを検索する(p.35)
SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1, Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

【参考】テーブル作成用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');

■事例２
【お題】
以下のテーブルより値段が同じ商品の組み合わせを取得する

【回答】
※DISTINCTを記述し、結果に冗長な行が抽出されないようにすることがポイント

/* 同じ値段だけど、商品名が違うレコードを検索する (p.36) */
SELECT DISTINCT P1.name,P1.price
FROM products P1,products P2
WHERE P1.price = P2.price
	AND
	  P1.name <> P2.name
ORDER BY P1.price;

【テーブル作成用SQL】

CREATE TABLE Products
(name VARCHAR(16) PRIMARY KEY,
 price INTEGER NOT NULL);

INSERT INTO Products VALUES('りんご',	50);
INSERT INTO Products VALUES('みかん',	100);
INSERT INTO Products VALUES('ぶどう',	50);
INSERT INTO Products VALUES('スイカ',	80);
INSERT INTO Products VALUES('レモン',	30);
INSERT INTO Products VALUES('いちご',	100);
INSERT INTO Products VALUES('バナナ',	100);