[SQL文] 自己結合＋相関サブクエリ＋EXISTS

0

自己結合での相関サブクエリの例

CREATE TABLE IF NOT EXISTS `dinosaurs` (
     `id` int(6) unsigned NOT NULL,
     `species` int(6) unsigned NOT NULL,
     `active` int(1) unsigned NOT NULL,
     `name` varchar(200) NOT NULL,
     `comment` varchar(200),
     PRIMARY KEY (`id`)
   ) DEFAULT CHARSET=utf8;
INSERT INTO `dinosaurs` (`id`, `species`, `active`, `name`, `comment`) VALUES
     ('1', '1', '0', 'Tyrannosaurus', 'old name'),
     ('2', '1', '1', 'T. rex', 'new name'),
     ('3', '2', '1', 'Allosaurus', ''),
     ('4', '3', '1', 'Stegosaurus', ''),
     ('5', '4', '0', 'Mammoth', 'deleted');
idspeciesactivenamecomment
110Tyrannosaurusold name
211T. rexnew name
321Allosaurus
431Stegosaurus
540Mammothdeleted

注：speciesのnameが変更された場合、新しい名前がactive=1で追加され、古い名前はactive=0になる。

active=1のspeciesを過去のレコード（active=0のもの）も含めて取得するSQL文

SELECT * FROM `dinosaurs` A
    WHERE A.active = 1 OR
         (A.active = 0 AND EXISTS
              (SELECT 1 FROM `dinosaurs` B
                   WHERE A.species = B.species AND 
                         B.active = 1)
         )

注：「A.active = 0 AND」は省略可能

idspeciesactivenamecomment
110Tyrannosaurusold name
211T. rexnew name
321Allosaurus
431Stegosaurus

考え方

  1. [メインクエリ] テーブルAからactive=1のレコード3件（T. rex、Allosaurus、Stegosaurus）を取得 ⇒ そのまま結果へ
  2. [メインクエリ] テーブルAからactive=0のレコード2件（Tyrannosaurus、Mammoth）を取得
  3. [サブクエリ] active=0のレコード2件それぞれについて、
    テーブルAのspecies（1または4）がテーブルBのspeciesと同じ、かつ
    テーブルBのactiveが1であるレコードが、テーブルBに存在するかどうかを判定
  4. [サブクエリ] 存在する場合（Tyrannosaurus） ⇒ [メインクエリ] 結果に追加
  5. [サブクエリ] 存在しない場合（Mammoth） ⇒ [メインクエリ] 結果から除外

相関サブクエリと通常のサブクエリの違い

相関サブクエリ

  • メインクエリのFROM句にテーブルAがある
  • サブクエリのFROM句にテーブルBがある
  • サブクエリのWHERE句にテーブルAの項目がある
  • メインクエリ実行 ⇒ その結果に対して1行ずつサブクエリ実行

通常のサブクエリ

  • サブクエリ実行 ⇒ メインクエリ実行