SQLの自己結合を用いて部分的に不一致なデータの検索をする

はじめに

SQLにおいて、自分はあまり聞き慣れなかった自己結合の今後役立つかもしれないテクニックを紹介していきます。

実行結果はこちらで確認できます。

部分的に不一致なデータの検索

例題1

テーブル

このようなconpaniesテーブルがあるとします。

company_name	company_group_id	address
株式会社エース親会社	1	東京都千代田区千代田1-1
株式会社エース子会社1	1	東京都千代田区千代田1-1
株式会社エース子会社2	1	東京都千代田区千代田1-2
株式会社レイボー食品	2	東京都千代田区皇居外苑1-1
株式会社ステップ工業	3	大阪府豊中市緑丘1-1

株式会社エース親会社と株式会社エース子会社1は同じ住所なのに、株式会社エース子会社2が微妙に違っていてこれが間違っているとします。

同じ会社（同じcompany_id）だけど、住所が違うレコードを検出したい場合、どんなクエリがいいでしょうか？

自己結合を用いて解決できます。

Schema (MySQL v5.7)

日本語対応してなかったので、英語に。

    CREATE TABLE companies (
      id INT NOT NULL PRIMARY KEY auto_increment,
      company_name TEXT,
      company_group_id INT,
      address TEXT
    );
    
    INSERT INTO companies VALUES
    	(1, 'ACE', 1, 'Japan Tokyo chiyoda 1-1'),
    	(2, 'ACE_1', 1, 'Japan Tokyo chiyoda 1-1'),
    	(3, 'ACE_2', 1, 'Japan Tokyo chiyoda 1-2'),
    	(4, 'RAINBOW_MEAL', 2, 'Japan Tokyo koukyogaien 1-1'),
    	(5, 'STEP_INDUSTRY', 3, 'Japan Osaka fuchu midorigaoka 1-1');

Query

    SELECT
    	DISTINCT C1.company_name,
        C1.address
    FROM companies C1
    INNER JOIN companies C2
    	ON C1.company_group_id = C2.company_group_id
       AND C1.address <> C2.address;

Results

company_name	address
ACE_2	Japan Tokyo chiyoda 1-2
ACE	Japan Tokyo chiyoda 1-1
ACE_1	Japan Tokyo chiyoda 1-1

自己結合と、非同値結合の組み合わせは、とっても便利ですね。

例題2

スーパーにある果物（商品）テーブルから

同じ価格のものをグループにして、同じ価格じゃない消費は除外したデータにしましょう。

（同じ価格の商品の組み合わせを取得しましょう。）

Schema (MySQL v5.7)

CREATE TABLE services (
  id INT NOT NULL PRIMARY KEY auto_increment,
  name TEXT,
  price INT
);

INSERT INTO services VALUES
	(1, 'apple', 50),
	(2, 'orange', 100),
	(3, 'grape', 50),
	(4, 'melon', 80),
	(5, 'remon', 30),
	(6, 'strawberry', 100),
	(7, 'banana', 100);

Query

SELECT
	DISTINCT S1.name,
             S1.price
FROM services S1
INNER JOIN services S2
	ON S1.price = S2.price
   AND S1.name <> S2.name
ORDER BY S1.price

Results

name	price
grape	50
apple	50
strawberry	100
banana	100
orange	100

例題1もですが、DISTINCT付けないと、結果に冗長な行が現れるので付けています。

参照

51 - 53p

アウトプット100本ノック実施中