SQL指南書 外部結合⑨ 商集合（関係除算）

SQL 指南書集 目次 - ray88’s diary
SQL指南書 HAVING句⑤ 関係徐算でバスケット解析 - ray88’s diary
■例題
　「item」テーブルのすべての商品を揃えている店舗を「shopitems」テーブルから選択する

STEP1:ShopItemテーブルからshop列の重複を取り除いたテーブル「SI1」を抽出

STEP２:相関サブクエリ
　　　主クエリのShopItems SI1の各行（店舗）ごとに、相関サブクエリが評価されます。
　　　このサブクエリでは、ItemsテーブルとShopItems SI2テーブルを左外部結合します。
　　　この結合条件は、I.item = SI2.item と SI1.shop = SI2.shop です。
　　　この条件で、特定の店舗の全商品との結合結果を得ます。
　　　サブクエリのWHERE句のSI2.item IS NULLは、外部結合でマッチしなかった行を取得します。
　　　この結果は、特定の店舗で取り扱っていない商品のリストになります。

STEP３：主クエリのフィルタリング
　　　　NOT EXISTSは、サブクエリが結果を返さない場合に真となります。
　　　　これは、ある店舗で全ての商品が取り扱われている場合に当てはまります。

/* 外部結合で関係除算：差集合の応用(p.99) */
SELECT DISTINCT shop
  FROM ShopItems SI1
WHERE NOT EXISTS
      (SELECT I.item 
         FROM Items I LEFT OUTER JOIN ShopItems SI2
           ON SI1.shop = SI2.shop
          AND I.item   = SI2.item 
        WHERE SI2.item IS NULL) ;

【テーブル作成SQL】

/* 関係除算でバスケット解析 */
CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
 
CREATE TABLE ShopItems
(shop VARCHAR(16),
 item VARCHAR(16),
    PRIMARY KEY(shop, item));

INSERT INTO Items VALUES('ビール');
INSERT INTO Items VALUES('紙オムツ');
INSERT INTO Items VALUES('自転車');

INSERT INTO ShopItems VALUES('仙台',  'ビール');
INSERT INTO ShopItems VALUES('仙台',  '紙オムツ');
INSERT INTO ShopItems VALUES('仙台',  '自転車');
INSERT INTO ShopItems VALUES('仙台',  'カーテン');
INSERT INTO ShopItems VALUES('東京',  'ビール');
INSERT INTO ShopItems VALUES('東京',  '紙オムツ');
INSERT INTO ShopItems VALUES('東京',  '自転車');
INSERT INTO ShopItems VALUES('大阪',  'テレビ');
INSERT INTO ShopItems VALUES('大阪',  '紙オムツ');
INSERT INTO ShopItems VALUES('大阪',  '自転車');

※STEP2の状態のテーブル作成用SQL

SELECT 
    all_combinations.shop AS SI1_shop,
    all_combinations.item AS items_item,
    SI.item AS shopList_item
FROM 
    (SELECT I.item, S.shop
     FROM items I CROSS JOIN (SELECT DISTINCT shop FROM ShopItems) S) AS all_combinations
LEFT JOIN 
    ShopItems SI
ON 
    all_combinations.shop = SI.shop AND all_combinations.item = SI.item
ORDER BY
    all_combinations.shop, all_combinations.item;